0

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

user3375678
  • 1
  • 1
  • 1
  • 2
    Major [SQL injection](https://xkcd.com/327/) problem, and there should be no `'` before VALUES and after the ending `;`. – Joachim Isaksson Mar 03 '14 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. Mar 03 '14 at 16:28
  • [Give me parameterized SQL, or give me death](http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/)!!! – huMpty duMpty Mar 03 '14 at 16:29

1 Answers1

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.

Community
  • 1
  • 1
Steve
  • 213,761
  • 22
  • 232
  • 286
  • 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 Mar 03 '14 at 16:32
  • 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 – user3375678 Mar 03 '14 at 16:37
  • Absolutely, look at the links given in its comment from [Mr Isaksson](http://stackoverflow.com/users/477878/joachim-isaksson) or mine for a more formal discussion of the problem (a big one indeed) – Steve Mar 03 '14 at 16:41
  • 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 :-) – Steve Mar 03 '14 at 16:42
  • got an error "expression expected" dont know f i did that right – user3375678 Mar 03 '14 at 16:49
  • yeah i noticed that, had to remove the semicolons in it – user3375678 Mar 03 '14 at 16:55