Update error 2

[crashc123]

What is wrong with my update command?
Getting this error:
Microsoft JET Database Engine error '80040e14'
Syntax error in UPDATE statement.
/myweb/Projectlist.asp, line 150
(Line 150 in bold below)

iRecordId = Request.Form("ID&quot

strSQL = "UPDATE Projects SET " _

& "JobNo = '" & Request.Form("JobNo&quot & "', " _
& "Title = " & Request.Form("Title&quot & ", " _
& "Description = '" & Request.Form("Description&quot & "', " _
& "WHERE (ID = " & iRecordId & &quot"

Set cnnDBEdit = Server.CreateObject("ADODB.Connection&quot
cnnDBEdit.Open CONN_STRING

cnnDBEdit.Execute strSQL, adAffectAll, adCmdText or adExecuteNoRecords

cnnDBEdit.Close
Set cnnDBEdit = Nothing

Tried removing adComdtext or adExecuteNoRecords and got just Syntax error, line 150
I really want to thank everyone who has helped me get this far.

 

[TechnicalAnalysis]

Are those fields, Title and ID, numeric or text?
Please ensure to add ' quotation mark before and after a text field.
Also get rid of the space before and after '=' sign to see how it goes.
"Title='" & Request.Form("Title&quot & "', " _

Regards

http://www.technical.analysis.dingojunction.com

 

[crashc123]

Tried that, and I still get same error message.

right now all fields are text, later on I want to add some date fields but for right now I would be happy to get this right.

Thanks for your help, TechnicalAnalysis.

Also, should I post my whole code here to see if it is something else?

 

[Guest_imported]

Get rid of the comma right before WHERE clause so it could read as follows:


strSQL = "UPDATE Projects SET JobNo = '" & Request.Form("JobNo&quot & "', " _
& "Title = " & Request.Form("Title&quot & ", " _
& "Description = '" & Request.Form("Description&quot & "' " _
& "WHERE (ID = " & iRecordId & &quot"

 

[lobstah]

combining TechnicalAnalysis's and guestg's suggestions, final sql statement should look like:

strSQL = "UPDATE Projects SET " _

& "JobNo = '" & Request.Form("JobNo&quot & "', " _
& "Title = '" & Request.Form("Title&quot & "', " _
& "Description = '" & Request.Form("Description&quot & "' " _
& "WHERE (ID = '" & iRecordId & "')"

note the single quotes around the description and id values, and the removal of the comma before the WHERE clause.

 

[crashc123]

Well now I get a different error, so you guys are helping.

Now I get:

Microsoft JET Database Engine error '80040e07'
Data type mismatch in criteria expression.
/myweb/projectlist.asp, line 150

This is the same line as above:

cnnDBEdit.Execute strSQL, adAffectAll, adCmdText or adExecuteNoRecords

Any Ideas?

Again Thanks to you all!!

 

[lobstah]

can you repost your sql statement? apparently you have a text field comparing against a numeric field, or vice-versa.

you said ALL you fields were text fields, including the ID field? if not, remove the single quotes from around '" & iRecordId & "')"

 

[crashc123]

Okay you're right. I completely blanked about the ID field which is auto number. I think this is because it isn't something I want the user to bother with.

Thank you, lobstah that seemed to take care of this problem. Since I am new to all this I want to make sure I understand the idea. Text fields need to be surrounded by single quotes outside of double quotes when a numeric field only needs double quotes. If you have the time, could you explain why in layman's terms, so I can understand.

Thanks again!

 

[lobstah]

actually, the values aren't surrounded by double-quotes, the text string is.

for example, let me dissect your sql statement text string:

the first double-quote starts your text string
strSQL = "UPDATE Projects SET " _

then you end the string above, with the continue on next line character (_) and (&) on the next line and restart the text string with the double-quote
& "JobNo = '" & Request.Form("JobNo&quot & "', " _
then you stop the text string (after the single-quote, because now you have a variable to insert, and so that you get the variable's VALUE and not just the variable's name, you need to set it outside the text string)
& "Title = '" & Request.Form("Title&quot & "', " _
& "Description = '" & Request.Form("Description&quot & "' " _
& "WHERE (ID = '" & iRecordId & "')"

and so on and so on

does that make sense, or did i get too wordy?

 

[crashc123]

Hey, that was great. Will probably take a while to sink in though. Had to jump in at the deep end.....hopefully I will have time soon to go back to the beginning so that I understand what exactly I am doing.

Again your help is greatly apprciated.