Update SQL Statement using ADO Execute command

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dom - 28 Apr 2005 23:16 GMT

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

Reply to this Message

Harrie_Nak - 29 Apr 2005 00:08 GMT

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

>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'
[quoted text clipped - 14 lines]
> Thanks in advance
> Dom

Reply to this Message

Dom - 29 Apr 2005 17:10 GMT

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.....

> Hi,
> Yor main problem is the quote/doublequote.
[quoted text clipped - 27 lines]
> > Thanks in advance
> > Dom

Reply to this Message

Harrie_Nak - 29 Apr 2005 22:57 GMT

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

> Thanks for the reply.
> I tried your suggestion but the loldver variable is a Long datatype so
[quoted text clipped - 35 lines]
>> > Thanks in advance
>> > Dom

Reply to this Message

Van T. Dinh - 01 May 2005 11:49 GMT

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

Signature

HTH
Van T. Dinh
MVP (Access)

> 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.....

Reply to this Message