|
| |
Update error '80040e14' Syntax error (missing operator) in query expression 'ID='
View related threads:
(in this forum
| in all forums)
|
Logged in as: Guest
|
|
|
stephen78751
Posts: 15
Joined: 5/12/2004
Status: offline
 |
Update error '80040e14' Syntax error (missing operator)... - 8/3/2004 21:32:30
Trying to have the code below update items on a database, but get the following error message. Any ideas on what’s wrong?
Thanks in advance!
Stephen
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'ID='.
/update3.asp, line 24
<html>
<body>
<%
Dim ID, status, date1, amount, firstname, lastname, comments, email, password
ID= trim(Request.Form("ID"))
status= trim(Request.Form("status"))
date1= trim(Request.Form("date1"))
amount= trim(Request.Form("amount"))
firstname= trim(Request.Form("firstname"))
lastname= trim(Request.Form("lastname"))
comments= trim(Request.Form("comments"))
email= trim(Request.Form("email"))
password= trim(Request.Form("password"))
Dim Conn ,Rs, sql
Set Conn = Server.CreateObject("ADODB.Connection")
Set Rs = Server.CreateObject("ADODB.Recordset")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("/fpdb/users.mdb")
sql= "Update my_users Set ID='" & ID & "', status='" & status & "', date1='" & date1 & "', amount='" & amount & "', firstname='" & firstname & "', lastname='" & lastname & "', comments='" & comments & "', email='" & email & "', password='" & password &"' WHERE ID=" & ID
Rs.Open sql, Conn
Conn.Close
Set Rs=Nothing
Set Conn = Nothing
Response.Write "Successfully Updated"
%>
</body>
</html>
|
|
|
|
BeTheBall
Posts: 6493
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Update error '80040e14' Syntax error (missing opera... - 8/3/2004 21:44:08
A couple of things.
First, you don't want to update the ID number of the record. Second, if date1 is updating a field that is data type date/time, then the delimiters surrounding the variable should be ##, not ' '. Also, if amount is a numeric field then '"& amount &:' becomes "& amount &"Try this:
sql= "Update my_users Set status='" & status & "', date1=#" & date1 & "#, amount=" & amount & ", firstname='" & firstname & "', lastname='" & lastname & "', comments='" & comments & "', email='" & email & "', password='" & password &"' WHERE ID=" & ID &""
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
stephen78751
Posts: 15
Joined: 5/12/2004
Status: offline
|
RE: Update error '80040e14' Syntax error (missing opera... - 8/3/2004 22:01:58
Big improvement, but still getting the this error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.
/update3.asp, line 27
| |
|
|
|
BeTheBall
Posts: 6493
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Update error '80040e14' Syntax error (missing opera... - 8/3/2004 22:45:10
OK. A couple more comments. Check the data types of each of your db fields. Password can be a reserved word so try changing:
password='" & password &"'
to
[password]='" & password &"'
If the error still exists, please list the datatype (text, number or date) for each of your fields.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
stephen78751
Posts: 15
Joined: 5/12/2004
Status: offline
|
RE: Update error '80040e14' Syntax error (missing opera... - 8/3/2004 23:24:28
No luck yet. Here are the data types.
Thanks for your input.
Stephen
ID= AutoNumber
status= text
date1= date/time
amount= number
firstname= text
lastname= text
comments= text
email= text
password= text
| |
|
|
|
BeTheBall
Posts: 6493
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Update error '80040e14' Syntax error (missing opera... - 8/3/2004 23:31:42
Is comments a memo field? If so, move it to be the last field updated.
sql= "Update my_users Set status='" & status & "', date1=#" & date1 & "#, amount=" & amount & ", firstname='" & firstname & "', lastname='" & lastname & "', email='" & email & "', password='" & password &"', comments='" & comments & "'WHERE ID=" & ID &""
Also, when attempting the update, is there an entry for every field or are some fields blank? Blank memo or date fields may trigger an error.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
stephen78751
Posts: 15
Joined: 5/12/2004
Status: offline
|
RE: Update error '80040e14' Syntax error (missing opera... - 8/4/2004 0:53:50
No, no blanks. However, all the data is passed to the page by a form and it is all passed as text.
type="text" I don't find type options for date, number or AutoNumber. Could this be the problem?
Thanks again,
Stephen
<tr>
<td>ID: </td>
<td><input type="text" name="ID" size="30" maxlength="250" value="<%=Rs("ID")%>"></td>
</tr>
| |
|
|
|
stephen78751
Posts: 15
Joined: 5/12/2004
Status: offline
|
RE: Update error '80040e14' Syntax error (missing opera... - 8/4/2004 1:57:21
Spooky,
Yes, that shows the problem!
Thanks!
Stephen
Duane,
I got it working. I turns out that in my copy/paste process, I left many of the name value unchanged, so many name values where passed to the page in question incorrectly.
As for the numerous errors you corrected, I can not thank you enough. You saved me a great deal of time debugging and researching to resolve these problems.
Thanks again,
Stephen
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
ky