SYNTAX ERROR INSERT INTO STATEMENT Visual Basic

Question

Why am I getting this error

Syntax error INSERT INTO statement

Please help! Thanks in advance!

Dim cmd As New OleDb.OleDbCommand
    If TabControl1.SelectedIndex = 0 Then

        If Not cnn.State = ConnectionState.Open Then
            'open connection if it is not yet open
            cnn.Open()
        End If

        cmd.Connection = cnn
        'check whether add new or update
        If Me.txtStdID.Tag & "" = "" Then
            'add new 
            'add data to table
            cmd.CommandText = "INSERT INTO Student (StudentID, LastName, FirstName, MiddleInitial, Grade, Section, ContactNumber, AdviserID, CounselorID, ParentName)" & _
                            "VALUES('" & Me.txtStdID.Text & "','" & Me.txtLname.Text & "','" & _
                            Me.txtFname.Text & "','" & Me.txtMidInt.Text & "','" & _
                            Me.txtGrade.Text & "','" & Me.txtSection.Text & "','" & Me.txtContact.Text & "','" & _
                            Me.txtAdvID.Text & "','" & Me.txtConID.Text & "','" & Me.txtPname.Text & "')"
            cmd.ExecuteNonQuery()

To answer this, we must know mapping of data types to the columns. I assume you probably try to insert textual data to numerical field. — Nissim
– Nissim, Commented Aug 23, 2015 at 7:10
Try setting a breakpoint to see SQL before executing it. Or Debug.Print it. — mlt
– mlt, Commented Aug 23, 2015 at 7:12
SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection — marc_s
– marc_s, Commented Aug 23, 2015 at 7:15

Community · Accepted Answer · 2017-05-23 12:00:55Z

2

Well, this is a well known problem. Databases define many words as "reserved keywords", and if they are used for column names or table names, they need to be enclosed in the appropriate quoting character for your database.

Seeing that you are using an OleDbConnection I assume that you are using MS-Access as database. In that case the list of reserved keywords could be found here,

And indeed SECTION is a reserved keyword, so your query, should be written as

"INSERT INTO Student (......, [Section], ......

Said that, let's say something about string concatenation to build an SQL Query.

It's bad, bad, bad.... There are numerous problem with that. For example, what happens if one of your fields contains a single quote? The whole query will fail again with a Syntax error. Also, albeit more difficult to exploit with Access because it doesn't support multiple command texts there is the problem of SQL Injection to avoid at all costs. You need to learn how to use a PARAMETERIZED QUERY

Dim sqlText = "INSERT INTO Student (StudentID, LastName, FirstName, " & _ 
              "MiddleInitial, Grade, [Section], ContactNumber, AdviserID, " & _ 
              "CounselorID, ParentName) VALUES (?,?,?,?,?,?,?,?,?,?)"
If TabControl1.SelectedIndex = 0 Then
    Using cnn = New OleDbConnection(...constring here....)
    Using cmd = new OleDbCommand(sqlText, cnn)
       cnn.Open()
       cmd.Parameters.Add("@p1", OleDbType.VarWChar).Value = Me.txtStdID.Text
       cmd.Parameters.Add("@p2", OleDbType.VarWChar).Value = Me.txtLname.Text
       .... and so on with the other parameters ....
       .... strictly following the order of the fields in the insert....
       cmd.ExecuteNonQuery()
  End Using
  End Using

edited May 23, 2017 at 12:00

CommunityBot

11 silver badge

answered Aug 23, 2015 at 7:15

Steve

217k22 gold badges242 silver badges296 bronze badges

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

8 Comments

Maria Calif Over a year ago

thanks for the replies will try those corrections :)

Maria Calif Over a year ago

followed it but it still gives me "The INSERT INTO statement contains the following unknown field name: 'StudentID'. Make sure you have typed the name correctly, and try the operation again." even though i typed it correctly @steve

Steve Over a year ago

Did you put the square brackets around the SECTION name? In the example with the parameters I have wrongly omitted them.

Maria Calif Over a year ago

yeahp corrected it with [Section] instead of Section

Steve Over a year ago

Well, if the error is StudentID is an unknown field then there is no field with that name in your database table. Check it and also check the database used in the connection string. Do you use |DataDirectory| in the connection string?

|

Collectives™ on Stack Overflow

SYNTAX ERROR INSERT INTO STATEMENT Visual Basic

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related