Vb.net and MySQL issue

Question

When I click on save, I get the message:

You have an error in your SQL syntax;Check the manual that corresponds to your mySQL Server version for the right Syntax to use near 'idDrug=21' at line 21

Here is my code:

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

Do you have any subitem containing a single quote in your texts to insert? — Steve, Jan 16 '14 at 08:12
uum,what do you mean by the single quote?(still a beginner in vb) — J.Nandi, Jan 16 '14 at 08:23
`INSERT INTO TABLE (CUSTNAME) VALUE(' & txtCustName.Text & "')` where txtCustName.Text contains "O'Hara". The resulting sql will be invalid — Steve, Jan 16 '14 at 08:28

score 1 · Accepted Answer · edited May 23 '17 at 12:07

It is impossible to fix all of your code and to be precise what is the exact cause of your error.
Suffice to say that you never should do a database application using that kind of string concatenation approach. Any of your text fields could cause the error because it contains single quotes or any of your date items could cause the error because it is not formatted how the database requires. (Same for decimals and other floating point values).
To solve this problems (and to avoid the dangerous Sql Injection scenarion) exists the parameterized queries approach.

So, just an example on what you need to do for every line of your sql commands

 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

Notice how, in a parameterized query, you could specify the datatype of the value passed for the parameter using the Convert.ToXXXXX method. In this way the framework code could prepare the appropriate formatting of your values to be passed to the database engine.
Not to mention the now readable query text.

Darn,i wish i knew that earlier,i cant change the whole project because it is due Monday next week — J.Nandi, Jan 16 '14 at 08:29
Can't help with that, but deploying an insecure app (re-read the Sql Injection link) could become a real nigthmare soon — Steve, Jan 16 '14 at 08:32

Vb.net and MySQL issue

1 Answers1