Insert Error - Not recognizing Primary Key

[Looch]

I am using Dreamweaver MX with ColdFusion MX 6.1. The database being used is a
Microsoft Access database. Here's the situation... I have a form that will
gather information to create a new site administrator. The information that
the form asks for is as follows: userName (this is the Primary Key) firstName
lastName pwd There is also a hidden field named 'userGroup', which has a value
of 'admin'. Upon clicking the 'Add' button, I would like for it to first check
to see if there is already someone with the same userName. If there IS NOT a
user with the same userName as that entered in the form, then it should insert
the new user into the database. If there IS already a user with that userName,
then it should re-direct the user to an error page (or just put some RED text
at the top of the page) indicating that the userName they provided is already
in use and that they need to choose a new userName. I hope this is making
sense. I am currently getting an error when I try to add a user with a
userName that I know is already in use. How, and where in my code can I check
for this? Would it be in the query statement that inserts the data, or before
that, or maybe during the page validation? I am at a loss. Any help would
greatly be appreciated. Thanks!! On a side note...if you could also tell me
how to add a 'pwdConfirm' field in my form and explain how to check that
against the other 'pwd' to make sure that they match, before inserting the user
into the table, that would be tight. Thanks, again. Below is the code from my
INSERT query:

<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ
"addSiteAdmin">
<cfquery datasource="farragutChurch">
INSERT INTO tbl_users (firstName, lastName, username, pwd, userGroup) 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.username") AND #FORM.username# NEQ "">
'#FORM.username#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.pwd") AND #FORM.pwd# NEQ "">
'#FORM.pwd#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.userGroup") AND #FORM.userGroup# NEQ "">
'#FORM.userGroup#'
<cfelse>
NULL
</cfif>
)
</cfquery>
<cflocation url="adminIndex.cfm">
</cfif>

[vkunirs]

Hi

Before inserting the user into the table you need to check whether the user
is already exist or not..this has write in the form action page. if the user
exist then redicrect to the form page.

and for the Pwd validation , if you want to use the javascript then there so
many sites which are providing the functions to validate this. you search for
that for the javascript from confirm pwd validation.

[Looch]

Thanks for the tip. I'll check on the javascript stuff. However, I am unsure
what you meant when you said... Before inserting the user into the table you
need to check whether the user is already exist or not..this has write in the
form action page. if the user exist then redicrect to the form page. Can you
elaborate on this and give a little more instruction. I apologize for my
ignorance. I'm still somewhat new at this. I understand what needs to be
done, but I don't know HOW to do it. Are there actions/behaviors built-in to
Dreamweaver to do this, or do I just need to know the code? Where does the
code go? Any more guidance would be appreciated. Thanks!!

[ben]

You need to check for a duplicate userName in a SELECT query before
trying to INSERT the userName and take an appropriate action.

Another solution would be to try to INSERT the userName anyway and to
catch the error (CFTRY, CFCATCH) indicating that the username already
exists, then take an appropriate action.

Looch wrote:

> I am using Dreamweaver MX with ColdFusion MX 6.1. The database being used is a
> Microsoft Access database. Here's the situation... I have a form that will
> gather information to create a new site administrator. The information that
> the form asks for is as follows: userName (this is the Primary Key) firstName
> lastName pwd There is also a hidden field named 'userGroup', which has a value
> of 'admin'. Upon clicking the 'Add' button, I would like for it to first check
> to see if there is already someone with the same userName. If there IS NOT a
> user with the same userName as that entered in the form, then it should insert
> the new user into the database. If there IS already a user with that userName,
> then it should re-direct the user to an error page (or just put some RED text
> at the top of the page) indicating that the userName they provided is already
> in use and that they need to choose a new userName. I hope this is making
> sense. I am currently getting an error when I try to add a user with a
> userName that I know is already in use. How, and where in my code can I check
> for this? Would it be in the query statement that inserts the data, or before
> that, or maybe during the page validation? I am at a loss. Any help would
> greatly be appreciated. Thanks!! On a side note...if you could also tell me
> how to add a 'pwdConfirm' field in my form and explain how to check that
> against the other 'pwd' to make sure that they match, before inserting the user
> into the table, that would be tight. Thanks, again. Below is the code from my
> INSERT query:
>
> <cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ
> "addSiteAdmin">
> <cfquery datasource="farragutChurch">
> INSERT INTO tbl_users (firstName, lastName, username, pwd, userGroup) 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.username") AND #FORM.username# NEQ "">
> '#FORM.username#'
> <cfelse>
> NULL
> </cfif>
> ,
> <cfif IsDefined("FORM.pwd") AND #FORM.pwd# NEQ "">
> '#FORM.pwd#'
> <cfelse>
> NULL
> </cfif>
> ,
> <cfif IsDefined("FORM.userGroup") AND #FORM.userGroup# NEQ "">
> '#FORM.userGroup#'
> <cfelse>
> NULL
> </cfif>
> )
> </cfquery>
> <cflocation url="adminIndex.cfm">
> </cfif>
>

[Looch]

Thanks, ben. I tried the SELECT statement before the INSERT statement, but
after it checked to see if the the form had been submitted. I then proceeded
with the INSERT statement if the FORM.username was equal to the username
returned by the SELECT statement. I also set a variable in the cfif statement
to tell whether or not the data was inserted. If it wasn't, I output some red
text later on in the page indicating that the username was already in use.
Thanks SO much for the tips guys!! Now if I could just find an easy way to
compare 2 password fields to verify that they are the same before I submit the
form. I may be able to put some of this stuff that I just learned into effect
and do it up that way. We'll see. Thanks again for all of the help!

[ben]

The sequence has to be the following:
1. Form page: user enters fields and hits submit

2. Action page:

cftransaction
cfquery name="check"
SELECT ID from "table"
WHERE userName= '#forms.userName#'
/cfquery

cfif check.recordcount
..... userName exists ...
cfquery name="qryUpdate"
UPDATE "table"
set ...
WHERE ID=#check.ID#
/cfquery

cfelse
..... userName does not exist ...
cfquery name="qryInsert"
INSERT INTO "table" (username, ...)
values ('#forms.userName#', ....)
/cfquery
/cfif

Looch wrote:

> Thanks, ben. I tried the SELECT statement before the INSERT statement, but
> after it checked to see if the the form had been submitted. I then proceeded
> with the INSERT statement if the FORM.username was equal to the username
> returned by the SELECT statement. I also set a variable in the cfif statement
> to tell whether or not the data was inserted. If it wasn't, I output some red
> text later on in the page indicating that the username was already in use.
> Thanks SO much for the tips guys!! Now if I could just find an easy way to
> compare 2 password fields to verify that they are the same before I submit the
> form. I may be able to put some of this stuff that I just learned into effect
> and do it up that way. We'll see. Thanks again for all of the help!
>