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_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles btnDeleteIngr.Click
Dim vbResponse As Integer
vbResponse = MessageBox.Show("Are you sure that you want to
delete " + tbIngr.Text + "?", "Confirm Delete",
MessageBoxButtons.OKCancel, MessageBoxIcon.Warning)
If vbResponse = 1 Then
Dim dr As DataRow
Dim drv As DataRowView
drv = bsIngredients.Current
dr = drv.Row
dr.Delete()
bsIngredients.MoveNext()
taIngredients.Update(dsMenuPlanner.Ingredients)
dsMenuPlanner.AcceptChanges()
End If
End Sub

It's the "taIngredients.Update(dsMenuPlanner.Ingredients)" 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.

 

[Cor Ligthert [MVP]]

Randy,

I have somehow the idea that your update should be
taIngredients.Update(dsMenuPlanner.bsIngredients)

If it is not that, and it is as Robin suggest, add than before that row
cmb = oledbCommandbuilder(taIngredients)
or
SQLCommandBuilder(taIngredients)

'depending on what you use
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.aspx

I hope this helps,

Cor

 

[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]

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

 

[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]

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

 

[RobinS]

You're welcome. You might check out Dave Sceppa's book ADO.Net The Core
Reference -- it's very readable, and has a lot of code examples.

Good luck.
Robin S.

 

[tni]

As Cor said, if he deletes one, it is marked as a delete and no longer
displayed. Usually you call yourtableadapteror 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.






- Show quoted text -

What do yo umean by "not precise"?

 

[RobinS]

IMO, table adapters and strongly typed datasets are limiting. First of all,
every time you change the data source, you have to regenerate the strongly
typed dataset and re-set up the table adapters. And it is difficult to
separate the layers of an application.

I generally write my business apps following the 3-layer model --
separating my UI, data access class, and business layer. I use business
objects that are populated from stored procedures, and I can handle updates
however I want to. So if I have to do a join to pull the data I want, I
can handle the updates on both tables myself.

If you have to do a join to pull data using a table adapter, you can not
use the same table adapter and strongly typed dataset to do the update.

That's what I meant by "not precise enough". I think they work fine for
general vanilla stuff, and some people have gotten them to work with a
3-layer model by passing them back and forth via serialization, or by
creating the dataset in the business layer and passing it back to the UI,
where it is cast as the strongly typed dataset. To each his own.

Robin S.
----------------------------