Syntax error in INSERT INTO statement. VB.NET AND MS-ACCESS

0
votes

I think I have check thoroughly, I indicated the 7 field names as well as the the seven boxes where the data will be stored and send to the database... I'm not sure where specifically the syntax error, thank you!

Private Sub AddBtn_Click(sender As Object, e As EventArgs) Handles AddBtn.Click
        If UsernameTxt.Text = Nothing Or PasswordTxt.Text = Nothing Or FullNameTxt.Text = Nothing Or EmailTxt.Text = Nothing Or AddressTxt.Text = Nothing Or ContactNumberTxt.Text = Nothing Or UserTypeTxt.Text = Nothing Then
            MsgBox("Please Input Credentials", MsgBoxStyle.OkOnly)
        Else
            Dim sql As String
            Dim cmd As New OleDbCommand
            Dim i As Integer
            Try
                con.Open()
                sql = "INSERT INTO userTable (userName,passWord,fullName,userType,e_mail,home_address,contact_number) values ('" & UsernameTxt.Text & "', '" & PasswordTxt.Text & "', '" & FullNameTxt.Text & "', '" & UserTypeTxt.Text & "', '" & EmailTxt.Text & "', '" & AddressTxt.Text & "', '" & ContactNumberTxt.Text & "');"
                cmd.Connection = con
                cmd.CommandText = sql

                i = cmd.ExecuteNonQuery
                If i > 0 Then
                    MsgBox("Record Added SuccessFully")
                Else
                    MsgBox("Error Adding Record")
                End If
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                con.Close()
            End Try
        End If
    End Sub
2
vb.netms-access
hello pls help me :( - Kai
While the insert sql looks okish, you're using string concatenation to build it. Likelyhood is that one of the values you're trying to insert has some reserved character(s). An all around better option is to use parameters - Hursey
Please, use SQL Parameters instead - Morcilla de Arroz

2 Answers

2
votes

All the advice about parameters stands but, even if there are other issues, one that has not been addressed is that "Password" is a reserved word in Jet/ACE SQL. That means that you need to escape that column name no matter what else you do:

sql = "INSERT INTO userTable (userName,[passWord],fullName,..."

Passwords should not be stored in the clear anyway so, if you are doing the right thing and hashing passwords, you can name your column "PasswordHash" and there's no issue.

0
votes

Congratulations, you've discovered one of the reasons why it's important to use parameters in your SQL command statements -- they allow you to write a statement and fill in arbitrary data values without worrying about how the SQL syntax will be affected:

For Each textbox As TextBox In {UsernameTxt, PasswordTxt, FullNameTxt, UserTypeTxt, EmailTxt, AddressTxt, ContactNumberTxt}
    Dim prm = cmd.Parameters.Add(textbox.Name, OleDbType.VarWChar)
    prm.Value = textbox.Text
Next

con.Open()
sql = "INSERT INTO userTable (userName, passWord, fullName, userType, e_mail, home_address, contact_number) values (?, ?, ?, ?, ?, ?, ?)"
cmd.Connection = con
cmd.CommandText = sql

For an introduction on how to use parameters in ADO.NET, see here.