INSERT INTO语句中的VB.net语法错误(尽管该语句中没有语法错误) [英] VB.net Syntax error in INSERT INTO statement (Although, there's not syntax error in the statment)
问题描述
我已经在Microsoft Access中测试了此SQL语句,它可以正常运行,但是由于某种原因,它在我的代码中不起作用.我已经检查了很多次,但没有弄清楚语法错误(尽管它可以在MS Access中使用).
I have tested this SQL statement in Microsoft Access and it work perfectly but for some reason it doesn't work in my code. I have checked it many times and I haven't figure out the syntax error (Although, it work in MS Access).
这是错误消息:
"System.Data.OleDb.OleDbException"类型的未处理异常 发生在System.Data.dll
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
其他信息:INSERT INTO语句中的语法错误.
Additional information: Syntax error in INSERT INTO statement.
这是我的代码:
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
'Now, when the Register button is clicked,
'We check, if the user type is User, we create a normal User in the database.
'Otherwise, we create a Wroker.
Dim fname, lname, nationality, password As String
Dim age, yearExpr, phone, saloonID, type As Integer
fname = fNameBox.Text
lname = LNameBox.Text
age = Convert.ToInt32(ageBox.Text)
phone = Convert.ToInt32(phoneBox.Text)
password = passwordBox.Text
If (userTypeCB.Text.Equals("User")) Then
type = 1
salSql = ""
salCnc.Open()
salSql = "INSERT INTO User ([Fname], [Lname], [Age], [Phone#], [Type], [Password])
VALUES (" & "'" & fname & "'" & "," & "'" & lname & "'" & "," & age & "," & phone & "," & type & "," & "'" & password & "'" & ")"
salCommand = New OleDb.OleDbCommand(salSql, salCnc)
salCommand.ExecuteNonQuery()
salCnc.Close()
ElseIf (userTypeCB.Text.Equals("Worker")) Then
yearExpr = exprBox.Text
saloonID = saloonBox.Text
nationality = NationBox.Text
End If
End Sub
现在,编译器指向salCommand.ExecuteNonQuery()
作为错误源.知道有什么问题吗?
Now, the compiler points to the salCommand.ExecuteNonQuery()
as the error source. Any idea what's the problem?
推荐答案
信用@LarsTech
Credit to @LarsTech
用户是关键字.您必须将其放在方括号中.即:
INSERT INTO [User]...
另外,使用参数化命令来避免SQL注入,这也可能是导致此语法错误的原因.
Also, use a parameterized command to avoid SQL injection which could also be the cause of this syntax error.
'''code removed for brevity
salSql = "INSERT INTO [User] ([Fname],[Lname],[Age],[Phone#],[Type],[Password]) VALUES (?,?,?,?,?,?)"
salCommand = New OleDb.OleDbCommand(salSql, salCnc)
salCommand.Parameters.Add("@fname", fname)
salCommand.Parameters.Add("@lname", lname)
salCommand.Parameters.Add("@age", age)
salCommand.Parameters.Add("@phone", phone)
salCommand.Parameters.Add("@type", type)
salCommand.Parameters.Add("@password", password)
salCommand.ExecuteNonQuery()
'''code removed for brevity
这篇关于INSERT INTO语句中的VB.net语法错误(尽管该语句中没有语法错误)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!