Insert record

t.d. · t.d.

Hi all, I'm having a bit of a problem at the moment. I'm basically trying to
insert a record to a MS Access database. I'm using dreamweaver MX. What I want
to do is insert the 1 record into two different tables in the database. I've
tried just about everything now and still can't get it to work.

TA-Selene · TA-Selene

Can you post the code you are using and/or any error messages you are getting? Its hard to diagnose without more specific information.

t.d. · t.d.

Here is the code: <cfset CurrentPage=GetFileFromPath(GetTemplatePath())> <cfif
IsDefined('FORM.MM_InsertRecord') AND FORM.MM_InsertRecord EQ 'form1'>
<cfquery datasource='CGVEC' username='Tony' password='Admin'> INSERT INTO
alltend (ID, title, 'desc', added, ends) VALUES ( <cfif IsDefined('FORM.ID')
AND #FORM.ID# NEQ ''> #FORM.ID# <cfelse> NULL </cfif> ,
<cfif IsDefined('FORM.title') AND #FORM.title# NEQ ''> '#FORM.title#'
<cfelse> NULL </cfif> , <cfif IsDefined('FORM.desc') AND #FORM.desc#
NEQ ''> '#FORM.desc#' <cfelse> NULL </cfif> , <cfif
IsDefined('FORM.added') AND #FORM.added# NEQ ''> '#FORM.added#'
<cfelse> NULL </cfif> , <cfif IsDefined('FORM.ends') AND #FORM.ends#
NEQ ''> '#FORM.ends#' <cfelse> NULL </cfif> ) </cfquery>
<cflocation url='/tenders.cfm'> </cfif> <?xml version='1.0'
encoding='iso-8859-1'?> <!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0
Transitional//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd'>
<html xmlns='http://www.w3.org/1999/xhtml'> <head> <title>Untitled
Document</title> <meta http-equiv='Content-Type' content='text/html;
charset=iso-8859-1' /> </head> <body> <form method='post' name='form1'
action='<cfoutput>#CurrentPage#</cfoutput>'> <table align='center'> <tr
valign='baseline'> <td nowrap align='right'>ID:</td> <td><input
type='text' name='ID' value='' size='32'></td> </tr> <tr
valign='baseline'> <td nowrap align='right'>Title:</td> <td><input
type='text' name='title' value='' size='32'></td> </tr> <tr
valign='baseline'> <td nowrap align='right'>Desc:</td> <td><input
type='text' name='desc' value='' size='32'></td> </tr> <tr
valign='baseline'> <td nowrap align='right'>Added:</td> <td><input
type='text' name='added' value='' size='32'></td> </tr> <tr
valign='baseline'> <td nowrap align='right'>Ends:</td> <td><input
type='text' name='ends' value='' size='32'></td> </tr> <tr
valign='baseline'> <td nowrap align='right'>&nbsp;</td>
<td><input type='submit' value='Insert Record'></td> </tr> </table>
<input type='hidden' name='MM_InsertRecord' value='form1'> </form>
<p>&nbsp;</p> </body> </html> What I want to do is to enter the form
into 2 different tables within the same database. The other table is called
generaltend. Would really appreciate it if you could help me. Thanks

toofastdad · toofastdad

I would like a user to register prior to gaining access to the next level.
This is working, however, when testing the database I realised that I was
entered in the database many times!

The information below is in the form, the table name is client:
FirstName, LastName, Address, City, Province, PostalCode, Phone, Email,
UserName, Password

How do I check to see if the users email address is already in the database
and if it isn't, insert the record. If it is, I would like to return an error
message stating that the user and email address already exists. I am using CF
MX 7 and MySQL database.

Thanks for your help...Colin

The ScareCrow · The ScareCrow

<cftransaction>
<cfquery name="qryFind">
Select emailaddress
From myTable
Where emailaddress = '#form.Email#'
</cfquery>
<cfif qryFind.RecordCount Is 0>
<cfquery name="qryInsert>
Insert Into myTable(emailaddress)
Values('#form.Email#')
</cfquery>
<cfelse>
Opps already exists..............
</cfif>
</cftransaction>

Ken

toofastdad · toofastdad

Ken...thanks for the snippet, however I keep getting errors. Here is the code
from my page prior to inserting your snippet, could you please have a look. I
think I may be inserting in the wrong places.

Thanks...Colin

<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>

<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "register">
<cfquery datasource="#ds#">
INSERT INTO client (FirstName, LastName, Address, City, Province,
PostalCode, Phone, Email, UserName, Password) VALUES (
<cfif IsDefined("FORM.FirstName") AND #FORM.FirstName# NEQ "">
'#FORM.FirstName#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.LastName") AND #FORM.LastName# NEQ "">
'#FORM.LastName#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.Address") AND #FORM.Address# NEQ "">
'#FORM.Address#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.City") AND #FORM.City# NEQ "">
'#FORM.City#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.Province") AND #FORM.Province# NEQ "">
'#FORM.Province#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.PostalCode") AND #FORM.PostalCode# NEQ "">
'#FORM.PostalCode#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.Phone") AND #FORM.Phone# NEQ "">
'#FORM.Phone#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.Email") AND #FORM.Email# NEQ "">
'#FORM.Email#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.UserName") AND #FORM.UserName# NEQ "">
'#FORM.UserName#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.Password") AND #FORM.Password# NEQ "">
'#FORM.Password#'
<cfelse>
NULL
</cfif>
)
</cfquery>

<CFMAIL from="me@blahblab.com"
to="#IIf(isDefined("Form.Email"),"Form.Email",DE(" "))#" type="html"
subject="Welcome to Blahblah">

<CFINCLUDE template="userpassword.cfm">
<CFINCLUDE template="thank_you.cfm">
</CFMAIL>
<cflocation url="thank_you.cfm">
</cfif>
<cfquery name="province" datasource="#ds#">
SELECT * FROM province
</cfquery>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

The ScareCrow · The ScareCrow

I would need to see the error message before I could help, but here are a
couple of things I have noticed.

Password and possibly email are reserved words, this could cause an error. So
either rename the columns or enclose in [ ]

You have cfif's around the values in the insert and if the form value is not
defined or is empty then you insert a null value (this is the correct way to do
this).
1. Are all fields set in the db to accept null ?
2. Are you really sure you want to do this, at present it is possible to
insert a completely empty record. I would think you would need to make at
least the email, username and passwords required ?

Just a point to note:
If you make the email address the key to the table, this by default will not
allow a duplicate entry, so this would also do what you want, but you would
need to catch the error and process it.

Ken

toofastdad · toofastdad

Ken... thanks for the reply! Password and email are not reserved words
according to Ben Forta (CF MX 7 WACK). I will now look a little more closely
for this and use [ ] when appropriate.

As for your suggestion of making the email the Primary Key, great idea. I
changed the database and it works beautifully! Now, I need to create a custom
error message. Is that easy to do? Do you have any suggestions on how to catch
the error and process it?

Thanks Ken...C

toofastdad · toofastdad

Ken... I am using client side validation for all fields in the form priior to
processing and the CLIENT_ID (PK) in the table was not NULL the new database
has the email as the PK and CLIENT_ID is auto increment , INDEX Key

The ScareCrow · The ScareCrow

I'm not sure what's in Ben's book, but I think he may be talking about CF.
Password is a reserved work in MS Access.

Anyway to catch the error use cftry

<cftry>
cfquery stuff in here.........

<cfcatch type="Database">
error message or cflocation in here.................
</cfcatch>
</cftry>

Ken

toofastdad · toofastdad

Thanks Ken...I will give this a try today. Ben's book was referring to Query
of Queries. I also looked up reserved words for MySQL and it looks the same, I
haven't compared. All these database systems have subtle changes so it seems.

[url]http://dev.mysql.com/doc/mysql/en/reserved-words.html[/url]

I will post my results later Thanks...C

toofastdad · toofastdad

Dear Ken,

Thank you for your help with my database query, the cftry example worked
perfectly for the error message. Using the email address as the PK did the
trick.

Thanks for your help, I hope you are around for my next dilemma!!

C

SilverStrike · SilverStrike

Another thing, I don't think that so many <cfif> tags are very efficient. I will suggest using cfset or cfparam, before the insert query.

Insert record

Thread Tools

Display

Insert record

Similar Questions and Discussions

How to Insert record with foreign key id vs. value

insert record loop

How to insert a new record

insert a record

Waiting for db to insert record?

Re: Insert record

Re: Insert record

Insert Record

Re: Insert Record

Re: Insert Record

Re: Insert Record

Re: Insert Record

Re: Insert Record

Re: Insert Record

Re: Insert Record

Re: Insert Record

Re: Insert Record

Posting Permissions