Incorrect syntax......

Adam H-W · June 20th, 2004, 01:30 PM

Hi there

I've got a varchar column in my SQL db - when I go to insert some data I get an error like this:

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

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 've'.

This is because it is stopping at the ' chararcter (I'm guessing that it thinks the rest of the statement is being commented out) - how do I get round this so that it doesn't think that the rest of the statement is being commented out?

thanks

Adam

shahchi1 · June 20th, 2004, 07:20 PM

you need to use the Replace() function and replace all single quotes with two single quotes.

Repalce(YourString,"'","'')

Stilll,if you are having any problems,
Response.Write(yourSQLStatement) and post it.

happygv · June 21st, 2004, 05:44 AM

Hi Adam,

Can you post the code here, so as to find where you go wrong?

Cheers!

_________________________
-Vijay G

Strive for Perfection

Adam H-W · June 21st, 2004, 06:51 AM

sure

it's a stored procedure and this is the asp code I'm using for it:

set strSQL = con.execute("[up_insert_sale] '" & strSale & "' ,'" & strCondition & "' , '" & intPrice & "' , '" & strDetails & "' , '" & strName & "' , '" & strEmail & "' , '" & strLocation & "' , '" & strDateNow & "' , '" & Deleted & "'")

thanks

happygv · June 21st, 2004, 07:29 AM

Hi Adam,

You are making mistake there by trying to set the result of con.execute() to strSQL.

It should be written this way.

Code:

set strSQL = "[up_insert_sale] '" & strSale & "' ,'" & strCondition & "' , '" & intPrice & "' , '" & strDetails & "' , '" & strName & "' , '" & strEmail & "' ,  '" & strLocation & "' ,  '" & strDateNow & "' , '" & Deleted & "'"
Response.write strSQL
response.end
con.execute(strSQL)

But I foresee that the code marked in RED should be causing problem, as you are trying to enclose intprice within quotes in first case. Not sure if Deleted is BIT type or INT type. Post details on these two COLUMN's DATA TYPES

And post the resulting SQL statement here. That should show what goes wrong.

Cheers!

_________________________
-Vijay G

Strive for Perfection

Adam H-W · June 21st, 2004, 10:33 AM

When I copy your code there and do a response.write I get this error:

Microsoft VBScript runtime error '800a01a8'

Object required: '[string: "[up_insert_sale] 'ad"]'

where ad is the text that I added in the text field.

intPrice is a varchar column and Deleted is bit.

thanks

Adam

happygv · June 21st, 2004, 10:45 AM

That is because, you are missing "EXECUTE" word there.

It should be

strSQL = "Execute up_insert_sale .....

Remove SET keyword there.

If you say up_insert_sale is a stored proc.

Cheers!

_________________________
-Vijay G

Strive for Perfection

Adam H-W · June 21st, 2004, 11:11 AM

great, thanks Vijay

happygv · June 21st, 2004, 11:14 AM

Welcome! Actually I didnot notice that you were missing EXECUTE the previous time;).

Cheers!

_________________________
-Vijay G

Strive for Perfection