Discarding rows in dataset.

[Graham Blandford]

Hi all,

Would someone be able to tell me the most 'graceful' way of removing
unwanted rows from a dataset based on a condition prior to update? OR,
resetting the rows all to unchanged after they are initally added to the
recordset.

I create a dataset, which begins empty after the initial .Fill.

Then I create several rows with some default information, leaving one
critical piece of information value as 0;

dr = ds.Tables("mytable").NewRow()
With dr
.Item("basicdata1") = myvalue1
.Item("basicdata2") = myvalue2
.Item("criticaldata") = 0
.Item("created") = Now()
End With
ds.Tables("mytable").Rows.Add(dr)

Then, my update does something like;

da.Update(ds, "mytable")

All pretty basic stuff.... but what I would like is to ONLY insert new rows
into the database that have a "criticaldata" value <> 0 and discard the
rest. I could use the GetChanges method if I knew how to mark the rows as
'unchanged' immediately after creation - but don't know how.

Any help would be greatly appreciated.
Thanks,

Graham

 

[William Ryan eMVP]

YOu can use GetChanges and just get the rows with RowState or added. Then
call update on the GetChanges table - then acceptChanges on the original


--
W.G. Ryan MVP Windows - Embedded

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups

 

[Gerard]

I guess row.AcceptChanges will do.

 

[Jay B. Harlow [MVP - Outlook]]

Graham,
In addition to GetChanges with the DataRowState you can use
DataTable.Select.

I would recommend DataTable.Select as you are returned references to the
rows in the existing DataSet, where as GetChanges will return a new DataSet
(with new rows).

David Sceppa explains the difference between GetChanges & Select when you
intend on updating the database in his book "Microsoft ADO.NET - Core
Reference" from MS
Press.

If you are doing a lot with ADO.NET I strongly recommend Sceppa's book,
which is a good tutorial on ADO.NET as well as a good desk reference once
you know ADO.NET.

Hope this helps
Jay

 

[Graham Blandford]

I don't think I made myself very clear in my original post - either that or
I am reading the response wrongly....

I have an empty dataset.
In code, I add say 3 rows - "row a", "row b" and "row c" with say a single
value which I set to 0 (zero).

I am led to believe that at this point, these rows will all be marked as
"added".

The user - through the interface may or may not change the value of these
rows to say, 1.

Now, if I perform an update on the dataset I ONLY want the rows that have
been changed to a value of 1.

As far as I understand, the GetChanges method isn't going to help me,
because the rowstate on all of the rows will be 'added' and therefore any
'change' to a value will be ignored, as it already has an 'added' state.

Is this making sense?

Thanks,
Graham

 

[Jay B. Harlow [MVP - Outlook]]

Graham,

I am led to believe that at this point, these rows will all be marked as
"added".

Correct, they will be marked as Added, you can call AcceptChanges on the
dataset & they will no longer be marked as Added.

Thus allowing you to call GetChanges to get the row that the user changed to
a 1.

If you want to look for rows without 0s, you could use DataTable.Select to
return the rows without 0s, then pass this array of rows to your
DataAdapter.

Hope this helps
Jay

 

[Graham Blandford]

Thanks Jay,

I tried the former method, as it appears to be a more graceful answer - but
am a little confused. I call the AcceptChanges method once I have created my
new rows.

Then, after modifying one of the rows, I am doing something like this;

mynewdataset = myoriginaldataset.GetChanges() ' I also tried
GetChanges(Datarowstate.Modified)..
da.Update(mynewdataset, "mytable") ' Also tried .Update(myoriginalset,
"mytable") ...

Now I get the error;

"An unhandled exception of type 'System.Data.DBConcurrencyException'
occurred in system.data.dll
Additional information: Concurrency violation: the UpdateCommand affected 0
records."

Am I doing something wrong? - Should I be using a different method of
update?

If I can't do the update using this method, you mention the
DataTable.Select - I can use this - but am unsure on how you would process
the array of rows against the dataadaptor... I'll go looking for this, but
if you have a moment to explain I'd very much appreciate it....


Thanks again,
Graham

 

[Jay B. Harlow [MVP - Outlook]]

Graham,
Appearences can be conceiving ;-)

What are you calling AcceptChanges on?

Are you calling it before or after you change the zeros to a one?

Can you post an actual sample of what you are attempting?

I don't have a sample, however you should be able to:
- create your dataset
- add the rows with zeros
- dataset.acceptchanges
- allow user to change zeros to one
- getchanges or datatable.select
- dataadapter.update

Reading the help on DBConcurrencyException, it sounds like you have a bad
update statement.


There is a overload on DataAdapter.Update that accepts an array of DataRows
(from the DataTable.Select for example).

Hope this helps
Jay

 

[Graham Blandford]

Hi Jay,

Still no luck - I get the concurrency violation whether I overload the using
the .Select OR the GetChanges method.
The update statement looks fine, and indeed works if I do a straight forward
update without trying to filter out the zero-valued records.

Here's the code - inline with your requests.

Thanks for helping out.
Graham

- create your dataset

Dataset is defined in the IDE along with the adaptor.

- add the rows with zeros

' Performed within a couple of nested loops;
drLinkMAN_crop_operations =
dsLinkMAN_crop_operations_1.Tables("crop_operations").NewRow()
With drLinkMAN_crop_operations
.Item("summit_gdcid") = summit_gdcid
.Item("summit_farm_id") = drLinkMAN_field_trans.Item("summit_farm_id")
.Item("summit_field_id") = summit_field_id
.Item("year") = int_current_year
.Item("farm_id") = drLinkMAN_field_trans.Item("farm_id")
.Item("field_id") = drLinkMAN_field_trans.Item("field_id")
.Item("section_id") = drLinkMAN_field_trans.Item("section_id")
.Item("crop_id") = 0
.Item("created") = Now()

dsLinkMAN_crop_operations_1.Tables("crop_operations").Rows.Add(drLinkMAN_cro
p_operations)
End With

- dataset.acceptchanges

' After the loop(s).
dsLinkMAN_crop_operations_1.AcceptChanges()

- allow user to change zeros to one

Done via the interface - works - data is correct if I don't try to
remove the zero-valued rows.

- getchanges or datatable.select
- dataadapter.update

Dim dsMyChanges As New DataSet
dsMyChanges = dsLinkMAN_crop_operations_1.GetChanges(DataRowState.Modified)
If dsMyChanges.HasChanges Then
Dim intx As Int16
daLinkMAN_crop_operations.Update(dsMyChanges) '<---------------------
HERE IS WHERE I GET THE VIOLATION ERROR
If dsMyChanges.HasErrors Then
Dim e As String = ""
End If
End If

 

[Graham Blandford]

Jay. I think I know the problem.

Would I be correct in thinking that when then I AcceptChanges on the
dataset, and then make a change, the row is marked as 'Modified' - and
subsequently calls the UpdateCommand, when in fact it needs to be calling
the InsertCommand??...

Cant believe it never clicked before... now I have to figure out how to
filter WITHOUT using the AcceptChanges. I guess I could use the
OnRowUpdating Handler - will that allow me to ignore a row if I mark it?

Thanks,
Graham

 

[Graham Blandford]

Hi Jay,

Figured out a fairly graceful solution.
Added the handler, and deal with it in the Onrowupdating method: -

Protected Shared Sub OnRowUpdating(ByVal sender As Object, ByVal args As
OleDbRowUpdatingEventArgs)

If args.StatementType = StatementType.Insert Then
If args.Command.Parameters("crop_id").Value = 0 Then
args.Status = UpdateStatus.SkipCurrentRow
End If
End If

End Sub

Works well!

Once again, thanks for all your help.
Graham

 

[Jay B. Harlow [MVP - Outlook]]

Graham,
You are correct, if you AcceptChanges, all your "new" rows will now be
Modified & not Added, the Adapter will attempt to call the UpdateCommand
instead of the InsertCommand.

If due to the nature of your process all "Modified" rows are really inserts,
I probably would simply put the Insert Statement in the UpdateCommand also,
which might be "easier" then handling the RowUpdating event...

An alternative I used in a couple of other places is to have a single
"Update" stored procedure, that the SP itself decides if its an insert or
update. Both the UpdateCommand & InsertCommand of the DataAdapter would use
this same stored procedure...

Hope this helps
Jay

 

[Cor Ligthert]

Hi Graham,

In this part of the message you show exactly where the acceptchanges is for.

I have an empty dataset.
In code, I add say 3 rows - "row a", "row b" and "row c" with say a single
value which I set to 0 (zero).

Here you have to do the acceptchanges.

And than all changes after this are marked as updated.

I hope this helps?

Cor