CODEWITHSUNDEEP
vb.netrowoledb
MaylorTaylor
MaylorTaylor

Reputation: 5041

Delete row from database using OleDb

I have this function working half right. The part that is working right is where I can select a row on the DataGridView, call this function using a "Delete Row" button, and then it will delete the row from the DataGridView....However, it does not delete the row on the database.

Can anyone help me with deleting the row from the DB using OleDb?

Function DeleteTableRow()
    Dim TaxConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ConfigurationManager.AppSettings("Database")
    Dim dbConnection = New OleDbConnection(TaxConnStr)

    Try
        Dim dbCommand As OleDbCommand = New OleDbCommand
        Dim rdr2 As OleDbDataReader

        Dim selectedRow = DataGridView1.SelectedRows

        dbCommand.CommandText = "DELETE FROM UserCriteria WHERE RowID =" & selectedRow
        If dbConnection.State = ConnectionState.Closed Then
            dbConnection.Open()
        End If

        dbCommand.Connection = dbConnection
        rdr2 = dbCommand.ExecuteReader
        dbCommand.ExecuteNonQuery()


        rdr2.Close()

        '''Must select entire row to delete
        'DataGridView1.Rows.Remove(DataGridView1.Rows(DataGridView1.SelectedCells.Item(0).RowIndex))

        '''allows you to select on cell in the row to delete entire row
        For Each oneCell As DataGridViewCell In DataGridView1.SelectedCells
            If oneCell.Selected Then
                DataGridView1.Rows.RemoveAt(oneCell.RowIndex)
            End If
        Next



    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        dbConnection.Close()
    End Try
End Function

Upvotes: 1

Views: 13197

Answers (3)

Steve
Steve

Reputation: 216253

DataGridView.SelectedRows is a collection of DataGridViewRow, you can't use a collection as a parameter to delete a particular and specific record on the database table. (Do you have OPTION STRICT set tot OFF?)

You need to loop over the collection, get the correct ID value from every single row and use that value as parameter to your delete query.

If dbConnection.State = ConnectionState.Closed Then
    dbConnection.Open()
End If

' Creating the command and its parameter here before entering the loop to avoid a continue'
' create and destroy pattern for the OleDbCommand'
Dim dbCommand As OleDbCommand = New OleDbCommand
dbCommand.CommandText = "DELETE FROM UserCriteria WHERE ID =?"
dbCommand.Connection = dbConnection
dbCommand.Parameters.AddWithValue("@row", 0) 
Dim rows = DataGridView1.SelectedRows
For Each row in rows
    dbCommand.Parameters("@row").Value = row.Cells("ID").Value)
    dbCommand.Connection = dbConnection
    dbCommand.ExecuteNonQuery()
Next

Pay also attention to not use string concatenation to build sql commands. This habit leads to a Whole can of worm called Sql Injection

Of course, an OleDbDataReader is not needed here. (Nothing to read about)

Upvotes: 2

nkvu
nkvu

Reputation: 5841

The issue is that your DataGridView1.SelectedRows will return a SelectedRowCollection (sorry, I've made the assumption this is a WinForms app). And that's not going to get you the correct result when passed to your CommandText because you'll probably get the ToString() of the SelectedRowCollection rather than the ID that you're after

What you actually want to do is loop over the collection (if the user is able to select more than one row) and delete each row that was selected, something like:

For Each selectedRow in DataGridView1.SelectedRows
    '1. Get the DatabaseId of the selected row
    '2. Modify dbCommand.CommandText to use the selected row from 1
    '3. execute command like you are doing with ExecuteNonQuery
Next

Each selectedRow in the above will be of this type...which has a Cells property you can access to get the ID you need (I am not sure which cell it will be in but you should be able to tell from your code).

Upvotes: 0

Robert Beaubien
Robert Beaubien

Reputation: 3156

You do not need a reader to delete a row. no data will be returned

   rdr2 = dbCommand.ExecuteReader
    dbCommand.ExecuteNonQuery()


    rdr2.Close()

should simply be

    dbCommand.ExecuteNonQuery()

Upvotes: 1

Related Questions