Visual Basic.NET Syntax error in INSERT INTO statement?

[Versaiteis]

Alright, so I keep getting this error every time I try to add a record to an access database. I've checked all of my columns and everything I could think of but I do not believe any of them are reserved by access.

What I'm asking is either what exactly is the problem here and/or how do I view the INSERT INTO statement. The error occurs during run time. When the program crashes it says "OleDBException was unhandled" and the syntax error above.

What's weird about this is that I have another database in the bin folder and if I change

Data source= FWDatabase.mdb

to

Data source= AddressBook.mdb

The whole program works absolutely fine. I am able to add the new record to the AddressBook database, but to change it to the FWDatabase database throws the same error everytime =\

Here is the code:

Public Class Form1
    Dim inc As Integer
    Dim MaxRows As Integer

    Dim con As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        con.ConnectionString = "[PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = FWDatabase.mdb]"

        con.Open()
        sql = "SELECT * FROM tblClients"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "AddressBook")
        con.Close()

        MaxRows = ds.Tables("AddressBook").Rows.Count
        inc = 0
        NavigateRecords()

    End Sub

Private Sub btnCommit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCommit.Click
        If inc <> -1 Then
            Dim cb As New OleDb.OleDbCommandBuilder(da)
            Dim dsNewRow As DataRow

            dsNewRow = ds.Tables("AddressBook").NewRow()

            dsNewRow.Item(1) = txtFirstName.Text
            dsNewRow.Item(2) = Date.Now

            ds.Tables("AddressBook").Rows.Add(dsNewRow)

            da.Update(ds, "AddressBook")

            MsgBox("New Record added to Database")

            btnCommit.Enabled = False
            btnAddNew.Enabled = True
            btnUpdate.Enabled = True
            btnDelete.Enabled = True
        End If
    End Sub
End Class

 

[r3plica]

slap in a try catch block and put your code in that, then you will be able to see what the error being generated is.
My guess is incorrect permissions (long shot guess I know but i'm feeling lucky).

 

[Versaiteis]

Alright, cool, I've got limited experience with the try/catch method, but I'm sure I can find something online.

Should it be a problem with permissions how would I fix that?

 

[Tom]

Yes definitely read up on it, it will save your apps from completly crashing and also give you a more detailed description of the errors your receiving. Also I would suggest setting a break point and stepping thru you code.

 

[Versaiteis]

Well I've got it setup like this

Private Sub btnCommit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCommit.Click
       Try
           'Previous code here
       Catch ex as Exception
           MsgBox(ex.Message)
       End Try
End Class

and all it returns is "Syntax error in INSERT INTO statement."

Still not quite sure what this means =\

 

[Versaiteis]

Alright, so I had a bit of a breakthrough, though my problem still persists

I managed to get the command builder to display its InsertCommand text into a message box and got this:

INSERT INTO tblClients (ID, First Name, Last Name) VALUES (?,?,?)

I would assume that this is what the code is throwing as a syntax error, but looking at it I see no syntax errors in this sql statement =\ not to mention I do not know how to change it

 

[r3plica]

you have referenced an ID column for your insert. Is this correct or is the ID an autonumber?
If it is an identy column, then you should not have it as part of your insert statement as that is an error

I hope that helps

 

[MattP]

Alright, so I had a bit of a breakthrough, though my problem still persists

I managed to get the command builder to display its InsertCommand text into a message box and got this:

INSERT INTO tblClients (ID, First Name, Last Name) VALUES (?,?,?)

I would assume that this is what the code is throwing as a syntax error, but looking at it I see no syntax errors in this sql statement =\ not to mention I do not know how to change it

I would have a look into how to handle multi-word column names.

 

[Tom]

I agree with r3plica's point about the Id field...
Also not to take ya in a different direction but I would suggest not even using a command builder but instead creating your own statements. CommandBuilders are inefficient and also lack flexibility with future proofing. For instance I have a app in production heavy on command builders and typed datasets, every time someone updates the database tables (adds a column) I have to go into the app and update the typed dataset to match the new changes or the program will crash (despite nothing in the program using the new columns) and then recompile and redistribute the app.

 

[Versaiteis]

Actually the column was not set to autonumber, but I did notice that the database that does work with the program set the ID column as autonumber and its Insert command did not include the ID field. So I did the same for the other database. Its Insert command looks like this now

INSERT INTO tblClients (First Name, Last Name) VALUES (?,?)

however, it still returns a syntax error for the Insert Into statement =\

I think there must be some difference between the databases, but I can't quite tell what that is.

Also, using the command builder is the only way I know how to incorporate the database, or at least update it. I haven't really looked up other ways to do it. I had the hardest time using databases for a while so I'm still very new to it.

Thank you to everyone that provided a solution, glad to know that help can at least be found somewhere on the net ^^

 

[Versaiteis]

Solution

Wow, so now I feel a little dumb lol

so my Insert command was

INSERT INTO tblClients (First Name, Last Name) VALUES (?,?)

Well apparantly, and this is something I was unaware of, it is considered a syntax error to include a space in the column fields

a Simple change to

INSERT INTO tblClients (First_Name, Last_Name) VALUES (?,?)

and the code worked flawlessly -_-

 

[MattP]

While it's better practice to rename the columns like you did in the database you may come across a database where you don't have control over the column names.

In that situation you can put the names in brackets to have multiple words treated as a column name.

INSERT INTO tblClients ([First Name], [Last Name]) VALUE (?,?)