Syntax error in UPDATE statement - help please

[PushCode]

Can anyone tell what's wrong with this update query?

I'm getting the following error:
Syntax error in UPDATE statement.

Here's the pertinent code:

Dim fileID
Dim fileCatID
Dim fileTitle
Dim qUpdate

fileID = request.form("id")
fileCatID = request.form("cat_id")
fileTitle = Trim(request.form("title"))

Set qUpdate = Server.CreateObject("ADODB.Recordset")
qUpdate.ActiveConnection = strServerConnection
qUpdate.Source = "UPDATE downloads SET title = '" & fileTitle & "', cat_id = " & fileCatID & " WHERE id = " & fileID
qUpdate.Open()
qUpdate.Execute
qUpdate.Close

 

[TheCandyman]

That normally means your trying to put a text box into a numbers only field in the DB. do this

response.write qUpdate.Source

and try that directly in in your DB. Most DBs will thow an error and point to the spot with issues. If it works in the DB, then it's your connection. let us know.

 

[PushCode]

Thanks TheCandyman, but I don't know what you mean.

Where should I put the response.write qUpdate.Source line?

Either before or after the qUpdate.Source line still throws the same error. I don't know what you mean by trying it directly in my DB either.

Please excuse my ignorance.

 

[PushCode]

By the way here are the form fields from the submitting page.

<input type="hidden" name="id" value="<%= qFile("id") %>" />
<input type="text" size="40" name="title" value="<%= qFile("title") %>" />
<select name="cat_id">
	<option>Change Category...</option>
	<%While Not qCats.EOF%>
	<option value="<%= qCats("cat_id") %>"><%= qCats("category") %></option>
	<%
	qCats.MoveNext
	Wend
	qCats.Close
	Set qCats = Nothing
	%>
</select>

 

[TheCandyman]

Sorry, should have explained more. Put it here

Set qUpdate = Server.CreateObject("ADODB.Recordset")
qUpdate.ActiveConnection = strServerConnection
qUpdate.Source = "UPDATE downloads SET title = '" & fileTitle & "', cat_id = " & fileCatID & " WHERE id = " & fileID
[b]response.write qUpdate.Source[/b]
qUpdate.Open()
qUpdate.Execute
qUpdate.Close

If you are using MSAccess, make a query, and put the SQL in there to see if it pulls correctly. Just trying to narrow down the problem to see if it a DB or ASP error.

 

[PushCode]

Thanks. Doing that still throws the same error.

Anyone with thoughts on this problem?

 

[PGoRule]

It's a issue with the update statements caused due parameter passing into Update statement.
As CandyMan says, you will get the exact query that you are passing to DB by using this:

response.write qUpdate.Source

Just check what it returning as a string in browser window. For this commect the other code related to the passing that query programmatically from your code to check the response string. Copy that string and apply directly to query builder of your database. Check whether it runs properly ther or gives any error. That error message will tell you the exact problem if you have any...

If fileId and fileCatId are numeric then parse them to numbers before sending it to query. If these are Varchars then pass them through quotes ('fileCatId').


Sharing the best from my side...

--Prashant--

 

[etjohnson81]

make sure your datatypes match. I have ran into that before. If you are putting into a text field you have to have the ' such as

 update table set text '" & txtValue & "', num = " & numValue

Hope that helps.

<A href="

http://www.easycgi.com/shared_hosting/?kbid=4815&img=easycgi_120x60_5.gif">

<img src="

http://banners.easycgi.com/ShowBanner.asp?id=4815&img=easycgi_120x60_5.gif"

border=0></a>

 

[PushCode]

Well, I resorted to letting Dreamweaver do it for me. It works now. I couldn't figure it out with all of the suggestions. Thanks anyway folks.