none
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll Additional information: Syntax error in INSERT INTO statement. RRS feed

  • Question

  •     Dim cmd As New OleDbCommand("insert into studnt (namtlb,date,addres) values ('" & TextBox1.Text & "',#" & DateTimePicker1.Value.ToString("yyy/MM/dd") & "#,'" & TextBox2.Text & "'", con)
            REM   Dim cmd As New OleDbCommand("insert into studnt (namtlb,date,addres) values ('" &  & "',#" &  & "#,'" &  & "'", con)

            con.Open()
            cmd.ExecuteNonQuery()

            con.Close()



            MessageBox.Show("تم الحفظ بنجاح")
            TextBox1.Text = String.Empty
            TextBox2.Text = String.Empty

    • Moved by Kristin Xie Friday, November 20, 2015 8:26 AM related to ado.net
    Wednesday, November 18, 2015 11:22 PM

Answers

All replies

  • Something in the input data like , or ' is coming in causing the T-SQL to be malformed and causing a syntax error with the T-SQL.  With you having Textboxes in the T-SQL formulation where you can have someone enter characters as mentioned, not checking for the characters and making the necessary corrections to the data to not produce malformed T-SQL is bad news for you.
    Thursday, November 19, 2015 1:13 AM
  • Hi.

    why not output the cmd.CommandText before the cmd.ExcuteNoQuery

    then copy the output command text string to SQL query Window and run executing to find what error reason it happen.


    DON'T TRY SO HARD,THE BEST THINGS COME WHEN YOU LEAST EXPECT THEM TO.

    Thursday, November 19, 2015 2:01 AM
  • You really should be using parameters!

    Dim cmd As New OleDbCommand("insert into studnt (namtlb,date,addres) values (@name, @date, @address)", con)
    cmd.Parameters.AddWithValue("@name", TextBox1.Text)
    cmd.Parameters.AddWithValue("@date", DateTimePicker1.Value.ToString("yyy/MM/dd"))
    cmd.Parameters.AddWithValue("@address", TextBox2.Text)
    
    con.Open()
    cmd.ExecuteNonQuery()
    
    con.Close()
    
    This assumes that the date column in your studnt database table is a string and not a DateTime. If that's not the case, then that's probably where your problem is.


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, November 22, 2015 5:08 PM
  • Bonnie, I believe the parameter markers in the INSERT statement need to be "?" instead of the parameter names with OleDbCommand.

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Sunday, November 22, 2015 5:45 PM
  • Dan, I thought that parameter names work OK too ... but I forgot one caveat. You *do* have to put the ? in the query, but you can still use parameter names when you add the parameters (mainly for clarity so you can see that you're using the right parameters). So, you're right ... and this is what you can do:

    Dim cmd As New OleDbCommand("insert into studnt (namtlb,date,addres) values (?, ?, ?)", con)
    cmd.Parameters.AddWithValue("@name", TextBox1.Text)
    cmd.Parameters.AddWithValue("@date", DateTimePicker1.Value.ToString("yyy/MM/dd"))
    cmd.Parameters.AddWithValue("@address", TextBox2.Text)
    
    con.Open()
    cmd.ExecuteNonQuery()
    
    con.Close()


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, November 22, 2015 6:14 PM
  • As Bonnie mentioned, you should be using Command Parameters. Also, if you are working with a Microsoft Access database, "date" is reserved word and should not be used. I would recommend renaming this column.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, November 23, 2015 2:11 PM