ADO and SQL syntax

[jozino01]

hi,
my vb6 code:

sSQL = "INSERT INTO OUTTURN (CUTID, SIZE, GRADE, LOT, VOLUME, PRICE, REVENUE, PERCENTAGE, INVOICE, CUSTOMER) VALUES ('" & Text2.Text & "', '" & ws1.Cells(i, 1) & "', '" & ws1.Cells(i, 2) & "', '" & ws1.Cells(i, 3) & "', '" & ws1.Cells(i, 4) & "', '" & ws1.Cells(i, 5) & "', '" & ws1.Cells(i, 6) & "', '" & ws1.Cells(i, 7) & "', '" & ws1.Cells(i, 8) & "', '" & ws1.Cells(i, 9) & "'); "
Set rsAccess = New ADODB.Recordset
rsAccess.CursorLocation = adUseClient
rsAccess.Open sSQL, objAccessConnection, adOpenKeyset, adLockOptimistic

generates this SQL command:

INSERT INTO OUTTURN (CUTID, SIZE, GRADE, LOT, VOLUME, PRICE, REVENUE, PERCENTAGE, INVOICE, CUSTOMER) VALUES ('1', '12 X 12 (20)', '2 MER & BTR', 'JGH 4200', '24960', '560', '13977.6', '0.0191063494931', '988', 'SOUTHBEACH-9348');

and it gives me SYNTAX ERROR in my program, but when I copy/paste and run it directly as Access 2000 query it works fine.

Any idea, please?

 

[RonRepp]

I usually use the Con.Execute command instead of using a recordset whenever I'm doing something other than a select query.

It would go something like this:

    Dim Con as ADODB.Connection
    Set Con = New ADODB.Connection
    (enter your connection string here)
    (Concatenate your SQL string)      
    Con.ConnectionTimeout = 0
    Con.Open Con.ConnectionString
    Con.Execute(sSQL)

I hope this helps.





Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[jozino01]

i tried your code and I got the same error message as with my original code - "Syntax error in INSERT INTO statement

 

[RonRepp]

It looks like you have everything in single quotes, and I thought Access was supposed to be double quotes. Other than that, if you have any of those numbers going into a column that is integer/double/number oriented, you would not use the quotes.

Sorry I can't help more.



Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[simonkue]

Have you tried it without the trailing ; ?

 

[peter0480]

The column 'Size' may be the culprit. This is a reserved word, which can cause the query to fail depending on where it's run from.

You could either change the column name or, whenever you refer to it in queries, enclose it in brackets like [Size].

 

[AEtherson]

What is the error message?

just guess work without it

 

[jozino01]

thanks for all your input, specially peter0480. it was the word SIZE causing trouble.

 

[gmmastros]

That's what I initially thought too. So I did a google search and came up with:

http://support.microsoft.com/kb/q209187/

Size is not on the list. I wonder why?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jozino01]

well, i guess SIZE is a reserved word only for ADO, not for Access...?

 

[abaldwin]

Jozino)1,

The web site you listed showed reserved words for Access2000 and had a disclaimer that it may not be inclusive.

Checkout this link

http://developer.mimer.com/validator/sql-reserved-words.tml

Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[jozino01]

gmmastros listed that site

anyway, thanks for update.

 

[abaldwin]

Uh No.....two different urls there. Mimer is more in depth than the Microsoft one.

Glad you got it fixed.

later

Andy Baldwin

"Testing is the most overlooked programming language on the books!