Visual Basic MySQL Datagrid Delete from database

Question

I am making a invoice system in Visual Basic 2010 and i am stuck here

Here is my code

     Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As  System.EventArgs) Handles btnDelete.Click

    For Each row As DataGridViewRow In datagrid.SelectedRows
        Dim selectedindex As String = datagrid.CurrentRow.Cells(0).Value.ToString()
        datagrid.Rows.Remove(row)
        If conn.State = ConnectionState.Closed Then
            conn.Open()
        End If
        Dim sql = "DELETE FROM sales WHERE InvoiceNo='" & txtInvoiceNo.Text & "' and id='" & selectedindex & "'"
        Dim cmd As New MySqlCommand(sql, conn)
        Dim reader As MySqlDataReader = cmd.ExecuteReader

        If reader.Read() Then

        End If
        conn.Close()
        reader.Close()
    Next

End Sub

This code is not working well please have a look to my invoice screen shot and suggest me a better code

When i try to delete the selected row the code does not work and if any one can tell me i can delete multiple selected rows? they are deleted from the datagrid but not from database

Sales Form

It's definitely vb.net, not vba. And that query code will be vulnerable to sql injection attacks. — Joel Coehoorn
– Joel Coehoorn, Commented Mar 9, 2012 at 14:13

Joel Coehoorn · Accepted Answer · 2012-03-09 14:23:15Z

Try this:

Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As  System.EventArgs) Handles btnDelete.Click

    Using cn As New MySqlConnection("Connection string here"), _
          cmd As New MySqlCommand("DELETE sales WHERE InvoiceNo= ?InvoiceNo and id= ?id", cn)

        cmd.Parameters.Add("?InvoiceNo", MySqlDbTypes.VarChar, 10).Value = txtInvoiceNo.Text
        cmd.Parameters.Add("?id", MySqlDbTypes.Int32)

        cn.Open()
        For Each row As DataGridViewRow In datagrid.SelectedRows
            cmd.Parameters(1).Value = row.Cells(0).Value
            cmd.ExecuteNonQuery()
            datagrid.Rows.Remove(row)
        Next row

    End Using

End Sub

It looks like you're trying to save and re-use a single connection object, and just open it as needed. This is misguided: it breaks .Net's ability to do connection pooling. You really do want to create a new connection object for each place where you'll talk to the database. This also let's you do a better job making sure a connection is closed properly after a bad query. Finally, the use of query parameters will guard your app against sql injection attacks.

Collectives™ on Stack Overflow

Visual Basic MySQL Datagrid Delete from database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related