Inserting data from VB.NET to MS Access: Syntax error in INSERT INTO statement

Question

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?

Solution 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

Solution 2

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()

Comments

[Jamie Clayton]

I would also suggest that you move any database code out of the User Interface code behind and into a separate DLL to ensure better separation of concerns.

[Jamie Clayton]

I would also recommend that you don't hard code connection strings but store them in the app.config file so you can "adjust" the location of the MSAccess db without re-compiling.

[LarsTech]

The irony that your answer is missing some quotes.

[Fabio]

I think error is not because of missing quotations...

[Fabio]

And vulnerable for SQL Injection...

[LarsTech]

@Fabio Sure it is. I just didn't write it out because the better instruction is to use parameters.

[Fabio]

Without parameters he will need: add quotations and &-sign to concat SQL query and values of variables

[LarsTech]

@Fabio That's what I was implying. I updated the post.

[Admin]

i dont understand.. what should i do with sqlQry and where does it fit in in the code above?

[Admin]

i tried this and it gave me the same error..

[Fabio]

Check my answer I update it with new query

[LarsTech]

@user3140727 Make sure your table and field names are correct.

[Admin]

but myconnection is now undeclared.. leaving the original bit of code that declared it still gives me the same error

[Fabio]

Error happened in database, on my opinion query looks fine(new query).Then check that table, field names are right. Then check if length declared in database for fields username and password are more or same then inputed values

[Admin]

i did, nothing wrong there

[Fabio]

Can you run this query straight to your database(with Access)? Then you can get maybe more information about error

[Admin]

i dont understand what you mean.. how do i do that?

[Fabio]

Just open your database file(Login_Info.accdb) with Access, and run SQL Command...

[Admin]

im really sorry, but as ive said im pretty new to this.. can you explain further

[Admin]

im gonna go sleep now.. its 2 am where im at, ill take a look at it tomorrow

[Jade]

More prone to SQL injections and somehow error when a string text contains single quote.

[Gord Thompson]

Welcome to Stack Overflow. Thanks for submitting an answer, but there are a couple of problems with it: (1) It is a "code dump" answer with absolutely no explanation as to how it solves the OP's problem(s). Even a brief introductory sentence like "This code uses [some technique] to avoid the error." would have helped. (2) Your code concatenates user input directly into the SQL statement, leaving it vulnerable to SQL Injection. That is bad practice. You should have used a parameter query.