Getting Insert statement Error, I cant figure out whats wrong though.

Question

I am trying to insert into a DB with has the following columns: ID (autonumber), BonderIdentifier (text), Username (text), Login (date), Logout (date).

BonderIdentifier, Username, Login is the PK.

Here is what I do:

 Public Function submitNewToDB(ByVal sessionData As BonderSession) As Boolean
    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 Session ([BonderIdentifier], [Username], [Login]) values ('{0}', '{1}', '{2}')", sessionData.bonderIdentifier _
                 , sessionData.username, sessionData.login)
        cmd = New OleDbCommand(str, cn)
        cmd.ExecuteNonQuery()
        cn.Close()

    Catch ex As Exception
        Return False

    End Try

    Return True

End Function

Like I said I get an insert into error and I dont know why. Nothing is in the DB yet and the table is created.

EDIT I ran the built string in Access as such:

Insert into Session ([BonderIdentifier], [Username], [Login]) values ('Mork', 'sean', '2/23/2010 11:12:42 AM')

And it works.... but in VS it doesnt.

Answer 1

I am suspecting the problem is with the way the date is getting converted to a string. Can you post the query output (after substitution), so we can see the real issue?

Answer 2

I've rewritten your code to use the Using statement and Parameters.

Public Sub SubmitSessionToDB(ByVal sessionData As BonderSession)
    Using conn AS New OleDbConnection("Provider=microsoft.Jet.OLEDB.4.0;Data Source=G:\Sean\BMSBonder3_0.mdb;")
        Using cmd AS New OleDbCommand("Insert into Session ([BonderIdentifier], [Username], [Login]) values (@BonderId, @Username, @Login)", conn)
                cmd.CommandType = CommandType.Text
                cmd.Parameters.AddWithValue("@BonderId", sessionData.bonderIdentifier)
                cmd.Parameters.AddWithValue("@Username", sessionData.username)
                cmd.Parameters.AddWithValue("@Login", sessionData.login)

                conn.Open()
                cmd.ExecuteNonQuery()                
        End Using
    End Using
End Sub

I wrote it without Visual Studio, so there might be some small typo's.

The using statement ensures that the connection is always closed (even when an exception occurs).
The parameters prevent SQL injection, adds readability and make errors easier to detect.

If you still need a return value, you can add the try catch you had.
Please let me know if this resolves the syntax error as well.

Answer 3

When working with an Access/Jet database engine, dates are delimited with the # sign, not the ' sign. Try changing your statement to:

str = String.Format("Insert into Session ([BonderIdentifier], [Username], [Login]) values ('{0}', '{1}', #{2}#)" _
             , sessionData.bonderIdentifier, sessionData.username, sessionData.login)

Answer 4

That it is a syntax error means that there is something wrong with your statement (str).

What is the exact value of str when the error occurs? I am guessing that it is something like this:

Insert into Session ([BonderIdentifier], [Username], [Login]) values ('Bill O'Brien', 'some text', 'some other text')"

The ' in Bill O'Brien is the issue in my example. The parser thinks the ' closes out the text value and then sees more text instead of a comma or a )

It could also be bad data formatting that Access is not understanding as a date. I would like to see the string to identify the problem.

EDIT:

Access needs date values to be passed in with # surrounding the value, and not '.

Like this:

Insert into Session ([BonderIdentifier], [Username], [Login]) values ('Bill OBrien', 'some text', #yourdatevalue#)"