INSERT INTO exception

[gwbasic]

Hi, everybody,
i've dev a program that access to an mdb database and insert a new record with the command INSERT INTO but when i execute the db command with ExecuteNonQuery the program catch an exception that i did not understand.
I post the oledbcommand line and the fields of the access mdb.

objData = New CData(Application.StartupPath & "\Access.mdb")
objData.DatabaseCommand("INSERT INTO Invoice " & _
"(Number, Notes, Description, Quantity, " & _
"Cost, Import, ServicePayment, Tariff, SimgleUse, " & _
"BedAdd, Kids) VALUES (" & NF & ", '" & StringIntNotes & "', '" & _
StringIntDescription & "', '', '" & StringCost & "', '', '" & _
StringServicePayment & "', '" & StringTariff & "', '" & _
StringSimgleUse & "', '" & StringBedAdd & "', '" & StringKids & "')")

Where:
NF is INTEGER
StringIntNotes, StringIntDescription, StringCost, StringServicePayment, StringTariff, StringSimgleUse, StringBedAdd, StringKids are STRING

And the Access fields:
IDNumbInvoice: Primary Key - AutoIncrement Numeric Field
Number: Numeric
Notes: Text
Description: Text
Quantity: Text
Cost: Text
Import: Text
ServicePayment: Text
Tariff: Text
SimgleUse: Text
BedAdd: Text
Kids: Text

When i try to insert the new record the exception is "Syntax error in the expression INSERT INTO".
Thanks to all that try to solve my problem.

[si_the_geek]

Welcome to VBForums

I recommend checking whether any of your field names (especially Number, but the others too) are Reserved Words, because using them as a field/table name confuses the query parser within the database system, which causes this kind of error.

For lists of Reserved words, see the article What names should I NOT use for tables/fields/views/stored procedures/...? from our Database Development FAQs/Tutorials (at the top of the Database Development forum)

[gwbasic]

Hi, si_the_geek,
thank first of all for your interest, but... i've renamed all the fields of my access table with the prefix 'my'
(so, now i've:
myIDNumbInvoice: Primary Key - AutoIncrement Numeric Field
myNumber: Numeric
myNotes: Text
myDescription: Text
myQuantity: Text
myCost: Text
myImport: Text
myServicePayment: Text
myTariff: Text
mySimgleUse: Text
myBedAdd: Text
myKids: Text)
and the problem isn't solved.

I did not understand why in some sub all is ok but here all is wrong.

Thanx.

[si_the_geek]

There isn't anything obvious... but then we can't see your actual SQL statement, only the code which generates it.

Try temporarily changing objData.DatabaseCommand to Clipboard.SetText , you can then paste the value to Notepad so you can check it, or as a reply here so we can have a look.

[gwbasic]

Hi, si_the_geek,
thank you, i've modified the code so i've see that the exception was generated because there was just a reserved word called "Note".

thx si_the_geek all was solved!