Possible Bug Updating MS Acess number fields

S

Sebb77

Member
Joined
Feb 27, 2003
Messages
11
Location
Malta
Im trying to update a number column of a MS Access (2000 or XP) database using VB.Net DataAdapter. The form has a datagrid binded to the created datasets datatable and when I create a new row in the datagrid and run the update method of the dataadapter, for the binded datatable
(da.Update (ds, "testtbl")), an error is shown displaying "Syntax Error in INSERT INTO statement".
If i remove the number column from the insert statement, and its relevant parameter, everything works fine, but with number, of any type (double, currency, integer...), this error is displayed.

I also tried this, just using textboxes binded to the datatables columns but the same error is shown. This error does not occur if i use any other database, such as oracle.

Does anyone knows if this is a bug of the jet engine for MS Access, or if im doing something wrong (very difficult, because the only code I do is the fill and update method call) and how can I solve this problem?

Thank you for your help.
 
The select statement is created by the dataadapter wizard.
Maybe there is something wrong with the connection string?

This is the code :

Me.OleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\t\db1.mdb;Mode=Share Deny None;Extended Pro" & _
"perties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Dat" & _
"abase Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet " & _
"OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:N" & _
"ew Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt" & _
" Database=False;Jet OLEDB:Dont Copy Locale on Compact=False;Jet OLEDB:Compact W" & _
"ithout Replica Repair=False;Jet OLEDB:SFP=False"

OleDbInsertCommand1

Me.OleDbInsertCommand1.CommandText = "INSERT INTO t(id, value) VALUES (?, ?)"
Me.OleDbInsertCommand1.Connection = Me.OleDbConnection1
Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("id", System.Data.OleDb.OleDbType.VarWChar, 5, "id"))
Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("value", System.Data.OleDb.OleDbType.VarWChar, 3, "value"))
 
Sorry, i just noticed that i changed the second parameter, for testing purposes.

The original code was:

Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("value", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(10, Byte), CType(0, Byte), "value", System.Data.DataRowVersion.Current, Nothing))
 
Have you tried:

"INSERT INTO testtbl(id, value)

From what i can see your dataset is using the table "testtbl" yet your insert into statement uses the table "t".

Also I wouldnt reccomend using SQL reserved words in your database as table, field or query names.
 
thank you very much!
The problem was the name of the number field (value). I didnt know that it is a reserved word!!!
 
Back
Top