• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

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\IXXXXX\page3.cfm: line 14
 
12 :   <CFQUERY datasource="AnyOldDatabase">
13 :   UPDATE INTO tblNewUser (CloneOrList1, OtherOptions1, Printer1)
14 :   VALUES ('#FORM.CloneOrList1#','#FORM.MoreOptions1#','#FORM.DefaultPrinter1#')
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.






 
0
CementTruck
Asked:
CementTruck
  • 8
  • 5
  • 5
2 Solutions
 
BogoJokerCommented:
Hi CementTruck,

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
0
 
CementTruckAuthor Commented:
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.
0
 
BogoJokerCommented:
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
CementTruckAuthor Commented:
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?


0
 
BogoJokerCommented:
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
0
 
CementTruckAuthor Commented:
Bogo,

I have looked at everything until crosseyed, but still cannot find an error.

I'll keep looking.
0
 
BogoJokerCommented:
Hmmm.
That is really perplexing.
Are the database column names correct?  Same with the #FORM# elements?

Joe P
0
 
rockymageeCommented:
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>
0
 
rockymageeCommented:
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.
0
 
CementTruckAuthor Commented:
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.
0
 
CementTruckAuthor Commented:
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?
0
 
BogoJokerCommented:
He suggested adding a WHERE.
Say you had 100 records in your database, the update right now would make all 100 records exactly the same.
Specify just one record by using WHERE id='1' <-- that would only update record with id = 1 assuming that you have an id field.  This is the normal and safe method for updating a single record in the database.

Joe P
0
 
rockymageeCommented:
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

0
 
CementTruckAuthor Commented:
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.
0
 
rockymageeCommented:
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 ....
0
 
CementTruckAuthor Commented:
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?
0
 
rockymageeCommented:
OK, what you want to do is put all of the information into the DB at the same time .... to do this you will need to pass the values of each page on to the next page until all the data has been entered.

Upon completion of all of the required fileds, submit the information to the DB.

There are several ways you can pass information from page to page ....

Session varaibles and hidden form fileds are probably the two most efficient ways.

If you choose to use session variables then keep in mind if the user gets up in the middle of the from filling process that all thier data will be lost if the session times out.

Approximately how many fields would you say you have?  Here is a code snippet I shared on EE that will gather the fields from your form and pass them as hidden fields .....  http://www.experts-exchange.com/Web/Q_21831663.html ....

You may also want to put a timeout on the actual page as well that redirects the browser to a start page if the page has not been altered.  So if your user gets up and does not come back for say 5 minutes, the page redirects back to the start page and clears all the stored data.

0
 
CementTruckAuthor Commented:
You've both been very helpful. I've split the point between you.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now