Missing semicolon at end of SQL statement!

[bomayed]

This SQL statement will work fine:

conn.execute ("INSERT INTO TABLE (FIELD1) VALUES ('NAME')")


However, if I add the where clause I will get an error message saying ( Missing semicolon at end of SQL statement!

EXAMPLE:

conn.execute ("INSERT INTO TABLE (FIELD1) VALUES ('NAME') WHERE ID LIKE '"&VarName&"' ")

Does anyone have any idea why am I getting this error message?

 

[KenReay]

Hi

SQL statements should be terminated with ;

Access (Jet) is sloppy about enforcing this rule

Perhaps you hace foiund a situation where it does enforce it, ot maybe you are using a backen which is not Access (Jet)

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[bomayed]

Thanks for your reply,

But I can't find the part which is not Access (Jet) in this statement:

conn.execute ("INSERT INTO TABLE (FIELD1) VALUES ('NAME') WHERE ID LIKE '"&VarName&"' ")

and where ever I add a semicolon at the end of the statement I get an error . .

 

[KenReay]

Hi

What the same error?

Is ID a number column?

Also are you not mixing simple insert with insert from a table,

AND having a table called TABLE does not seem like a good idea

eg

conn.execute ("INSERT INTO tblTABLE (FIELD1) VALUES ('NAME'); ")

or

conn.execute ("INSERT INTO tblTABLE (FIELD1) SELECT FIELD1 FROM tblTABLE WHERE ID = " & VarName & "; ")

or

conn.execute ("INSERT INTO tblTABLE (FIELD1) SELECT FIELD1 FROM tblTABLE WHERE ID = '" & VarName & "'; ")








Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[bomayed]

Thanks again ,

When I use the semicolon at the end of the SQL statement I get an error message saying ( Expected: List separator or ). . )

ID= is not a number , it's variant like A123BE

TABLE is not the real name , the actual name of the table is COMPUTERS . .

still can't get it to work right

 

[bomayed]

wait , could this be because something wrong with my connection string?

This is my connection string by the way


conn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Computers_DD.mdb;Persist Security Info=False"

 

[SonOfEmidec1100]

The problem is in the syntax of your sql statement not the missing ;. Its the WHERE clause that is invalid, it is not logical in this context. - This is what Ken tried to explain with the examples in his second post.

Another case where you can use a WHERE clause is with an UPDATE statement

conn.execute ("UPDATE TABLE SET FIELD1 = 'NAME' WHERE ID LIKE '" & VarName & "' ")