Deleting a Row from the Data Source with Table Adapter

[Randy]

Hi,
Trying to pass along a table row delete to the datasource, but I'm
crashing. Here is the code:

Private Sub btnDeleteIngr_C lick(ByVal sender As Object, ByVal e As
System.EventArg s) Handles btnDeleteIngr.C lick
Dim vbResponse As Integer
vbResponse = MessageBox.Show ("Are you sure that you want to
delete " + tbIngr.Text + "?", "Confirm Delete",
MessageBoxButto ns.OKCancel, MessageBoxIcon. Warning)
If vbResponse = 1 Then
Dim dr As DataRow
Dim drv As DataRowView
drv = bsIngredients.C urrent
dr = drv.Row
dr.Delete()
bsIngredients.M oveNext()
taIngredients.U pdate(dsMenuPla nner.Ingredient s)
dsMenuPlanner.A cceptChanges()
End If
End Sub

It's the "taIngredients. Update(dsMenuPl anner.Ingredien ts)" line that
crashes. The error is "Update requires a valid DeleteCommand when
passed DataRow collection with deleted rows."

I can't figure out how to resolve this. Can anybody help?

Thanks,
Randy

[RobinS]

Your data adapter must have InsertCommand, DeleteCommand, and UpdateCommand
objects defined for it in order to perform updates.

Robin S.
--------------------------------
"Randy" <sp***********@ gmail.comwrote in message
news:11******** **************@ p77g2000hsh.goo glegroups.com.. .

Hi,
Trying to pass along a table row delete to the datasource, but I'm
crashing. Here is the code:

Private Sub btnDeleteIngr_C lick(ByVal sender As Object, ByVal e As
System.EventArg s) Handles btnDeleteIngr.C lick
Dim vbResponse As Integer
vbResponse = MessageBox.Show ("Are you sure that you want to
delete " + tbIngr.Text + "?", "Confirm Delete",
MessageBoxButto ns.OKCancel, MessageBoxIcon. Warning)
If vbResponse = 1 Then
Dim dr As DataRow
Dim drv As DataRowView
drv = bsIngredients.C urrent
dr = drv.Row
dr.Delete()
bsIngredients.M oveNext()
taIngredients.U pdate(dsMenuPla nner.Ingredient s)
dsMenuPlanner.A cceptChanges()
End If
End Sub

It's the "taIngredients. Update(dsMenuPl anner.Ingredien ts)" line that
crashes. The error is "Update requires a valid DeleteCommand when
passed DataRow collection with deleted rows."

I can't figure out how to resolve this. Can anybody help?

Thanks,
Randy

[Cor Ligthert [MVP]]

Randy,

I have somehow the idea that your update should be
taIngredients.U pdate(dsMenuPla nner.bsIngredie nts)

If it is not that, and it is as Robin suggest, add than before that row
cmb = oledbCommandbui lder(taIngredie nts)
or
SQLCommandBuild er(taIngredient s)

'depending on what you use
http://msdn2.microsoft.com/en-us/lib...ndbuilder.aspx

I hope this helps,

Cor
"Randy" <sp***********@ gmail.comschree f in bericht
news:11******** **************@ p77g2000hsh.goo glegroups.com.. .

Hi,
Trying to pass along a table row delete to the datasource, but I'm
crashing. Here is the code:

Private Sub btnDeleteIngr_C lick(ByVal sender As Object, ByVal e As
System.EventArg s) Handles btnDeleteIngr.C lick
Dim vbResponse As Integer
vbResponse = MessageBox.Show ("Are you sure that you want to
delete " + tbIngr.Text + "?", "Confirm Delete",
MessageBoxButto ns.OKCancel, MessageBoxIcon. Warning)
If vbResponse = 1 Then
Dim dr As DataRow
Dim drv As DataRowView
drv = bsIngredients.C urrent
dr = drv.Row
dr.Delete()
bsIngredients.M oveNext()
taIngredients.U pdate(dsMenuPla nner.Ingredient s)
dsMenuPlanner.A cceptChanges()
End If
End Sub

It's the "taIngredients. Update(dsMenuPl anner.Ingredien ts)" line that
crashes. The error is "Update requires a valid DeleteCommand when
passed DataRow collection with deleted rows."

I can't figure out how to resolve this. Can anybody help?

Thanks,
Randy

[Randy]

Thank you both for your advice. I thought that I had a DeleteCommand
set up, but I did it wrong. I did it in the table adapter on the
designer rather than in the properties. That's now fixed and the code
executes without error.

I still have one problem. I haven't set up the CommandText
correctly. What I have in there is "DELETE FROM Ingredients", which
obviously deletes all records from the table Ingredients. Can you
help me to change this query to just delete the table that the user
has selected? It's always going to be the current row. The code
above has not changed. Let me know if I can provide more information.

Thanks again!
Randy

[Randy]

Can you help me to change this query to just delete the table that the user

has selected?

Sorry, I meant the row that the user has selected (not the table).

[Cor Ligthert [MVP]]

Randy,

That depends how the user has selected it, I know at least 10 simple ways.

Cor

"Randy" <sp***********@ gmail.comschree f in bericht
news:11******** *************@o 5g2000hsb.googl egroups.com...

>Can you help me to change this query to just delete the table that the
user
has selected?

Sorry, I meant the row that the user has selected (not the table).

[Randy]

Using the binding navigator, the user scrolls through the records in
the table. If they see one that they don't want any longer, they
click a button intended to delete the current row from the data
table. Very straightforward from a user perspective, and I think is
pretty simple programatically . I just don't know enough to write the
SQL command so that it just deletes the current row and not all of the
records.

Thanks for any advice.
Randy

[Cor Ligthert [MVP]]

Randy,

If it is a row in a table, than the row will be marked as deleted. (it will
not deleted, if you want that, than you have to remove it, but than it will
not be deleted from yourdatabase).

It will not be showed anymore, however it is still there, the same is for an
updated and a new record, they are marked as updated and new. It is clever
done, if you delete a row and afterwards insert in your idea a new one, the
rowstate is set to "update".

When you now give your datatable to the database using the dataadapter or
tableadapter, than select those the rowstate which are changed, those will
be processed in the database and automatically is done by the adapter, the
acceptchanges which is to set the rowstates to all changes done.

This is one of the methods to do your updates, I hope it gives an idea,

Cor


"Randy" <sp***********@ gmail.comschree f in bericht
news:11******** **************@ l77g2000hsb.goo glegroups.com.. .

Using the binding navigator, the user scrolls through the records in
the table. If they see one that they don't want any longer, they
click a button intended to delete the current row from the data
table. Very straightforward from a user perspective, and I think is
pretty simple programatically . I just don't know enough to write the
SQL command so that it just deletes the current row and not all of the
records.

Thanks for any advice.
Randy

[RobinS]

As Cor said, if he deletes one, it is marked as a delete and no longer
displayed. Usually you call your tableadapter or dataadapter after the user
has completed all changes. The adapter checks the state of each row. If it
was Added, it executes the Insert command; if it was Modified, it executes
the Update command; if it was Delete, it executes the Delete command. The
delete command should be something like "DELETE FROM myTable WHERE
myPrimaryKey = @myPrimaryKey", and you pass in the parameter.

I don't use table adapters; they're not precise enough for me. I write my
own stored procs and execute my own code against them. Are you actually
using a strongly typed dataset and table adapter, or are you using a data
adapter and regular dataset?

Robin S.
--------------------------
"Randy" <sp***********@ gmail.comwrote in message
news:11******** **************@ l77g2000hsb.goo glegroups.com.. .

Using the binding navigator, the user scrolls through the records in
the table. If they see one that they don't want any longer, they
click a button intended to delete the current row from the data
table. Very straightforward from a user perspective, and I think is
pretty simple programatically . I just don't know enough to write the
SQL command so that it just deletes the current row and not all of the
records.

Thanks for any advice.
Randy

[Randy]

I was using a table adapter, but I've since switched to data
adapters. I'm finding more resources explaining data adapters, so I'm
heading in that direction. I think that for the simple types of
actions that I am doing, either would probably work fine.

Thanks again for all of your help.

Randy