0

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?

Improve this question
2
  • 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. Commented Dec 28, 2013 at 0:24
  • 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. Commented Dec 28, 2013 at 0:25

5 Answers 5

Reset to default
7

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
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

i dont understand.. what should i do with sqlQry and where does it fit in in the code above?
Check my answer I update it with new query
but myconnection is now undeclared.. leaving the original bit of code that declared it still gives me the same error
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
Can you run this query straight to your database(with Access)? Then you can get maybe more information about error
|
1

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()
Improve this answer

7 Comments

I think error is not because of missing quotations...
@Fabio Sure it is. I just didn't write it out because the better instruction is to use parameters.
Without parameters he will need: add quotations and &-sign to concat SQL query and values of variables
@Fabio That's what I was implying. I updated the post.
i tried this and it gave me the same error..
|
-1 
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()
Improve this answer

2 Comments

More prone to SQL injections and somehow error when a string text contains single quote.
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.
-1

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

Improve this answer

Comments

-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.

Improve this answer

2 Comments

The irony that your answer is missing some quotes.
And vulnerable for SQL Injection...

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.