Insert Into Syntax error vb.net

Question

Good Day

I am using VB 2017 to create an application. i am using an Access Database. When i an running my code i get an Insert Into Syntax error

my code is as follows.

Please help.

    Public Shared Function AddLocation(location As Location) As Integer
    Dim connection As OleDbConnection = AutoBeautyCareDB.GetConnection
    Dim insertStatement As String = "Insert Into Location (CUST#,HOSP_ID,FLOOR,ROOM) VALUES(?,?,?,?)"
    Dim insertCommand As OleDbCommand = New OleDbCommand(insertStatement, connection)
    insertCommand.Parameters.AddWithValue("Cust#", location.CustNo.ToString)
    insertCommand.Parameters.AddWithValue("HospId", location.HospId.ToString)
    insertCommand.Parameters.AddWithValue("Floor", location.Floor.ToString)
    insertCommand.Parameters.AddWithValue("Room", location.Room.ToString)
    Try
        connection.Open()
        insertCommand.ExecuteNonQuery()
        Dim selectStatement As String = "Select @@Identity"
        Dim selectCommand As New OleDbCommand(selectStatement, connection)
        insertCommand.CommandText = selectStatement
        Dim locationId As Integer = insertCommand.ExecuteScalar
        Return locationId
    Catch ex As OleDbException
        Throw ex

    Finally
        connection.Close()

    End Try

End Function

Your second column appears to be named HOSP_ID, with an underscore, but you are binding a value to a parameter named HospId, without an underscore. — Tim Biegeleisen
– Tim Biegeleisen, Commented May 12, 2018 at 14:47
@TimBiegeleisen not relevant. In OleDb parameters are positional, their names are not considered — Steve
– Steve, Commented May 12, 2018 at 14:54
You might want to consider providing a full error output, the entire error text verbatim. Also, you might want to provide details to which database you are trying to access (oracle? SQL Server?). Do you know exactly which line causes the error/exception? I would also suggest to use your selectCommand instead of reusing the insertCommand. — YoYo
– YoYo, Commented May 12, 2018 at 16:15

Steve · Accepted Answer · 2018-05-12 14:53:32Z

1

When you use a special symbol like # you need to enclose the field name between square brackets, however it is best to change that name to something less problematic

Dim insertStatement As String = "Insert Into Location
                                 ([CUST#],HOSP_ID,FLOOR,ROOM) 
                                 VALUES(?,?,?,?)"

Also remember that AddWithValue, while it seems to be a useful shortcut, has many problems as explained in the following article

Can we stop using AddWithValue already?

A single line approach with better parameter handling is the following

insertCommand.Parameters.Add("Cust#", OleDbType.Integer).Value = location.CustNo

(Assuming Cust# is an integer type in your database table)

answered May 12, 2018 at 14:53

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tim Biegeleisen Over a year ago

This would have been my second guess +1. I don't know Access at all, but my comment above would be true for several other databases.

Steve Over a year ago

Yes that's true but it is the OleDb provider that forces this behavior. You have the same problem also with Sql Server if you use an OleDb provider.

Collectives™ on Stack Overflow

Insert Into Syntax error vb.net

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related