CODEWITHSUNDEEP
vb.net
Osman Ahmed Mohamed
Osman Ahmed Mohamed

Reputation: 1

“syntax error in INSERT INTO statement”

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?

Upvotes: 0

Views: 57

Answers (2)

Atilla Ozgur
Atilla Ozgur

Reputation: 14701

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.

Upvotes: 1

idstam
idstam

Reputation: 2878

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

Upvotes: 0

Related Questions