index > Windows Forms Data Controls and Databinding > Syntax error in INSERT INTO statement in vb2008 express and 2007accdb

Syntax error in INSERT INTO statement in vb2008 express and 2007accdb

I have created a database tablein accdb2007.In the VB2008 express edition, when running the project, it loads all the data into the datagrid view and into my checkboxes.I also can navigate through therecordsin the textboxes.However, when trying to programatically Adding New record it throws this error: Syntax error in INSERT INTO statement.So it won't let me add or update a record. I have no idea what's causing this. Need help.
Thanx

learningvb8 Wednesday, July 15, 2009 11:47 PM

Can you post the code that you are using to Insert the data ?

Ashray Lavsi
If my post answers your Question, don't forget to "Mark it as Answer"

Ashray Lavsi Thursday, July 16, 2009 12:11 PM

Here is the code I'm using to add / update records programatically.

da.Fill(ds, ("andmytable")theda.update statement had to be replaced with the da.Fill statement because the database provider was a Jet.OLEDB.4.0 and the da.update worked great there. But thisthrew a "unrecognized database format" error with my database from microsoft office 2007ACCDB. So I looked at my database explorer and found out that my provider is ACE.OLEDB.12.0. Once I had changed it in the code the above error was gone.
However, then I got a "Syntax error in INSERT INTO statement". So I changed out the da.update with da.fill and the error is gone.
Now the entire prgram is working without an error message.However, it really doesn't update nor add a new record anymore.
Any Idea what code to use for this ACE.OLEDB.12.0 provider?
Thanks

Edited bylearningvb8 Tuesday, July 21, 2009 8:59 PM

learningvb8 Thursday, July 16, 2009 4:50 PM

Hi learningvb8,

After I read your code carefully, I found you have used sql = "SELECT * FROM my table" as da's command, I don't see any update/insert command in this post, And also I don't see using OleDbCommandBuilder to generate sql for you. So when you call da.Update, it will cause error since it doesn't find any update command.

You can put a break point to see if the "da.UpdateCommand.CommandText" is empty or not correct. You shuld set that text correct in order to call the update method.

Sincerely,
Kira Qian

Please mark the replies as answers if they help and unmark if they don't.

Kira Qian Friday, July 17, 2009 7:56 AM

Kira, if u look closer at the thread above u'rs u can see that the Dim cb As New OleDb.OleDbCommandBuilder(da) is right in there.Its right after the if inc <>-1 code. I still get the "Syntax error in INSERT INTO statement". Again, this coder worked perfectly with the old database downloaded from the internet for the tutorial i went through. Its the Nwind database with Provider=Microsoft.Jet.OLEDB.4.0. However, the coulmns are named CompanyName, Contact Name and so forth. I like to have different names for the culmns.In order to change the names I had to convert the old database to the newer one whcih is access2007. Now the provider has changed to Provider=Microsoft.ACE.OLEDB.12.0. The rest of the program still works but the update and addnew record won't work anymore. It generates the syntax error and won't update or add the records anymore.
I Still need help.
Thanks Kira

learningvb8 Friday, July 17, 2009 6:15 PM

Can you please repost your code using the "Insert Code Block" and without your comments in between so that I can copy-paste it in a new project and check. Also make sure that the names of the Columns you have don't have any white spaces, as sometimes it results in an error.. i.e. use ColumnName instead of Column Name

Ashray Lavsi
If my post answers your Question, don't forget to "Mark it as Answer"

Ashray Lavsi Friday, July 17, 2009 8:34 PM

Private Sub BtnCommit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnCommit.Click

Oh yeah, I just didclearout thespace inthe column names and got this error:No value given for one or more required parameters.
I've just changed the column names back with the space inbetween, now the program works like before. Ofcourse the update and addnew record is still not working. So I still need help for it.
Thanks

Edited bylearningvb8 Saturday, July 18, 2009 12:22 AM
Edited bylearningvb8 Saturday, July 18, 2009 12:14 AM
Edited bylearningvb8 Tuesday, July 21, 2009 8:56 PM

learningvb8 Friday, July 17, 2009 11:45 PM

Well, it should be pretty obvious that the .Fill() fills a DataSet/DataTable from the database, whereas the .Update() sends updates to the database. I don't know why you would have expected it to work otherwise.

What you've posted, as far as using the CommandBuilder, looks ok ... but perhaps the CommandBuilder does not work well with that particular Provider. The best way to find out is, as Kira suggested, take a look at the UpdateCommand that the CommandBuilder builds for you. Maybe post some of that code for us to take a peek at.

~~Bonnie Berent [C# MVP]

BonnieB Sunday, July 19, 2009 6:00 PM

Hi Bonnie, thanx for u'r review.Howeve, I forgot to tell u that the original code was da.update(ds,mytablename).It just gave me the syntax error "INSERT INTO" tha's when i've changed it fo fill. Sorry for the confusion. I'm not advanced enough to know about the command builder. Don't even know how to look at the update command builder. Here is the code I have in the form1 load.

Again, this code worked fine with the jet.oledb.4.0, since i had to convert it in order to change the column names it changed the provider to ace.12.0 and won't take the code anymore. I'm still in vista32bit where it worked fine with the northwind .mdb extension.
Since the conversion it just won't work. It seems like u guys suggest that the commandbuilder isn't compatible with the new provider. I just don't know how to make it work. Do u know how to make it work for me?
Thanks
Nick

Edited bylearningvb8 Tuesday, July 21, 2009 8:56 PM

learningvb8 Sunday, July 19, 2009 6:18 PM

I'm not advanced enough to know about the command builder. Don't even know how to look at the update command builder.

Nick,

But I assume you *do* know how to set breakpoints and look at the value of variables in the debugger, correct?

So, set a breakpoint on the line

Dim cb As New OleDb.OleDbCommandBuilder(da)

and see what you can see. In particular, take a look at this:

cb.DataAdapter.UpdateCommand.CommandText

~~Bonnie Berent [C# MVP]

BonnieB Sunday, July 19, 2009 10:57 PM

Hi Bonnie, I did set a breakpoint at Dim cb As New OleDb.OleDbCommandBuilder(da), ran the program andused the commit button.
It turned the red breakpoint yellow. When putting the mouse cursor on top ot the (da) it shows a + sign. Clicking it opens up a drop down menu. It shows all the stuff the dataadappter could do. It showed the Insert Command in the left column and the word "nothing" in the right column. And the update command is also "nothing". When put the cursor over the cb it shows "cb|nothing" as well. This command "cb.DataAdapter.UpdateCommand.CommandText" is not even appearing in there. Is this tell u somthing? I don't know what to do with the info. I just would like to program accept the code to add new record to the database.
Thanks Bonnie
Nick

learningvb8 Tuesday, July 21, 2009 9:10 PM

Hi Nick,

From your description above, it sounds to me like you're not very used to using the debugger. Get used to it, it's very, veryuseful. ;0)

Anyway, I'm guessing that you didn't actually run the statement that you set the breakpoint on, you can hit F10 to run justthe highlightedstatement, and then look at the properties again. (Alternatively, you can set the breakpoint on the statement just after the New OleDbCommandBuilder).

~~Bonnie Berent [C# MVP]

BonnieB Tuesday, July 21, 2009 10:29 PM

Hi Bonnie,
Thanx, u'r right I didn't know...just learned more...well, I just did do the above to the Dim cb As New OleDb.OleDbCommandBuilder(da) line...that's when it jumped up to Public Class Form1, to the line "Dim con As New OleDb.OleDbConnection" and turned the word "con" yellow...when i put the cursor on top of it, it reads "con|nothing"...at the "Dim cb As New OleDb.OleDbCommandBuilder(da)" where thebreakpoint is set, mouse cursor on "(da)" reads "da|nothing"...
well, for what ever reason, now when setting the breakpoint to "Dim dsNewRow As DataRow"which is next line down...hit F10 the breakpoint jumps down the next line to "dsNewRow = ds.Tables("Personal_Finance_Assistant_Table1").NewRow()"...don't know why its doing this but that's what it does...and it does turn the word "con" yellow in the Public Class Form1 with "con|nothing"...as a matter of fact, it does this no matter where i set the breakpoint on...when setting the breakpoint on this line "da.Update(ds, "Personal_Finance_Assistant_Table1")" that's where i got the syntax error "INSERT INTO" and put mouse cursor on top of the (ds, it also reads "ds|nothing" and turnes the word "con" yellow just like it does like before...seems like that everything's "nothing"
That's what it shows in the immediate window with breakpoint at "Dim con As New OleDb.OleDbConnection"

Step into: Stepping over non-user code 'Project_Personal_Finance_Assistant.My.MyApplication.New'