CODEWITHSUNDEEP
mysqldatabasevb.netwinformssql-delete
Mohamed Amine
Mohamed Amine

Reputation: 582

Delete command using ID from DataGridView

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()

Upvotes: 0

Views: 141

Answers (2)

jmcilhinney
jmcilhinney

Reputation: 54427

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

Upvotes: 1

nbk
nbk

Reputation: 49375

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

Upvotes: 0

Related Questions