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, Aug 23 '15 at 07:10
Try setting a breakpoint to see SQL before executing it. Or [Debug.Print](http://stackoverflow.com/q/2916287/673826) it. — mlt, Aug 23 '15 at 07:12
[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection — marc_s, Aug 23 '15 at 07:15

score 2 · Accepted Answer · edited May 23 '17 at 12:00

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 '17 at 12:00

Community

1
1

answered Aug 23 '15 at 07:15

Steve

213,761
22
232
286

thanks for the replies will try those corrections :) – Maria Calif Aug 23 '15 at 07:22
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 – Maria Calif Aug 23 '15 at 07:51
Did you put the square brackets around the SECTION name? In the example with the parameters I have wrongly omitted them. – Steve Aug 23 '15 at 07:55
yeahp corrected it with [Section] instead of Section – Maria Calif Aug 23 '15 at 07:56
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? – Steve Aug 23 '15 at 08:00
cnn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\Guidance System.accdb") ok i will check that and here is my connection string. @steve – Maria Calif Aug 23 '15 at 08:01
Just a note, when debugging the Application.StartupPath is the BIN\DEBUG folder (or x86 variant) so your database is in that folder not in the project folder. – Steve Aug 23 '15 at 08:14
it working without any problem now. you saved my day thanks :D @steve – Maria Calif Aug 24 '15 at 08:27

SYNTAX ERROR INSERT INTO STATEMENT Visual Basic

1 Answers1