syntax error in INSERT INTO statement visual basic

Question

i dont know if this has been answered or not but i'm having problem with a syntax error in insert into statement. heres my code, i'm using visual basic 2010, and ms access 2010 as my database

Imports System.Data.OleDb
Imports Comprehensive.Form1
Public Class Form2
Dim cnn As New OleDb.OleDbConnection
Private Sub RefreshData()
    If Not cnn.State = ConnectionState.Open Then
        cnn.Open()
    End If
    Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM items ORDER BY ID", cnn)
    Dim dt As New DataTable
    da.Fill(dt)
    Form1.DataGridView1.DataSource = dt
    cnn.Close()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim cmd As New OleDbCommand
    Try
        If Not cnn.State = ConnectionState.Open Then
            cnn.Open()
        End If


        cmd.Connection = cnn
        cmd.CommandText = "INSERT INTO Items (Item_Name, Item_Type, Date_Added)" + "'VALUES('" & TextBox1.Text  & "','" & TextBox2.Text & "','" & TextBox3.Text & "');'"
        cmd.ExecuteNonQuery()
        RefreshData()
    Catch ex As Exception
        MessageBox.Show(ex.Message & " - " & ex.Source)
        cnn.Close()
    End Try



End Sub

Private Sub Form2_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    'TODO: This line of code loads data into the 'ShitdataDataSet.Items' table. You can move, or remove it, as needed.
    Me.ItemsTableAdapter.Fill(Me.ShitdataDataSet.Items)
    cnn = New OleDb.OleDbConnection
    cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=shitdata.mdb;"
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    Me.Close()
End Sub

End Class

Major SQL injection problem, and there should be no ' before VALUES and after the ending ;. — Joachim Isaksson
– Joachim Isaksson, Commented Mar 3, 2014 at 16:27
You have an extra single quote before your VALUES clause, and there should be a space between the close paranthesis in your column list and the VALUES keyword, as well as an extra single quote at the end of your statement. — AJ.
– AJ., Commented Mar 3, 2014 at 16:28

Community · Accepted Answer · 2017-05-23 11:49:45Z

1

The INSERT line contains single quotes not needed before the VALUES keyword AND at the end of the sql statement, remove them

 (Item_Name, Item_Type, Date_Added) VALUES(....) 
                                  ^^^          ^^^

But as usual, you don't write sql commands in this way.
You should always use a parameterized query (SQL Injections)

    cmd.Connection = cnn
    cmd.CommandText = "INSERT INTO Items (Item_Name, Item_Type, Date_Added)" & _
                      "VALUES(?, ?, ?)"
    cmd.Parameters.AddWithValue("@p1", TextBox1.Text)
    cmd.Parameters.AddWithValue("@p2", TextBox2.Text)
    cmd.Parameters.AddWithValue("@p3", TextBox3.Text)
    cmd.ExecuteNonQuery()

And, as a last note, keep in mind that if you have a datetime in your table (as it should) for the Date_Added field then you need to convert the textbox3 value to a datetime.

If this is the case, then, I recommend to look at this question where the problem of inserting datetime values in access databases has been resolved.

edited May 23, 2017 at 11:49

CommunityBot

11 silver badge

answered Mar 3, 2014 at 16:28

Steve

217k22 gold badges242 silver badges296 bronze badges

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

6 Comments

user3375678 Over a year ago

woah that was fast reply, anyway thanks man, that solves y problem, guess all i have to worry about is the update and delete buttons on the program

user3375678 Over a year ago

parameterized query? first time i heard that, and my prof did not discuss anything about that, guess i need to do more research other than listening to my teachers

Steve Over a year ago

Absolutely, look at the links given in its comment from Mr Isaksson or mine for a more formal discussion of the problem (a big one indeed)

Steve Over a year ago

And there is also the problem of parsing text. What happen in a concatenated sql string when TextBox1.Text contains a single quote? Try it and show it to your teacher :-)

user3375678 Over a year ago

got an error "expression expected" dont know f i did that right

|

Collectives™ on Stack Overflow

syntax error in INSERT INTO statement visual basic

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related