Update Data Source from Data Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using VB.Net and I have been trying to store record changes to a SQL
Server table from changes made to a data table by using the data adabpter's
update command, but I am having no luck. The user will make changes to the
data table by entries in unbound text boxes & then displayed in a data grid,
and this works fine. By this process the user will change multiple records
in the Data Table. When the user accepts his entries he will hit a button
that will store the changes to the SQL Server table.
If i understand the Data Adapter Update command, it will update all the
modified rows in the specified data table in the Source table. I can do this
if i want to specify & update just one row. But am having trouble when
multiple rows were changed and need updating. Just about all the examples in
the books & help files suggest just one record at a time being updated &
those changes being made in bound controls. So any suggestions in how to
proceede?
Thanks
Gary
 
Hi Gary:

When you call Update, the Adapter looks to each row and checks the RowState
of it. If it's added, it will look to your Insert command and fire it with
whatever parameters you specify mapping to values in the row/column. If the
Rowstate is deleted, the same process occurs but with a Delete command. If
it's modified than the same thing occurs with an Update command.

So if you use DataBinding, the positioning of each row in the datatable is
done for you so that whatever changes you make are made to the underlying
row at a given index and you don't have to worry about it. However if it's
unbound, all you need to do is ensure that you get whatever the row is and
make the changes to that Row's values. You can implement your own
navigation mechansim. So if I understand you correctly, at this point you'll
have multiple rows that have been changed. If you have a correctly
configured adapter, all you need to do is call Update and everythign should
be handled for you behind the scenes. If you're having a problem, the first
thing to do is make sure that the rowstates are changed. The simplest way to
do this is via

System.Diagnostics.Debug.Assert(DataSetName.HasChanges, "No changes are
present but should be");
Put this right before the line that calls the Update method of the adapter.
If the assertion doesn't fail, then you do have changes and chances are the
problem is with the Commands. Make sure that you have a command for each
action you will take (if you don't have a Delete command but no rows have a
rowstate of deleted, everything will be fine. But if you do have a rowstate
of deleted for at least one row and no delete command, the update will blow
up. So in general, unless you're positive that only x action will be taken,
make sure you have a valid command for each action that can be taken). Make
sure that all the parameters are in place and that they are mapped to
columns in the underlying data table.

How are you generating your commands? Let me know and hopefully I can walk
you through everything ;-)

Bill
 
Bill,
Thanks for the feed back. As it turns out the 'Where' portion of my update
statement did not have appropriately set parameters. Thus all records were
being updated with a single value intended for one record. But all is well
now. Thanks again.
Gary
 
Back
Top