Delete command using ID from DataGridView

Question

I have data shown in DataGridView. I made a Button when selecting IDs in the grid the code below runs but I keep getting an error.

Dim cnx As New MySqlConnection("datasource=localhost;database=bdgeststock;username=root;password=")
Dim cmd As MySqlCommand = cnx.CreateCommand
Dim resultat As Integer
If ConnectionState.Open Then
    cnx.Close()
End If
cnx.Open()
If grid.SelectedCells.Count = 0 Then
    MessageBox.Show("please select the ids that u want to delete")
Else
    cmd.CommandText = "delete from utilisateur where idu= @P1"
    cmd.Parameters.AddWithValue("@P1", grid.SelectedCells)
    resultat = cmd.ExecuteNonQuery
    If (resultat = 0) Then
        MessageBox.Show("error")
    Else
        MessageBox.Show("success")
    End If
End If
cnx.Close()
cmd.Dispose()

And which error exactly?

nbk
– nbk

2020-03-23 00:54:10 +00:00
Commented Mar 23, 2020 at 0:54 — nbk
– nbk, Commented Mar 23, 2020 at 0:54
MessageBox.Show("error")

Mohamed Amine
– Mohamed Amine

2020-03-23 01:02:45 +00:00
Commented Mar 23, 2020 at 1:02 — Mohamed Amine
– Mohamed Amine, Commented Mar 23, 2020 at 1:02

jmcilhinney · Accepted Answer · 2020-03-23 01:07:05Z

How does this make sense?

cmd.Parameters.AddWithValue("@P1", grid.SelectedCells)

As you tagged this question WinForms, you are presumably using a DataGridView rather than a DataGrid (names matter so use the right ones). In that case, the SelectedCells property is type DataGridViewSelectedCellCollection. How does it make sense to set your parameter value to that? How is that going to get compared to an ID in the database?

If you expect to use the values in those cells then you have to actually get those values out. You also need to decide whether you're going to use a single value or multiple. You are using = in your SQL query so that means only a single value is supported. If you want to use multiple values then you would need to use IN and provide a list, but that also means using multiple parameters. I wrote an example of this type of thing using a ListBox some time ago. You can find that here. You could adapt that code to your scenario like so:

Dim connection As New SqlConnection("connection string here")
Dim command As New SqlCommand
Dim query As New StringBuilder("DELETE FROM utilisateur")

Select Case grid.SelectedCells.Count
    Case 1
        query.Append(" WHERE idu = @idu")
        command.Parameters.AddWithValue("@idu", grid.SelectedCells(0).Value)
    Case Is > 1
        query.Append(" WHERE idu IN (")

        Dim paramName As String

        For index As Integer = 0 To grid.SelectedCells.Count - 1 Step 1
            paramName = "@idu" & index

            If index > 0 Then
                query.Append(", ")
            End If

            query.Append(paramName)
            command.Parameters.AddWithValue(paramName, grid.SelectedCells(index).Value)
        Next index

        query.Append(")")
End Select

command.CommandText = query.ToString()
command.Connection = connection

nbk · Accepted Answer · 2020-03-23 01:03:58Z

0

SelectedCells is a collection of cells

so it never can be only one id, so you have to guess which was you want or only allow one row to be selected

grid.SelectedCells(0).Value.ToString()

Or you have to program a loop to delete all selected rows

answered Mar 23, 2020 at 1:03

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Collectives™ on Stack Overflow

Delete command using ID from DataGridView

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related