vb.net INSERT INTO 语句中的语法错误visual basic
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22151943/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
syntax error in INSERT INTO statement visual basic
提问by user3375678
i dont know if this has been answered or not but i'm having problem with a syntax error in insert into statement. heres my code, i'm using visual basic 2010, and ms access 2010 as my database
我不知道这是否已得到回答，但我在 insert into 语句中遇到语法错误问题。这是我的代码，我使用的是 Visual Basic 2010，并将 ms access 2010 作为我的数据库
Imports System.Data.OleDb
Imports Comprehensive.Form1
Public Class Form2
Dim cnn As New OleDb.OleDbConnection
Private Sub RefreshData()
If Not cnn.State = ConnectionState.Open Then
cnn.Open()
End If
Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM items ORDER BY ID", cnn)
Dim dt As New DataTable
da.Fill(dt)
Form1.DataGridView1.DataSource = dt
cnn.Close()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cmd As New OleDbCommand
Try
If Not cnn.State = ConnectionState.Open Then
cnn.Open()
End If
cmd.Connection = cnn
cmd.CommandText = "INSERT INTO Items (Item_Name, Item_Type, Date_Added)" + "'VALUES('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "');'"
cmd.ExecuteNonQuery()
RefreshData()
Catch ex As Exception
MessageBox.Show(ex.Message & " - " & ex.Source)
cnn.Close()
End Try
End Sub
Private Sub Form2_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'TODO: This line of code loads data into the 'ShitdataDataSet.Items' table. You can move, or remove it, as needed.
Me.ItemsTableAdapter.Fill(Me.ShitdataDataSet.Items)
cnn = New OleDb.OleDbConnection
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=shitdata.mdb;"
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Me.Close()
End Sub
End Class
结束班
回答by Steve
The INSERT line contains single quotes not needed before the VALUES keyword AND at the end of the sql statement, remove them
INSERT 行在 VALUES 关键字和 sql 语句末尾包含不需要的单引号，删除它们
(Item_Name, Item_Type, Date_Added) VALUES(....)
^^^ ^^^
But as usual, you don't write sql commands in this way.
You should always use a parameterized query (SQL Injections)
但是像往常一样，你不会这样写sql命令。
您应该始终使用参数化查询（SQL 注入）
cmd.Connection = cnn
cmd.CommandText = "INSERT INTO Items (Item_Name, Item_Type, Date_Added)" & _
"VALUES(?, ?, ?)"
cmd.Parameters.AddWithValue("@p1", TextBox1.Text)
cmd.Parameters.AddWithValue("@p2", TextBox2.Text)
cmd.Parameters.AddWithValue("@p3", TextBox3.Text)
cmd.ExecuteNonQuery()
And, as a last note, keep in mind that if you have a datetime in your table (as it should) for the Date_Added field then you need to convert the textbox3 value to a datetime.
最后，请记住，如果您的表中有 Date_Added 字段的日期时间（应该如此），那么您需要将 textbox3 值转换为日期时间。
If this is the case, then, I recommend to look at this questionwhere the problem of inserting datetime values in access databases has been resolved.
如果是这样的话，那么，我建议看看这个问题，其中在插入数据库的访问日期时间值的问题已经解决。
