我试图将我在DataGridView中的所有数据保存到Access数据库,但我收到此错误:INSERT INTO语句中的语法错误。 [英] I am trying to save all my data in a DataGridView into an Access database but I'm getting this error: Syntax error in INSERT INTO statement.
问题描述
我正在尝试将DataGridView中的所有数据保存到Access数据库中,但是我收到此错误:
INSERT INTO语句中的语法错误。
Dim tyresize As 字符串
Dim 率,模式 As String
对于 i 作为 整数 = 0 我 .dgvorder.Rows.Count - 2
' qutno = Me.TextBox1.Text
tyresize = 我 .dgvorder.Rows(i )。细胞( 3 )。Value.ToString
rate = Me .dgvorder.Rows( i)。细胞( 4 )。Value.ToString
pattern = Me .dgvorder.Rows (i).Cells( 5 )。Value.ToString
conecDB()
initCMD()
SQL = 插入order_item(Sales_Order_No,& _
number,& _
pattern,& _
repair,& _
quotaction_no)values('& 我 .txtorderno.Text& ','& tyresize& ; ','& rate& ','& pattern& ','& 我 .txtqutno.Text& ')
execComDB(SQL)
closeDB()
下一步
MsgBox( 成功保存...... )
使用构建SQL INSERT语句的方式你得到语法错误也就不足为奇了。
为VB.NET参数化查询进行Goggle并找出如何正确执行此操作。这也将有助于解决您自己打开的另一个严重问题:通过SQL注入攻击彻底破坏数据库。
SQL = 插入order_item(Sales_Order_No,& _
number,& _
模式,& _
修复,& _
quotaction_no)values('& 我 .txtorderno.Text& ','& ; tyresize& ','& rate& ','&模式与 ','& 我 .txtqutno.Text& ')
以上sql语句意味着您要添加字符串数据。我怀疑Sales_Order_No
和数字
字段是数字字段。删除这些数据周围的单引号,然后重试。
正如Dave所说,你的sql代码是 sql注入 [ ^ ]易受攻击。因此,您必须更改代码以使用参数化查询。
SQL = 插入order_item(Sales_Order_No,[number],pattern,repair,quotaction_no)& _
值(?,?,?,?,?)
请勿使用number
作为字段名称!这是保留字 [ ^ ]。为了解决这个问题,我添加了[]
括号。
现在,你必须创建OleDbCommand [ ^ ] with < a href =https://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters%28v=vs.90%29.aspx>参数 [ ^ 。
Dim 命令正如 新 OleDbCommand(queryString,connection)
command.CommandText = SQL;
' 1。 Sales_Order_no
command.Parameters.Add( @ p1,OleDbType。 整数)。值= CInt (我 .txtorderno.Text)
' 2。数字
command.Parameters.Add( @ p2,OleDbType。 整数)。值= tyresize
' 3 。模式
command.Parameters.Add( @ p3,OleDbType。 字符)。值=费率
' 和等......
注意:添加参数的顺序非常重要!
请参阅: OleDbParameter构造函数(String,OleDbType) [ ^
I am trying to save all my data in a DataGridView into an Access database but I'm getting this error:
Syntax error in INSERT INTO statement.
Dim tyresize As String
Dim rate, pattern As String
For i As Integer = 0 To Me.dgvorder.Rows.Count - 2
'qutno = Me.TextBox1.Text
tyresize = Me.dgvorder.Rows(i).Cells(3).Value.ToString
rate = Me.dgvorder.Rows(i).Cells(4).Value.ToString
pattern = Me.dgvorder.Rows(i).Cells(5).Value.ToString
conecDB()
initCMD()
SQL = "Insert into order_item(Sales_Order_No," & _
"number," & _
"pattern," & _
"repair," & _
"quotaction_no)values('" & Me.txtorderno.Text & "','" & tyresize & "' , '" & rate & "' , '" & pattern & "', '" & Me.txtqutno.Text & "')"
execComDB(SQL)
closeDB()
Next
MsgBox("Successfull Save......")
With the way you've built your SQL INSERT statement, it's no surprise you're getting syntax errors.
Goggle for "VB.NET parameterized query" and find out how to do it correctly. This will also help with another serious problem you've opened yourself up to: the complete destruction of your database through SQL Injection attacks.
SQL = "Insert into order_item(Sales_Order_No," & _ "number," & _ "pattern," & _ "repair," & _ "quotaction_no)values('" & Me.txtorderno.Text & "','" & tyresize & "' , '" & rate & "' , '" & pattern & "', '" & Me.txtqutno.Text & "')"
Above sql statement means that you want to to add string data. I suspect thatSales_Order_No
andnumber
fields are numeric fields. Remove single quotes around these data and try again.
As Dave mentioned, your sql code is sql injection[^] vulnerable. So you have to change your code to use parametrized queries.
SQL = "Insert into order_item(Sales_Order_No, [number], pattern, repair, quotaction_no)" & _ "values(?, ?, ?, ?, ?)"
Do not usenumber
as a field name! It's reserved word[^]. To workaround this, i added[]
brackets.
Now, you have to create OleDbCommand[^] with parameters[^].
Dim command As New OleDbCommand(queryString, connection) command.CommandText = SQL; '1. Sales_Order_no command.Parameters.Add("@p1", OleDbType.Integer).Value = CInt(Me.txtorderno.Text) '2. Number command.Parameters.Add("@p2", OleDbType.Integer).Value = tyresize '3. pattern command.Parameters.Add("@p3", OleDbType.Char).Value = rate 'and so on...
Note: the order in which you add parameters is very important!
See: OleDbParameter Constructor (String, OleDbType)[^]
这篇关于我试图将我在DataGridView中的所有数据保存到Access数据库,但我收到此错误:INSERT INTO语句中的语法错误。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!