Inserting into a Access DB with an AutoNumber PK and getting an Insert syntax error

Question

I am trying to insert into a access db and the PK is an autonumber that is generated by the DB. I am getting a syntax error for the insert statement and dont know why. If possible I would like to return the autonumber value in the same statement.

Here is what I got so far.

   Dim cn As OleDbConnection
    Dim cmd As OleDbCommand
    Dim str As String

    Try
        cn = New OleDbConnection("Provider=microsoft.Jet.OLEDB.4.0;Data Source=G:\Sean\BMSBonder3_0.mdb;")
        cn.Open()
        str = String.Format("Insert into Bonder(BonderName, BonderConfig, Date, User) values ('{0}', '{1}', '{2}', '{3}')", addBonder.BonderName _
                    , xmlString, Date.Now.ToString, Environment.UserName)
        MsgBox(str)
        cmd = New OleDbCommand(str, cn)
        cmd.ExecuteNonQuery()
        cn.Close()

    Catch ex As Exception
        MsgBox(ex.Message)
        Return False
    End Try

    Return Nothing

Coding in VB.Net obviously. In the query all the field values are set except the autonumber field.

Answer 1

I'd bet dollars to donuts that the problem is because Date and User are reserved words. Enclose them in square brackets:

str = String.Format("Insert into Bonder(BonderName, BonderConfig, [Date], [User]) ....

Better yet, rename the fields in the database to non-reserved words to prevent further headaches.

Answer 2

Put a breakpoint on the line

cmd.ExecuteNonQuery()

and copy the value of str out to the Access query window and run the query from there. The Access query window may pinpoint the problem.

You will need to put a hash either side of your date if inserting dates into Access. So the value:

Date.Now.ToString

will be:

"#" & Date.Now.ToString("yyyy-mm-dd") & "#"

Answer 3

In addition to hardcode's comments, the delimiter for dates in Access is hash (I see now that this was mentioned by hawbsl):

 "Insert into Bonder(BonderName, BonderConfig, Date, User) values ('{0}', '{1}', #{2}#, '{3}')

To get the number, you need a second statement on the same connection:

SELECT @@identity