DB Insert Prob???

ma701sd · Jan 2, 2001

Hi,
Can anyone cast a fresh pair of eyes over this one...

Im trying to insert only one item into a particular column in my database.
I conduct a query to select the appropriate the row (which is successful) but for some reason, the DB is not populated. ColdFusion does not report any errors though....?
Im passing via form variables..

Heres the code:
----------------------------------------------------------
CreatePage.cfm
<CFFORM ACTION="AuthenticateUser.cfm?cfid=#cfid#&cftoken=#cftoken#" METHOD="POST">
Please enter Your Username <CFINPUT TYPE="Text" NAME="UserName" REQUIRED="NO"><BR>
Please ener your password <CFINPUT TYPE="password" NAME="Password" REQUIRED="NO"><BR>
If you have forgotten your password <A href="ForgottenPassword.cfm"> Retreive your Password </A>
<INPUT TYPE="submit" VALUE="process">
----------------------------------------------------------
----------------------------------------------------------
InsertIntoDB.cfm
<CFQUERY DATASOURCE = "ONLINE" NAME = "GetUser">
SELECT UserName
FROM Users
WHERE UserName = '#Client.UserName#'
</CFQUERY>

<head>
<title>Untitled</title>
</head>

<body>

<CFIF #GetUser.RecordCount# EQ 1>
<CFINSERT DATASOURCE = "Online"
TABLENAME = "Users"
FORMFIELDS = '#Form.Title#'>
</CFIF>

<CFOUTPUT>
Your website now has a heading of <B>#title#</b><BR>
</cfoutput>
---------------------------------------------------------

If anyne can help...PLEASE!!!!!

Thanks
Sam

<INPUT TYPE="reset" VALUE="Clear">

iqof188 · Jan 2, 2001

There's two things about your code: the first is, why are you using Client.UserName in your query "GetUser" in InsertIntoDB.cfm? The variable is coming from a FORM, so refer to it by using FORM.UserName.
The reason why no data is inserted is possibly because the recordcount of the GetUser query is NOT 1. According to your code data only gets inserted if recordcount is 1. Check the recordcount, and PLEASE write a full SQL statement (INSERT INTO et cetera) since I think CFINSERT is bad ;-). Check other forums, and you'll see CFINSERT provides a lot of problems.
Hope this helps.
<webguru>iqof188</webguru>

ma701sd · Jan 3, 2001

Hi iqof188,
Thanks for your reply.
Im using client.username as this is a variable that I have set in my Application.cfm and is verified once the user has logged in i.e if the user login is succesful then a variable is set where CFSET form.username = client.username (I thunk this is right??)

Now about the inserting the data into my DB,
This is the error I get:
[Microsoft][ODBC Microsoft Access Driver] The field 'Users.PasswordReminderQuestion' cannot contain a Null value because the Required property for this
field is set to True. Enter a value in this field.

Data Source = "Online"

SQL = "INSERT INTO Users ( `Title` ) VALUES ( ?)"

Data Source = "Online"

The error occurred while processing an element with a general identifier of (CFINSERT), occupying document position (18:2) to (20:30).

Basically, i'm checking to make sure the user is a valid user within the database, If Yes then add the data else display an error message.. (it retrieves a record)
The query is successful, yet it does not enter the data.
WHats interesting is that the error (above)where passwordreminderquestion cannot be null does not make sense because this field is already populated. Is this a db setting?

Help!!!!!!!!!!!

Cheers
Sam

GunJack · Jan 3, 2001

Hey Sam,

I'm a little confused by the code you posted. The first section has a form which calls a page called "AuthenticateUser.cfm" but you posted code for a page called "InsertIntoDB.cfm". If authenticateuser then calls InsertIntoDB.cfm, it would help to see authenticateuser.cfm as well.

I see possibly two things that might be wrong. The first is that your Access database probably has the "allow zero length" option set to "no" on your "PasswordReminderQuestion" field. This would give you the error above when you try to insert a row with only data for the "title" field. Change this to "yes" and it shoudl eliminate that problem. It also appears that your data for the title field might not be coming across correctly but I can't tell without seeing the page which calls the "insertIntoDB.cfm" page.

GJ

iqof188 · Jan 3, 2001

I'm not sure I fully understand the problem then, but shouldn't the CFINSERT be a CFUPDATE? It seems to me you are trying to Add a value to the Title column in your database, right? Username, passwordreminderquestion and the other fields in your table are already there right? Sort out whether you need an insert or an update. Hope this helps!

<webguru>iqof188</webguru>

ma701sd · Jan 4, 2001

Hi Gunjack,
Yeah, that was me messing about with my code (experimenting though I cant think y!!

iqof188, Thanks...Problem Sorted!
I was being silly and could not see that I had to use an update instead of an insert SQL command. The row where I wanted to add data already had data so the only logical thing was an update!

Thanks everybody for your help !

Sam....

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

DB Insert Prob???

ma701sd

IS-IT--Management

iqof188

IS-IT--Management

ma701sd

IS-IT--Management

GunJack

ISP

iqof188

IS-IT--Management

ma701sd

IS-IT--Management

Similar threads

Part and Inventory Search

Sponsor