CementTruck
asked on
Syntax Error upon Database Update
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.
The error occurred in C:\XXXXX\XXXXX\XXXXX\IXXXX
12 : <CFQUERY datasource="AnyOldDatabase
13 : UPDATE INTO tblNewUser (CloneOrList1, OtherOptions1, Printer1)
14 : VALUES ('#FORM.CloneOrList1#','#F
15 : </CFQUERY>
Hello Experts,
I am getting this error upon submitting the page2.
Just a little background info:
I have created an Access database table named tblNewUser. I have 5 .cfm pages that need to populate the database table. Page one asks the user the normal firstname, lastname stuff, then the user submits page1, and page2 has a query looking for all the FORM names from the page1, and populates tblNewUser with the desired information. More questions are asked on page2 and upon submit I want to continue populating the same recordID with more information. Page2 usesthe syntax "INSERT INTO tblNewUser (FirstName, LastName,.....)" and it works just fine. I figured I would use "UPDATE INTO", or just plain "UPDATE" for page 3, but then I get the error above. When I check to tsee if IE has grabbed all the pertinent FORM information from page2 after submit, I see that it has, but will not update the database with that information, plus I get the error message.
What am I doing wrong????? I made sure all the form names were unique. I removed "INTO". I made sure there were no spaces in the table name. I changed UDPATE back to INSERT, and it works, except it puts all this information in a new recordID. But at least I know that the syntax is pretty close. The answer is probably right under my nose.
Thanks for the help.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.
The error occurred in C:\XXXXX\XXXXX\XXXXX\IXXXX
12 : <CFQUERY datasource="AnyOldDatabase
13 : UPDATE INTO tblNewUser (CloneOrList1, OtherOptions1, Printer1)
14 : VALUES ('#FORM.CloneOrList1#','#F
15 : </CFQUERY>
Hello Experts,
I am getting this error upon submitting the page2.
Just a little background info:
I have created an Access database table named tblNewUser. I have 5 .cfm pages that need to populate the database table. Page one asks the user the normal firstname, lastname stuff, then the user submits page1, and page2 has a query looking for all the FORM names from the page1, and populates tblNewUser with the desired information. More questions are asked on page2 and upon submit I want to continue populating the same recordID with more information. Page2 usesthe syntax "INSERT INTO tblNewUser (FirstName, LastName,.....)" and it works just fine. I figured I would use "UPDATE INTO", or just plain "UPDATE" for page 3, but then I get the error above. When I check to tsee if IE has grabbed all the pertinent FORM information from page2 after submit, I see that it has, but will not update the database with that information, plus I get the error message.
What am I doing wrong????? I made sure all the form names were unique. I removed "INTO". I made sure there were no spaces in the table name. I changed UDPATE back to INSERT, and it works, except it puts all this information in a new recordID. But at least I know that the syntax is pretty close. The answer is probably right under my nose.
Thanks for the help.
ASKER
I am using MSAccess for testing purposes, but will be migrating the database to SQL once I get this working. I've tried with and without the "INTO" and still have the same error.
Oh, doh. Sorry I missed it before.
Are you familer with the SET syntax?
UPDATE tblNewUser SET CloneOrList1 = '#FORM.CloneOrList1#', OtherOptions1 = '#FORM.MoreOptions1#', Printer1 = '#FORM.DefaultPrinter1#'
Try that.
Joe P
Are you familer with the SET syntax?
UPDATE tblNewUser SET CloneOrList1 = '#FORM.CloneOrList1#', OtherOptions1 = '#FORM.MoreOptions1#', Printer1 = '#FORM.DefaultPrinter1#'
Try that.
Joe P
ASKER
BogoJoker,
Never tried SET, and am not sure how to use it.
tried this:
<CFQUERY datasource="AnyOldDatabase
UPDATE tblNewUser SET CloneOrList1 = '#FORM.CloneOrList1#', OtherOptions1 = '#FORM.MoreOptions1#', Printer1 = '#FORM.DefaultPrinter1#'
</CFQUERY>
But got this:
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.
Did I have the syntax right at least?
Never tried SET, and am not sure how to use it.
tried this:
<CFQUERY datasource="AnyOldDatabase
UPDATE tblNewUser SET CloneOrList1 = '#FORM.CloneOrList1#', OtherOptions1 = '#FORM.MoreOptions1#', Printer1 = '#FORM.DefaultPrinter1#'
</CFQUERY>
But got this:
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.
Did I have the syntax right at least?
That looks correct to me. I do not know why that did not work.
Maybe one of your FORM elements has a single quote in it that is messing up the SQL?
Joe P
Maybe one of your FORM elements has a single quote in it that is messing up the SQL?
Joe P
ASKER
Bogo,
I have looked at everything until crosseyed, but still cannot find an error.
I'll keep looking.
I have looked at everything until crosseyed, but still cannot find an error.
I'll keep looking.
Hmmm.
That is really perplexing.
Are the database column names correct? Same with the #FORM# elements?
Joe P
That is really perplexing.
Are the database column names correct? Same with the #FORM# elements?
Joe P
try breaking out each update to its own individual action:
<CFQUERY datasource="AnyOldDatabase
UPDATE tblNewUser SET CloneOrList1 = '#FORM.CloneOrList1#'
UPDATE tblNewUser SET OtherOptions1 = '#FORM.MoreOptions1#'
UPDATE tblNewUser SET Printer1 = '#FORM.DefaultPrinter1#'
</CFQUERY>
<CFQUERY datasource="AnyOldDatabase
UPDATE tblNewUser SET CloneOrList1 = '#FORM.CloneOrList1#'
UPDATE tblNewUser SET OtherOptions1 = '#FORM.MoreOptions1#'
UPDATE tblNewUser SET Printer1 = '#FORM.DefaultPrinter1#'
</CFQUERY>
You may also need to add the .... WHERE .... so that it knows what record to update.
Usually the best way to do this is to have a unique key for each record so that you can
specify each record individually.
Usually the best way to do this is to have a unique key for each record so that you can
specify each record individually.
ASKER
Bogo, Rocky,
I tried Rocky's first idean - no worky. Then I started REMming statements out. I started with:
<CFQUERY datasource="AnyOldDatabase
UPDATE tblNewUser SET CloneOrList1 = '#FORM.CloneOrList1#'
</CFQUERY>
and it worked. Then I added: OtherOptions1 = '#FORM.MoreOptions1#' - and it worked etc. etc.
In short, I did not change any code, but I added the original code in piece by piece until it worked for everything. That was pretty strange.
So basically Bogo, were at:
<CFQUERY datasource="AnyOldDatabase
UPDATE tblNewUser SET CloneOrList1 = '#FORM.CloneOrList1#', OtherOptions1 = '#FORM.MoreOptions1#', Printer1 = '#FORM.DefaultPrinter1#'
</CFQUERY>
And it updates the database. Now I need to conquer the database portion.
I tried Rocky's first idean - no worky. Then I started REMming statements out. I started with:
<CFQUERY datasource="AnyOldDatabase
UPDATE tblNewUser SET CloneOrList1 = '#FORM.CloneOrList1#'
</CFQUERY>
and it worked. Then I added: OtherOptions1 = '#FORM.MoreOptions1#' - and it worked etc. etc.
In short, I did not change any code, but I added the original code in piece by piece until it worked for everything. That was pretty strange.
So basically Bogo, were at:
<CFQUERY datasource="AnyOldDatabase
UPDATE tblNewUser SET CloneOrList1 = '#FORM.CloneOrList1#', OtherOptions1 = '#FORM.MoreOptions1#', Printer1 = '#FORM.DefaultPrinter1#'
</CFQUERY>
And it updates the database. Now I need to conquer the database portion.
ASKER
Rocky,
Can you explain the "WHERE" to me? I've got issues with the database now. Although the text shows op in the table now, if there was any blank spaces in the preceding records the text from the last record populates the empties. Will your "WHERE" fix that?
Can you explain the "WHERE" to me? I've got issues with the database now. Although the text shows op in the table now, if there was any blank spaces in the preceding records the text from the last record populates the empties. Will your "WHERE" fix that?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Example DB table:
id col1 col2
-- ------ ------
1 asdf qwer
2 poio tryhj
3 qwe nmg
UPDATE tblNewUser SET col1 = '#FORM.CloneOrList1#' WHERE id = 1
This will update the first row of your DB respectively because the WHERE specifies the first row.
Hope that helps
id col1 col2
-- ------ ------
1 asdf qwer
2 poio tryhj
3 qwe nmg
UPDATE tblNewUser SET col1 = '#FORM.CloneOrList1#' WHERE id = 1
This will update the first row of your DB respectively because the WHERE specifies the first row.
Hope that helps
ASKER
Bogo,
I see.
Each time someone runs through the submission of the 5 pages, the database is incremented by one unique RecID. I plan on grabbing that RecID during the submission of the first page, and forcing the updates to occur only on that RecID. But what happens if someone abandons the forms in the middle of the submission process? Does that mean the next guy who comes along is forced to complete his pages? Can I do an "ABORT" if incomplete?
By the way, I've increased the point value of this question. You've already helped me immensely.
I see.
Each time someone runs through the submission of the 5 pages, the database is incremented by one unique RecID. I plan on grabbing that RecID during the submission of the first page, and forcing the updates to occur only on that RecID. But what happens if someone abandons the forms in the middle of the submission process? Does that mean the next guy who comes along is forced to complete his pages? Can I do an "ABORT" if incomplete?
By the way, I've increased the point value of this question. You've already helped me immensely.
Do you have a multipage form which multiple users can just randomly walk up and start filling in?
I am not 100% on what you mean about another user coming along and picking up where the last one left off.
Please explian ....
I am not 100% on what you mean about another user coming along and picking up where the last one left off.
Please explian ....
ASKER
Rocky,
I have a multipage form that can be accessed by any one of 2500 users. My fear is that someone will start filling in a form and after getting halfway through it (i.e. page 3 or 4), he decides to quit and go to lunch. At this point the database has already been populated with half the information. How do I abort this guys halfbaked database entry? Is there a way to delete incomplete records in a database after a couple of hours?
If another user gets on the same computer and signs in, will the forms and sessions still be saved? Can a "timeout" occur to delete the session variables after a few minutes?
I have a multipage form that can be accessed by any one of 2500 users. My fear is that someone will start filling in a form and after getting halfway through it (i.e. page 3 or 4), he decides to quit and go to lunch. At this point the database has already been populated with half the information. How do I abort this guys halfbaked database entry? Is there a way to delete incomplete records in a database after a couple of hours?
If another user gets on the same computer and signs in, will the forms and sessions still be saved? Can a "timeout" occur to delete the session variables after a few minutes?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You've both been very helpful. I've split the point between you.
Do you need the word INTO? I am not familer with the database your are using, I use mysql but the syntax for mysql is just
UPDATE tablename
no INTO keyword
Joe P