Question Physically deleting row in OleDB

[tim8w]

I have the folloing code using OleDB to manipulate an Access database:

Dim adpMasterRecipe As OleDb.OleDbDataAdapter
Dim dsMasterRecipe As New System.Data.DataSet

adpMasterRecipe = New OleDb.OleDbDataAdapter("SELECT * FROM tblMasterRecipe WHERE MasterID = @MasterID", My.Settings.RecipesConnectionString)
adpMasterRecipe.SelectCommand.Parameters.AddWithValue("@MasterID", iRecipeNumber)
adpMasterRecipe.Fill(dsMasterRecipe)

If dsMasterRecipe.Tables(0).Rows.Count > 0 Then
    dsMasterRecipe.Tables(0).Rows(0).Delete()
    dsMasterRecipe.AcceptChanges()
    adpMasterRecipe.Update(dsMasterRecipe)
End If

The problem is that the row is not actually removed from the actual database...

 

[jmcilhinney]

The problem is that you're calling AcceptChanges. You are telling the DataSet that all the changes it contains have been accepted, which you don't do until after they have been saved. You don't need to call AcceptChanges at all because Update does so implicitly.

 

[tim8w]

jmcilhinney,
If I remove the AcceptChanges, then I get the following error:

Update requires a valid DeleteCommand when passed DataRow collection with deleted rows.

 

[jmcilhinney]

You've provided SQL code to SELECT records from the database but you haven't provided any to DELETE records, therefore no records can be deleted. You have to either set the DeleteCommand of the adapter yourself or else create an OleDbCommandBuilder to do it for you.

 

[tim8w]

Unhnd_Exception on another forum suggested I go directly to the DB instead of using Adapters... Here's the code I ended up using:

Dim Connection As New OleDbConnection("...")
Dim Command As New OleDbCommand 

Command.Connection = Connection
Command.CommandText = "Delete From tblMasterRecipe WHERE MasterID = @MasterID"
Command.Parameters.AddWithValue("@MasterID", 1) '1 = your iRecipeNumber

Try
     Connection.Open()
     Command.ExecuteNonQuery()
     Connection.Close()
Catch ex As Exception
     MsgBox(ex.Message)
Finally
     Connection.Dispose()
     Command.Dispose()
End Try

 

[jmcilhinney]

Looking at your original code more closely, I can see that that makes sense in this particular case. You have an ID and you know that you want to delete that record and only that record. Sending just that ID to the database to delete the corresponding record is a much more efficient option than retrieving the whole record, flagging it as deleted and then sending it back to the database.

There are many circumstances where something like what you were doing originally will be a better option though, so you should still get clear in your mind what you would need to do. Generally, if you were to retrieve multiple records all at once and then the user could edit any or all of them before saving the changes to the database in a batch, then it would be more sensible to use a DataAdapter.