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...

 

[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

 

[jebenson]

Just for future reference, here is why your previous code was not working:

If dsMasterRecipe.Tables(0).Rows.Count > 0 Then
    dsMasterRecipe.Tables(0).Rows(0).Delete()
    dsMasterRecipe.AcceptChanges() [red]<--This Line is the problem[/red]
    adpMasterRecipe.Update(dsMasterRecipe)
End If

Calling AcceptChenges does exactly what it says, it accepts all the changes made to all tables in the DataSet. When you add, update or delete rows in a datatable, the rows DataRowState property is set to Added, Updated or Deleted. When Update is called, it "looks" for rows with a datarowstate of Added, Updated or Deleted and takes appropriate action. The AcceptChanges method accepts all changes, and resets the datarowstate of ALL rows to Unchanged. Thus, when the Update method is called, it sees nothing to do and so nothing gets changed in the database.

The Update method calls AcceptChanges behind the scenes, so you don't actually nbeed to call it at all.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!