Syntax error in INSERT INTO statement for Access 2010
17,440
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)
Author by
Aouie
Updated on November 25, 2022Comments
-
Aouie over 1 year
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
-
aaa over 10 yearscan you use .Text instead of .Text()? For example, YrSec.Text
-
Aouie over 10 yearsthanks sir it works :).how about auto capitalize the first letter in textbox.??
-
Gord Thompson over 10 years@user2926827 re: "how about auto capitalize the first letter in textbox.?" - That sounds like a completely separate question.