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

Question

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

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
– Hursey, Commented Apr 19, 2020 at 4:16

jmcilhinney · Accepted Answer · 2020-04-19 06:40:34Z

2

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.

edited Apr 19, 2020 at 6:40

answered Apr 19, 2020 at 6:06

jmcilhinney

54.9k5 gold badges30 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user20416 · Accepted Answer · 2020-04-19 04:25:31Z

0

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.

answered Apr 19, 2020 at 4:25

user20416

15.5k9 gold badges76 silver badges149 bronze badges

1 Comment

Kai Over a year ago

Hello sir, I have tried inserting your code below the Try, and after that I tried adding the inputs and it still says syntax error, I'm sorry but what did I do wrong? Thank you very much.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related