Syntax Error in UPDATE statement 1

[jedel]

Hey all,

I'm stuffed if I can find this error. Here is the UDPATE code. It comes from Dr Devs pages

<%
If Request("UDBtn") <> "" Then
   intRecIDs = Replace(Request("hidRecID"), "*", "")
   arrRecIDs = Split(intRecIDs, ", ")
   For i = 0 to Ubound(arrRecIDs)
      DESC= Replace(Request("txtDesc" & arrRecIDs(i)), "'", "''")
	  CAT= Replace(Request("cboCat" & arrRecIDs(i)), "'", "''")
	  

      Set commUpdate = Server.CreateObject ("ADODB.Command")
		commUpdate.ActiveConnection = MM_School_STRING
		
      commUpdate.CommandText = "UPDATE photoTBL SET Desc = '"&DESC&"', category = '"&CAT&"' WHERE PhotoID = " & arrRecIDs(i)
	   
      commUpdate.CommandType = 1
      commUpdate.CommandTimeout = 0
      commUpdate.Prepared = true
      commUpdate.Execute()
   Next
   
   Response.Redirect("RDPhotoList.asp")
End If

%>

The error I'm getting is:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

/ccc/admin/photos/photolist.asp, line 162

Can anybody find the syntax error???

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[wvdba]

UPDATE photoTBL SET Desc = '"&DESC&"', category = '"&CAT&"' WHERE PhotoID = " & arrRecIDs(i)

i think it should be like this:
does arrRecIDs(i) need quotes around it or not?
also it may need single quotes to double quotes by using
chr(34)
....Desc = " & CHR(34) & DESC & CHR(34) & ", category = " & ...

 

[vicvirk]

could you response.write the qry to the screen and post the actual qry:


tempStr = "UPDATE photoTBL SET Desc = '"&DESC&"', category = '"&CAT&"' WHERE PhotoID = " & arrRecIDs(i)

response.write tempStr

View the source of the generated markup and copy the actual query from there...

It could be a number of things, your DESC could contain linebreaks, quotes (single or double), arrRecIDs(1) could be blank...etc...

if it returns something like

UPDATE photoTBL SET DESC = 'this is my desc', category = 'myCat' WHERE PhotoID = 1

then there is nothing wrong with it...but if it returns

UPDATE photoTBL SET DESC = 'this is my "desc"', category = 'myCat' WHERE PhotoID =

Also check your field types, is the field named "category" a text field or a numeric - if it is numeric, you don't need the quotes...



TIP: trying googling the answer before posting, you'll find that more times than not someone else somewhere has had the same request and posted an answer online.
----
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javascript enabled browsers

 

[gmmastros]

DESC is a reserved keyword in sql. It's used in the order by clause to sort your data in descending order. If you can, I encourage you to change the name of the column (probably to DESCRIPTION). This is the best thing to do. If you cannot change the column name, then you can make this work by enclosing the column name in square brackets, like this...

commUpdate.CommandText = "UPDATE photoTBL SET [!][[/!]Desc[!]][/!] = '"&DESC&"', category = '"&CAT&"' WHERE PhotoID = " & arrRecIDs(i)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[vicvirk]

good call george, totally missed that one...

TIP: trying googling the answer before posting, you'll find that more times than not someone else somewhere has had the same request and posted an answer online.
----
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javascript enabled browsers

 

[gmmastros]

good call george, totally missed that one...

Me too.... the first time I read this thread.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jedel]

gmmastros, take a star. That was the problem.

Stinkin' reserved words!!!

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."