VB 2010 错误 (INSERT INTO) 语法错误 [英] VB 2010 error (INSERT INTO) syntax error

问题描述

我在 VB.NET 中有一个程序，可以将文本框中的数据输入到 Access 数据库中.这是示例图片:

I have a program in VB.NET that will input data from textboxes into an Access database. Here is sample image:

这是我正在使用的代码，它给了我一个错误:

This is the code I am using and it gives me an error:

m = TextBox1.Text
b = "'" + TextBox2.Text + "'"
x = "'" + TextBox3.Text + "'"
d = TextBox4.Text
n = "'" + TextBox5.Text + "'"
Dim s2 As String
s2 = "insert into users1 ( num , name1 , pass , add , phone ) " & " values ( " + m + " , " + n + " , " + b + " , " + x + " , " + d + " ) "
Dim cmd2 As New OleDbCommand(s2, con)
cmd2.ExecuteNonQuery()

推荐答案

SQL 失败的原因是add"是一个保留字.即你不能使用它而不把它放在方括号中 - [add].如上所述，您应该参数化您的查询，因此其中之一将起作用...

The reason your SQL is failing is that "add" is a reserved word. i.e. you cannot use it without putting it in square brackets - [add]. As above you should parameterise your query, so one of these will work...

Using oleCmd As New OleDbCommand("Insert Into users1 (num,name1,pass,[add],phone) values (@num,@name1,@pass,@add,@phone)", con)
    oleCmd.Parameters.Add("@num", OleDbType.Integer).Value = Textbox1.Text
    oleCmd.Parameters.Add("@name1", OleDbType.VarChar).Value = Textbox2.Text
    oleCmd.Parameters.Add("@pass", OleDbType.VarChar).Value = Textbox3.Text
    oleCmd.Parameters.Add("@address", OleDbType.VarChar).Value = Textbox4.Text
    oleCmd.Parameters.Add("@phone", OleDbType.Integer).Value = Textbox5.Text
    oleCmd.ExecuteNonQuery()
End Using

Using oleCmd As New OleDbCommand("Insert Into users1 (num,name1,pass,[add],phone) Values (?,?,?,?,?)", con)
    oleCmd.Parameters.AddWithValue("?", Textbox1.Text)
    oleCmd.Parameters.AddWithValue("?", Textbox2.Text)
    oleCmd.Parameters.AddWithValue("?", Textbox3.Text)
    oleCmd.Parameters.AddWithValue("?", Textbox4.Text)
    oleCmd.Parameters.AddWithValue("?", Textbox5.Text)
    oleCmd.ExecuteNonQuery()
End Using

注意，如果数据类型与您尝试插入的数据类型不匹配，它将失败，因此如果您尝试在 num 或 phone 中插入文本，它将失败.您需要验证您的输入并最好转换它们，而不是使用文本框 Text.

Note, it will fail if the data types do not match what you are trying to insert, so if you try to insert text in num or phone it will fail. You will need to validate your input and preferably convert them rather than use the textbox Text.

这篇关于VB 2010 错误 (INSERT INTO) 语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！