How to delete a Record

[benschol]

In my Project a Recipe (table 1) have several Ingredients (Table 2) with the same ID of the Recipe. I need to delete a Ingredient record without using Primary Keys or other field as reference. Please, how I can use, for example, DataTables,DataRows,Position, etc to point the Record to delete? Or what is the better method?

Thanks,

 

[jmcilhinney]

You can delete the row in the DataTable using DataRow.Delete(). Note that this would be called on the instance that you want to delete, which you will have to find first. This will mark the row as deleted in your local DataTable, but you'll still have to assign an OleDbCommand (assuming you are using OleDb) with valid syntax for a delete statement to the DeleteCommand property of an OleDbDataAdapter. You then call Update on the adapter to commit your changes to the database.

 

[benschol]

Thanks. I use, in agreement of your suggestion, this code:

' I define Connection and Dataset in a Module
....code........
dta = New OleDbDataAdapter("Select * from  ingredients where ID= " & vId & " ", con)
Dim cb As New OleDb.OleDbCommandBuilder(dta)
	Dim dtr As DataRow
	dtr = Me.dst.Tables("Ingredients").Rows(Me.IposAct)
	dtr.Delete()
	Dim tableDel As DataTable
	tableDel = Me.dst.Tables("Ingredients").GetChanges(DataRowState.Deleted)
	Me.dta.Update(tableDel)
	Me.dst.Tables("Ingredients").AcceptChanges()
	Me.btnFirst.PerformClick()
.......code............

But I receive this error:

System.InvalidOperationException: Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information.

Do you can help me about the cause?

Thanks,

 

[jmcilhinney]

Command builders need to know the primary key of the table because they need to use it to generate the delete, insert and update commands. I assume that you are using the same data adapter to fill the table as you are to update it. You should find that setting the MissingSchemaAction property of the data adpater to AddWithKey will solve your problem.

 

[benschol]

Thanks very much!! Problem solved.

Regards

 

[DavidT_macktool]

Another way to delete

If (Me.BindingContext(DsJournal1, "Journal").Count > 0) Then
If MsgBox("Delete selected record?", vbYesNo, "Confirm") = vbYes Then
Me.BindingContext(DsJournal1, "Journal").RemoveAt(Me.BindingContext(DsJournal1, "Journal").Position)
End If
End If

 

[jmcilhinney]

If (Me.BindingContext(DsJournal1, "Journal").Count > 0) Then
If MsgBox("Delete selected record?", vbYesNo, "Confirm") = vbYes Then
Me.BindingContext(DsJournal1, "Journal").RemoveAt(Me.BindingContext(DsJournal1, "Journal").Position)
End If
End If

I've not worked with the BindingContext property. Is the RemoveAt method you are calling a member of the DataRowCollection class? If so, won't your code remove the row from the collection as opposed to delete it from the table? You could use the same principle to get the row and delete it, though.

 

[DavidT_macktool]

BindingContex Dataset operations

BindingContext manipulates the dataset. Calling the update method updates the table, In my case SQL database table. I'm not saying it's the best method, it has worked in all situations I've used it in. I allow many deletes (from a datagrid) and use a save button to commit changes to the database. I also check for changes and prompt the user to save when a close button is clicked.

examples of BindingContext:

FIRST RECORD OF DATASET
Me.BindingContext(DsJournal1, "Journal").Position = 0

LAST RECORD
Me.BindingContext(DsJournal1, "Journal").Position = Me.BindingContext(DsJournal1, "Journal").Count - 1

SAVE CURRENT RECORD POSITION - saverow is an integer
saverow = Me.BindingContext(DsJournal1, "Journal").Position

RETURN TO CURRENT RECORD POSITION
Me.BindingContext(DsJournal1, "Journal").Position = saverow