error editing a record

Cinnamon
06-26-2002, 03:14 PM
I'm getting the following error:

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

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'WHERE'.

/myweb/new_page_2.asp, line 17

It pertains to this line of code:


strQuery = "UPDATE Cards SET entry=" & Cindex & "WHERE [unique]=" & SearchThis & ";"


I can post all of the code if necessary.

Cinnamon

Derek Stone
06-26-2002, 03:22 PM
Put a space between " and WHERE:


...entry=" & Cindex & " WHERE [unique]=" ...


Good Luck
-CL

Cinnamon
06-26-2002, 03:30 PM
Sorry... that didn't work. I put the space in... saved the page... reloaded in my browser and got the same error. I double checked to make sure I made the change.

Derek Stone
06-26-2002, 03:53 PM
What are the values located in CIndex and SearchThis?
I'm guessing that CIndex would be a number while SearchThis would be a string, correct? If so, you'll need to place apostrophes around SearchThis.

Good Luck
-CL

Cinnamon
06-26-2002, 04:04 PM
I placed ' around the string variable. Now my code looks like this:


strQuery = "UPDATE Cards SET entry=" '& Cindex &' " WHERE [unique]=" & SearchThis & ";"

Now I'm getting the following error:

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

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '='.

/myweb/new_page_2.asp, line 17


Of course I'm not sure which = it means!

Robby
06-26-2002, 04:17 PM
Anytime there is an error that may be caused by a variable or its' value, type in a line like this.( after the variable is assigned a value)...

response.write(strQuery)
response.end

Derek Stone
06-26-2002, 04:18 PM
strQuery = "UPDATE Cards SET entry='"& Cindex & "' WHERE [unique]=" & SearchThis & ";"

-CL

Serevinus
06-26-2002, 08:52 PM
Yeah, CL is right as usual, any time you use an apostrophe to search for a text string you should put them inside the quotes

And when I am debugging a sql query I usualy put somthing like


On Error Resume Next
'... define params, sql query ...
Response.Write strQuery
'... open database ...

Then you will see your query whether or not it works, and if it doesnt it will help you see whats wrong with it

Also, I always define Option Explicit at the top of every single ASP page I have created, I think this is a good idea but correct me if I'm wrong

Robby
06-26-2002, 09:31 PM
Serevinus, using On Error Resume Next is great once your code is working, not very good trying to find the errors. You can however trap the errors thrown by the rs.

Serevinus
06-26-2002, 10:10 PM
That is the only time I ever use On Error Resume Next, because if you mess up the SQL Query ASP throws an error and you cant see where in the query the error is
You use Response.End to stop it so you can read the query, I use Resume to show the query AND the search results (if there are any), of course I would remove the Resume after resolving the problem
Each to their own method I surpose

sl8rz
06-27-2002, 01:53 PM
I believe the problem occurs when an apostrophe appears IN the string. In this case it is rejected by the database. For example, if you have the word "I'll" or "David's" somewhere in your string you will often get an error.

I found the solution here (http://www.asksql.com/questions.asp?ArtID=14747) .

note: The SQL statement often looks fine in the debug print box or in a msgbox -- apostrophe and all.

Good Luck Cinnamon.

Cinnamon
06-27-2002, 02:15 PM
My string does not contain any apostiphies.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum