“syntax error in INSERT INTO statement”

Question

I'm a beginner in vb.net I've got the following Error upon executing the Insert-Statement

syntax error in INSERT INTO statement

Here is my code:

Private Sub cmdadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdadd.Click
        Try
            Dim sql As String = " INSERT INTO [Login1] ([ID],[Username],[Password])" & _
" VALUES(?,?,?)"

            Dim cmd As New OleDbCommand
            With cmd
                .Connection = con
                .CommandText = sql
                .Parameters.AddWithValue("?", txtid)
                .Parameters.AddWithValue("?", txtuser)
                .Parameters.AddWithValue("?", txtpass)
                .ExecuteNonQuery()
            End With
            MsgBox("The Data Has Been Added")

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

Could anyone help me?

Atilla Ozgur · Accepted Answer · 2014-12-09 08:35:20Z

1

Try to use sql parameter names

Private Sub cmdadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdadd.Click
    Try
        Dim sql As String = " INSERT INTO [Login1] ([ID],[Username],[Password])" & _
" VALUES(@ID,@Username,@Password)"

        Dim cmd As New OleDbCommand
        With cmd
            .Connection = con
            .CommandText = sql
            .Parameters.AddWithValue("@ID", txtid)
            .Parameters.AddWithValue("@Username", txtuser)
            .Parameters.AddWithValue("@Password", txtpass)
            .Open()
            .ExecuteNonQuery()
            .Close()
        End With
        MsgBox("The Data Has Been Added")

    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
End Sub

Note that: You should properly close your connections after you use them. Therefore following code may be better.

Private Sub InsertLogin()
    Dim sql As String = " INSERT INTO [Login1] ([ID],[Username],[Password])" & _
        " VALUES(@ID,@Username,@Password)"

    Using con As New OleDbConnection(ConnectionStringHERE)
      Using cmd As New OleDbCommand
            Dim cmd As New OleDbCommand
            cmd.Connection = con
            cmd.CommandText = sql
            cmd.Parameters.AddWithValue("@ID", txtid)
            cmd.Parameters.AddWithValue("@Username", txtuser)
            cmd.Parameters.AddWithValue("@Password", txtpass)
            con.Open()
            cmd.ExecuteNonQuery()
        con.Close()
      End Using
    End Using

End Sub

    Private Sub cmdadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdadd.Click
        Try
            InsertLogin()
            MsgBox("The Data Has Been Added")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

Here, I used cmd. syntax, it is no different then using With.

edited Dec 9, 2014 at 8:35

answered Dec 9, 2014 at 8:17

Atilla Ozgur

14.8k3 gold badges55 silver badges75 bronze badges

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

3 Comments

User999999 Over a year ago

I'm unfamiliar with using ExecuteNonQuerywithin a Withstatement. Does it open the open the connection aswell? Won't there be an exception thrown: The current state of the connection is closed... ?

Atilla Ozgur Over a year ago

With is synthetic sugar. It is no different than cmd.Connection cmd.CommandText

User999999 Over a year ago

So there will still be an exception thrown. As an OleDbCommand needs an open connection before executing ExecuteNonQuery

idstam · Accepted Answer · 2014-12-09 08:13:54Z

0

You have to name the sql parameters.

Look at the examples in the documentation: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue%28v=vs.110%29.aspx

answered Dec 9, 2014 at 8:13

idstam

2,8781 gold badge22 silver badges30 bronze badges

Collectives™ on Stack Overflow

“syntax error in INSERT INTO statement”

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related