CODEWITHSUNDEEP
sqlvb.net
CrashOverride
CrashOverride

Reputation: 338

Error in vb.net code in INSERT INTO

When I try to insert data in these three field gets an error saying error in INSERT INTO Statement. but when a save in only the first field sname it gets added but when adds other two gets this error I am getting an exception in INSERT INTO Statement check below any advice?

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Try
        Dim dbprovider As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Taher\Documents\Visual Studio 2010\Projects\WindowsApplication1\WindowsApplication1\Database1.accdb;Persist Security Info=False;"
        Me.con = New OleDb.OleDbConnection()
        con.ConnectionString = dbprovider
        con.Open()

        Dim sqlquery As String = "INSERT INTO admin (sname,username,password)" + "VALUES ('" & txtname.Text & "','" & txtuser.Text & "','" & txtpass.Text & "');"
        Dim sqlcommand As New OleDb.OleDbCommand(sqlquery)
        With sqlcommand
            .CommandText = sqlquery
            .Connection = con
            .ExecuteNonQuery()
            con.Close()
        End With
        MsgBox("User Registered")
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
End Sub

Upvotes: 0

Views: 644

Answers (1)

Steve
Steve

Reputation: 216243

The word PASSWORD is a reserved keyword in JET-SQL for Microsoft Access. If you have a column with that name you should encapsulate it with square brackets

"INSERT INTO admin (sname,username,[password])" &% _
"VALUES ('" & txtname.Text & "','" & txtuser.Text & _
"','" & txtpass.Text & "');"

That's the reason of the syntax error, however let me tell you that building sql commands concatenating strings is a very bad practice. You will have problems when your values contain single quotes and worst of all, your code could be used for sql injection Attacks

So your code should be changed in this way

Dim sqlquery As String = "INSERT INTO admin (sname,username,password)" & _
    "VALUES (?, ?, ?)"
Dim sqlcommand As New OleDb.OleDbCommand(sqlquery)
With sqlcommand
    .CommandText = sqlquery
    .Connection = con
    .Parameters.AddWithValue("@p1", txtname.Text)
    .Parameters.AddWithValue("@p2", txtuser.Text)
    .Parameters.AddWithValue("@p3", txtpass.Text)
    .ExecuteNonQuery()
    con.Close()
End With

also your use of the object OleDbConnection doesn't follow a good pattern. In case of exception you don't close the connection and this could be a problem in reusing the connection in subsequent calls. You should try to use the Using statement

Using connection = New OleDb.OleDbConnection()
    connection.ConnectionString = dbprovider
    connection.Open()
    .....
    ' rest of command code here '
    ' No need to close the connection 
End Using

in this way, also if you get an exception the OleDbConnection will be closed and disposed without impact on system resource usage.

Upvotes: 6

Related Questions