Inserting data from VB.NET to MS Access: Syntax er

Question

This question already has an answer here:

Syntax error in INSERT INTO Statement when writing to Access (1 answer)

Closed 4 years ago.

I'm using Microsoft Visual Studio 2013 and im trying to make a registration form for my account database using VB.NET. This is my code so far:

Private Sub btnRegistery_Click(sender As Object, e As EventArgs) Handles btnRegistery.Click
    Dim usernme, passwrd As String
    usernme = txtUsernm.Text
    passwrd = txtpasswrd.Text

    Dim myconnection As OleDbConnection
    Dim constring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\hasan\Documents\Visual Studio 2012\Projects\hasan\Login_Info.accdb"
    myconnection = New OleDbConnection(constring)
    myconnection.Open()

    Dim sqlQry As String

    sqlQry = "INSERT INTO tbl_user(username, password) VALUES(usernme , passwrd)"

    Dim cmd As New OleDbCommand(sqlQry, myconnection)
    cmd.ExecuteNonQuery()
End Sub

The code compiles fine, but when i try to register any new information i get the following message:

    A first chance exception of type 'System.Data.OleDb.OleDbException' 
    occurred in System.Data.dll
    Additional information: Syntax error in INSERT INTO statement.    
    If there is a handler for this exception, the program may be safely continued.

What could be a solution and cause for this problem?

Answer 1

Your query seems wrong: ... VALUES(usernme, passwrd)... -- Here the usernmeand passwrd are not variables for database, but just plain text in the query.

Use parameters, like this:

Dim usernme, passwrd As String
usernme = txtUsernm.Text
passwrd = txtpasswrd.Text
Dim constring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\hasan\Documents\Visual Studio 2012\Projects\hasan\Login_Info.accdb"
Using myconnection As New OleDbConnection(constring)
    myconnection.Open()
    Dim sqlQry As String = "INSERT INTO [tbl_user] ([username], [password]) VALUES (@usernme, @passwrd)"
    Using cmd As New OleDbCommand(sqlQry, myconnection)
        cmd.Parameters.AddWithValue("@usernme", usernme)
        cmd.Parameters.AddWithValue("@passwrd", passwrd)
        cmd.ExecuteNonQuery()
    End using
End using

Answer 2

Dim cnn As New OleDb.OleDbConnection

Private Sub RefreshData()
    If Not cnn.State = ConnectionState.Open Then
        '-------------open connection-----------
        cnn.Open()
    End If

    Dim da As New OleDb.OleDbDataAdapter("select stdID as [StdIdTxt]," &
                                       "Fname as [FnameTxt] ,Lname,BDy,age,gender,address,email,LNO,MNO,course" &
                                       "from studentTB order by stdID", cnn)

    Dim dt As New DataTable
    '------------fill data to data table------------
    da.Fill(dt)



    'close connection
    cnn.Close()


End Sub



Private Sub AddNewBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddNewBtn.Click
    Dim cmd As New OleDb.OleDbCommand

    '--------------open connection if not yet open---------------
    If Not cnn.State = ConnectionState.Open Then
        cnn.Open()
    End If
    cmd.Connection = cnn

    '----------------add data to student table------------------
    cmd.CommandText = "insert into studentTB (stdID,Fname,Lname,BDy,age,gender,address,email,LNO,MNO,course)" &
        "values (" & Me.StdIdTxt.Text & "','" & Me.FnameTxt.Text & "','" & Me.LNameTxt.Text & "','" &
        Me.BdyTxt.Text & "','" & Me.AgeTxt.Text & "','" & Me.GenderTxt.Text & "','" &
        Me.AddTxt.Text & "','" & Me.EmailTxt.Text & "','" & Me.Hometxt.Text & "','" & Me.mobileTxt.Text & "','" & Me.Coursetxt.Text & "')"


    cmd.ExecuteNonQuery()

    '---------refresh data in list----------------
    'RefreshData()

    '-------------close connection---------------------
    cnn.Close()

Answer 3

The SQL string should look like this

sqlQry = "INSERT INTO tbl_user(username, password) VALUES(" & usernme  & "', " & passwrd & ")"

The values usernme & passwrd aren't valid to the database. Beyond that you really should look into using a Command object and parameters.

Answer 4

You aren't including the actual variable information missing the quotations, like

VALUES ('" & usernme & '", ...etc

You should be using parameters to avoid errors and sql injection:

sqlQry = "INSERT INTO tbl_user (username, password) VALUES(@usernme, @passwrd)"

Dim cmd As New OleDbCommand(sqlQry, myconnection)
cmd.Parameters.AddWithValue("@usernme", usernme)
cmd.Parameters.AddWithValue("@passwrd", passwrd)
cmd.ExecuteNonQuery()

Answer 5

This insert error is nothing but a syntax error, there is no need for changing your code. please avoid reserved words like "password" form your database. This error is due to the field name password