Syntax error in INSERT INTO statement for Access 2010

Question

My INSERT statement apparently has a syntax error. Could someone please explain why that might be?

Private Sub Register_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Register.Click
    Dim StudentNum As String
    Dim Password As String
    Dim FirstName As String
    Dim LastName As String
    Dim YrandSec As String

    StudentNum = Number.Text()
    Password = Pass.Text
    FirstName = First.Text
    LastName = Last.Text
    YrandSec = YrSec.Text()

    SQL = "INSERT INTO Accounts(StudNo,Password,FirstName,LastName,YrandSec) VALUES ('" & StudentNum & "', '" & Password & "', '" & FirstName & "', '" & LastName & "', '" & YrandSec & "')"    - ERROR HERE
    Cmd = New OleDbCommand(SQL, Con)
    Con.Open()
    objCmd = New OleDbCommand(SQL, Con)

    If Repass.Text = Pass.Text = False Then
        Re.Text = "*Password didn't match!"
        Number.Text = ""
        Pass.Text = ""
        Repass.Text = ""
        Con.Close()
    Else
        If Number.Text = "" Or Pass.Text = "" Or Repass.Text = "" Or First.Text = "" Or Last.Text = "" Or YrSec.Text = "" Then
            MsgBox("Please complete the field", MsgBoxStyle.Information, "Failed to create")
        Else
            objCmd.ExecuteNonQuery()
            Re.Text = ""
            MsgBox("Account has been created", MsgBoxStyle.Information, "Congrats!")
            For fade = 0.0 To 1.1 Step 0.2
                Login.Opacity = fade
                Login.Show()
                Me.Hide()
                Threading.Thread.Sleep(30)
                Number.Text = ""
                Pass.Text = ""
                Repass.Text = ""
                First.Text = ""
                Last.Text = ""
                YrSec.Text = ""
            Next
        End If

    End If
End Sub

Gord Thompson · Accepted Answer · 2013-10-28 07:29:11Z

9

PASSWORD is a reserved word in Access SQL, so you need to wrap that column name in square brackets.
You really should use a parameterized query to protect against SQL Injection and generally make your life easier.

Try something like this

SQL = "INSERT INTO [Accounts] ([StudNo],[Password],[FirstName],[LastName],[YrandSec]) " & _
        "VALUES (?, ?, ?, ?, ?)"
Con.Open()
objCmd = New OleDbCommand(SQL, Con)
objCmd.Parameters.AddWithValue("?", StudentNum)
objCmd.Parameters.AddWithValue("?", Password)
objCmd.Parameters.AddWithValue("?", FirstName)
objCmd.Parameters.AddWithValue("?", LastName)
objCmd.Parameters.AddWithValue("?", YrandSec)

answered Oct 28, 2013 at 7:29

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

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

2 Comments

Aouie Over a year ago

thanks sir it works :).how about auto capitalize the first letter in textbox.??

Gord Thompson Over a year ago

@user2926827 re: "how about auto capitalize the first letter in textbox.?" - That sounds like a completely separate question.

aaa · Accepted Answer · 2013-10-28 05:26:15Z

0

remove those double quotes inside your sql statement.

answered Oct 28, 2013 at 5:26

aaa

4995 silver badges13 bronze badges

1 Comment

aaa Over a year ago

can you use .Text instead of .Text()? For example, YrSec.Text

Collectives™ on Stack Overflow

Syntax error in INSERT INTO statement for Access 2010

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related