CODEWITHSUNDEEP
vb.net
Surendran Kershan Naidoo
Surendran Kershan Naidoo

Reputation: 9

Insert Into Syntax error vb.net

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

Upvotes: 0

Views: 56

Answers (1)

Steve
Steve

Reputation: 216302

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)

Upvotes: 1

Related Questions