mysql - VB.Net 和mysql问题

  显示原文与译文双语对照的内容

有一个需要紧急解决的问题,当点击保存时,我得到消息:"你的SQL语法中有错误,请检查与mySQL服务器版本相对应的手册,以便附近使用。'iddrug=21在第 21行


Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
 Dim i As Integer
 Dim xid As Integer
 Dim xQTY(0) As Integer
 Dim xQTY_ID(0) As Integer
 Dim xCount As Integer
 Dim xCounter_ID(0) As Integer
 'Dim sqlstrx(5) As String
 xid = 0
 If lstitems.Items.Count> 0 Then
 If Split(Me.Text," -")(1) ="Add" Then
 sqlSTR ="INSERT INTO orders (CustID, Cust_Name, order_date)" & _
"VALUES (" & txtcustid.Text &"," _
 &"'" & txtcustname.Text &"'," _
 &"'" & Format(dttoday.Value,"yyyy-MM-dd") &"')"
 ExecuteSQLQuery(sqlSTR)
 sqlSTR ="SELECT * FROM orders ORDER BY order_no DESC"
 ExecuteSQLQuery(sqlSTR)
 xid = sqlDT.Rows(0)("order_no")
 For i = 0 To lstitems.Items.Count - 1
 sqlSTR ="INSERT INTO orders_detail (order_no, idDrug, DrugName, Unit_Cost, qty, totalcost)" & _
"VALUES (" & xid &"," _
 & lstitems.Items(i).Text &"," _
 &"'" & lstitems.Items(i).SubItems(1).Text &"'," _
 &"'" & lstitems.Items(i).SubItems(2).Text &"'," _
 & lstitems.Items(i).SubItems(3).Text &"," _
 & lstitems.Items(i).SubItems(4).Text &")"
 ExecuteSQLQuery(sqlSTR)
 'UPDATE STOCKS
 sqlSTR ="UPDATE stockbalances SET ItemQuantity = ItemQuantity -" & CDbl(lstitems.Items(i).SubItems(3).Text) & _
"WHERE idDrug =" & lstitems.Items(i).Text
 ExecuteSQLQuery(sqlSTR)
 Next
 Else
 'delete first
 For i = 0 To UBound(deleteID)
 ExecuteSQLQuery("DELETE FROM Orders_detail WHERE order_no =" & txtorderno.Text &" AND idDrug =" & deleteID(i))
 sqlSTR ="UPDATE stockbalances SET ItemQuantity = ItemQuantity +" & Delete_QTY(i) & _
" WHERE idDrug =" & deleteID(i)
 ExecuteSQLQuery(sqlSTR)
 Next
 For i = 0 To UBound(deleteID)
 ReDim deleteID(i)
 deleteID(i) = 0
 Next
 del = 0
 '--
 'If lstitems.Items.Count> 0 Then
 For i = 0 To lstitems.Items.Count - 1
 'MsgBox(stockID &"" & lstitems.Items(i).Text)
 sqlSTR ="SELECT * FROM orders_detail WHERE order_no =" & stockID &" AND idDrug =" & lstitems.Items(i).Text
 ExecuteSQLQuery(sqlSTR)
 If sqlDT.Rows.Count> 0 Then
 ReDim Preserve xQTY(i), xQTY_ID(i)
 xQTY(i) = sqlDT.Rows(0)("QTY")
 'xQTY_ID(i) = sqlDT.Rows(0)("Item_ID")
 End If
 'MsgBox(sqlDT.Rows(0)("QTY"))
 Next
 For i = 0 To lstitems.Items.Count - 1
 'MsgBox(xQTY_ID(i))
 If lstitems.Items(i).Index <= (UBound(xQTY)) Then
 If CDbl(lstitems.Items(i).SubItems(4).Text) <xQTY(i) Then
 'MsgBox(xQTY(i) - CDbl(lstitems.Items(i).SubItems(4).Text))
 If xQTY(i)> 0 Then
 sqlSTR ="UPDATE orders_detail SET qty =" & lstitems.Items(i).SubItems(4).Text &"," _
 &"totalcost =" & lstitems.Items(i).SubItems(3).Text * lstitems.Items(i).SubItems(4).Text & _
" WHERE Order_no =" & stockID &" AND idDrug=" & lstitems.Items(i).Text
 ExecuteSQLQuery(sqlSTR)
 'UPDATE STOCKS
 sqlSTR ="UPDATE stockBalances SET ItemQuantity = ItemQuantity +" & (xQTY(i) - CDbl(lstitems.Items(i).SubItems(4).Text)) & _
" WHERE idDrug =" & lstitems.Items(i).Text
 ExecuteSQLQuery(sqlSTR)
 End If
 ElseIf CDbl(lstitems.Items(i).SubItems(4).Text)> xQTY(i) Then
 If xQTY(i)> 0 Then
 sqlSTR ="UPDATE orders_detail SET qty =" & lstitems.Items(i).SubItems(4).Text &"," _
 &"totalcost =" & lstitems.Items(i).SubItems(3).Text * lstitems.Items(i).SubItems(4).Text & _
" WHERE order_no =" & stockID &" AND idDrug=" & lstitems.Items(i).Text
 ExecuteSQLQuery(sqlSTR)
 'UPDATE STOCKS
 sqlSTR ="UPDATE Stockbalances SET ItemQuantity = ItemQuantity -" & (CDbl(lstitems.Items(i).SubItems(4).Text) - xQTY(i)) & _
" WHERE idDrug =" & lstitems.Items(i).Text
 ExecuteSQLQuery(sqlSTR)
 End If
 End If
 End If
 Next
 ' End If

 'search for new item
 sqlSTR ="SELECT * FROM orders_detail WHERE order_no =" & stockID &" ORDER BY Order_Dtl ASC"
 ExecuteSQLQuery(sqlSTR)
 xCount = sqlDT.Rows.Count
 For i = 0 To sqlDT.Rows.Count - 1
 ReDim Preserve xCounter_ID(i)
 xCounter_ID(i) = sqlDT.Rows(i)("idDrug")
 ' xCount = i + 1
 Next
 'check
 If lstitems.Items.Count> xCount Then
 For i = 0 To lstitems.Items.Count - 1
 If i> UBound(xCounter_ID) Then
 'MsgBox(lstitems.Items(i).Text)
 sqlSTR ="INSERT INTO orders_detail (order_no, idDrug, DrugName, price, qty, totalcost)" & _
"VALUES (" & txtorderno.Text &"," _
 & lstitems.Items(i).Text &"," _
 &"'" & lstitems.Items(i).SubItems(0).Text &"'," _
 &"'" & lstitems.Items(i).SubItems(1).Text &"'," _
 & lstitems.Items(i).SubItems(2).Text &"," _
 & lstitems.Items(i).SubItems(3).Text &"," _
 & lstitems.Items(i).SubItems(4).Text &")"
 ExecuteSQLQuery(sqlSTR)
 'UPDATE STOCKS
 sqlSTR ="UPDATE stockbalances SET ItemQuantity = ItemQuantity -" & CDbl(lstitems.Items(i).SubItems(4).Text) & _
"WHERE idDrug =" & lstitems.Items(i).Text
 ExecuteSQLQuery(sqlSTR)
 End If
 Next
 End If
 End If
 Else
 MsgBox("Can't save without details!!", MsgBoxStyle.Exclamation, xTitlename)
 Exit Sub
 End If

 MsgBox("Record has been saved!!", MsgBoxStyle.Information, xTitlename)
 sqlSTR ="SELECT distinct orders.order_no AS 'Order No.', Cust_Name as 'Customer Name', order_date AS 'Date', sum(totalcost) AS 'TOTAL DUE' FROM orders_detail" & _
"INNER JOIN orders ON orders_detail.order_no = orders.order_no" & _
"WHERE order_date ='" & Format(dttoday.Value,"yyyy-MM-dd") &"' GROUP BY orders.order_no, Cust_Name, order_date"
 FillListView(ExecuteSQLQuery(sqlSTR), FrmORDERLIST.lstorder, 0)
 Me.Close()
End Sub

时间: 作者:

修复所有代码并准确地解释错误的确切原因是不可能的。
可以说,你永远不应该使用这种字符串连接方法来执行数据库应用程序。 任何文本字段都可以能导致错误,因为它包含单引号或者任何日期项都会导致错误。 ( 对于小数和其他浮点值相同) 。
为了解决这个问题,( 并避免危险的SQL注入 scenarion ) 存在参数化查询方法。

所以,只有一个例子,你需要对每一行的sql命令做什么


 sqlSTR ="INSERT INTO orders_detail (order_no, idDrug, DrugName," & _ 
"Unit_Cost, qty, totalcost) VALUES (" & _
"@id, @iddrug, @dname,@unit, @qty, @total)"
 Using cmd = new MySqlCommand(sqlStr, connection) 
 cmd.Parameters.AddWithValue("@id", xid)
 cmd.Parameters.AddWithValue("@idrug", Convert.ToInt32(lstitems.Items(i).Text))
 cmd.Parameters.AddWithValue("@dname", lstitems.Items(i).SubItems(1).Text)
 cmd.Parameters.AddWithValue("@unit", lstitems.Items(i).SubItems(2).Text)
 cmd.Parameters.AddWithValue("@qty", Convert.ToDecimal(lstitems.Items(i).SubItems(3).Text ))
 cmd.Parameters.AddWithValue("@total", Convert.ToDecimal(lstitems.Items(i).SubItems(4).Text))
 cmd.ExecuteNonQuery()
 End Using

注意,在参数化查询中,可以使用 Convert.ToXXXXX 方法指定参数传递的值的数据类型。 这样框架代码可以为要传递给数据库引擎的值准备适当的格式设置。
更别提现在可以读的查询文本。

作者:
...