Manish Sawjiani

Dear Experts

i am getting "Syntax error in INSERT INTO statement" when i try the following

Dim aRow As DataRow = ds.Tables("Friends").NewRow

Dim cmdBld As OleDbCommandBuilder

aRow("Fname") = "Sam"

aRow("Lname") = "Davis"

aRow("Area") = "Bronx"

ds.Tables("Friends").Rows.Add(aRow)

cmdBld = New OleDbCommandBuilder(daFriends)

daFriends.Update(ds, "Friends") '--- It is here i get the error

ds.Tables("Friends").AcceptChanges()

Please help and Thanks in advance

Manish

So near yet so far away.....



Re: Visual Basic Express Edition Inserting a New Recrod using Data Set a Syntax error

Manish Sawjiani

Hi Manish

The error is because OleDbCommandBuilder goes crazy if there is an autonumber field in the select statement. Therefore Manish try a Select statement as follows

Select Fname,Lname,Area from Friends. Probably if there is a way to fool OleDBCommandBuilder and make him skip the autonumber field i will let you know.

Cheers

Manish





Re: Visual Basic Express Edition Inserting a New Recrod using Data Set a Syntax error

Riquel Dong ĘC MSFT

Hi Manish,

Based on your post, when you use Update to save the data into the database the error occurs.

Because your application automatically generates commands, you can take advantage of the DbCommandBuilder object to automatically generate the DeleteCommand, InsertCommand, and UpdateCommand of the DbDataAdapter when your DataTable maps to or is generated from a single database table. Second please note that Limitations of Automatic Command Generation Logic and ensure that your application doesn't fall into these scenarios. For more information about Limitations of Automatic Command Generation Logic, please visit: http://msdn2.microsoft.com/en-us/library/tf579hcz(VS.80).aspx.

I use OleDbCommandBuilder to automatically generate commands to manipulate one data table in an Access database. It contains three columns. One is autonumber column and the other column is primary key column. The third column is ordinary column. I can save data change to database in this scenario. So I think the problem doesn't relate to autonumber column in your application. Please check your select query statement and limitations of automatic command generation. If you have any further questions, please tell me.

Best regards,

Riquel







Re: Visual Basic Express Edition Inserting a New Recrod using Data Set a Syntax error

Manish Sawjiani

Thanks Riquel

You are right. OleDbCommandBuilder had nothing to do with auto number. In fact the syntax error INSERT INTO statement was coming because there were 2 fields i.e. Address 1 and Address 2 with space. I removed the space and it started working like a charm!

Please correct me if i am wrong - about this one:

  • Space in the field name is better avoided
  • Reserved Words are to be avoided

Thanks again

Manish