Update SQL Statement using ADO Execute command

D

Dom

I am trying to set the value of a True/False field to False using the
following code and I keep getting a 'Syntax Error in UPDATE Statement'
message.

strSQL = "UPDATE tbl_Versions SET Current = 0 WHERE VersionIndex=" & loldver
cn.Execute strSQL

I have tried changing to 0 to thew word FALSE amongst other things and I
still get the same error.

I have searched for the correct FALSE value when executing a SQL statement
in ADO but can find nothing.

Could anyone tell me if I'm doing something wrong?

Thanks in advance
Dom
 
H

Harrie_Nak

Hi,
Yor main problem is the quote/doublequote.
This should work:

strSQL = "UPDATE tbl_Versions SET tbl_Versions.[Current] = False WHERE
VersionIndex = '" & loldver & "';"
cn.Execute strSQL

Take a good look at the single and double quotes around loldver

Regards,
ALbert
 
D

Dom

Thanks for the reply.
I tried your suggestion but the loldver variable is a Long datatype so
doesn't need any quotes, and I've tried changing 0 to False but that still
fails.
I'm at a loss now.....

Harrie_Nak said:
Hi,
Yor main problem is the quote/doublequote.
This should work:

strSQL = "UPDATE tbl_Versions SET tbl_Versions.[Current] = False WHERE
VersionIndex = '" & loldver & "';"
cn.Execute strSQL

Take a good look at the single and double quotes around loldver

Regards,
ALbert


Dom said:
I am trying to set the value of a True/False field to False using the
following code and I keep getting a 'Syntax Error in UPDATE Statement'
message.

strSQL = "UPDATE tbl_Versions SET Current = 0 WHERE VersionIndex=" &
loldver
cn.Execute strSQL

I have tried changing to 0 to thew word FALSE amongst other things and I
still get the same error.

I have searched for the correct FALSE value when executing a SQL statement
in ADO but can find nothing.

Could anyone tell me if I'm doing something wrong?

Thanks in advance
Dom
Click to expand...
Click to expand...
 
H

Harrie_Nak

What you can do is create a query in the Design View.
Test it, and if it works, go to the SQL view.
From there you can copy and paste it in your code.
(in reversed order you can use this to test your code).

regards,
Albert





Dom said:
Thanks for the reply.
I tried your suggestion but the loldver variable is a Long datatype so
doesn't need any quotes, and I've tried changing 0 to False but that still
fails.
I'm at a loss now.....

Harrie_Nak said:
Hi,
Yor main problem is the quote/doublequote.
This should work:

strSQL = "UPDATE tbl_Versions SET tbl_Versions.[Current] = False WHERE
VersionIndex = '" & loldver & "';"
cn.Execute strSQL

Take a good look at the single and double quotes around loldver

Regards,
ALbert


Dom said:
I am trying to set the value of a True/False field to False using the
following code and I keep getting a 'Syntax Error in UPDATE Statement'
message.

strSQL = "UPDATE tbl_Versions SET Current = 0 WHERE VersionIndex=" &
loldver
cn.Execute strSQL

I have tried changing to 0 to thew word FALSE amongst other things and
I
still get the same error.

I have searched for the correct FALSE value when executing a SQL
statement
in ADO but can find nothing.

Could anyone tell me if I'm doing something wrong?

Thanks in advance
Dom
Click to expand...
Click to expand...
Click to expand...
 
V

Van T. Dinh

There may be problems with reserved words (even though I can't find anything
from the list of reserved words). Try square brackets anyway, like:

strSQL = "UPDATE [tbl_Versions] AS T SET T.[Current] = 0 " & _
" WHERE T.[VersionIndex] = " & loldver
 
Top