delete row from dataview

[Sam]

Hi,

As I loop through a dataview's records, I delete datarow based on a
condition. However I don't want to commit those deletions until the
loop ends. With a datatable, i would just set row.delete() and call
AcceptChanges when the loop ends. But how can I do that with a dataview
?

Thank you

 

[Cor Ligthert [MVP]]

Sam,

Don't mix up the words Delete and Remove in AdoNet.

Delete marks a datarow as to be Removed by an acceptchanges or an DA.Update.
Remove removes a datarow from a DataTable.

I am not sure what you are doing, because it seems strange to me, however
you will have a solution with this.
If you do by instance this.

DataTable.AcceptChanges 'assuming that al other rowstates can be set to this
drv(0).Delete
DataTable.AcceptChanges

Than the first row that fullfils the rowfilter will be removed.

Which is than the same as
DataTable.rows.remove(drv(0).row)

I hope this helps,

Cor

 

[Sam]

Cor,
Thanks for replying.
I'm sorry but I don't understand your example. Maybe I haven't
explained properly what I want to do.
Watch this:

For each row as datarowview in myDataView

if my condition is met then
mark row to be removed
end if

Next

commit changes here, meaning remove the rows from myDataView.

Now, I've come up with a solution, which is to convert the dataview to
a datatable using ToTable function. Then I can use .Delete and
AcceptChanges on this datatable, and then reassign the datattable to be
the source for my dataview. I know it's a bit ackward but it's the best
I could come up with. Do you have a better solution ?

Sam

 

[Virgil]

Hi Sam,

If I understand your question correctly, you want to iterate through
the rows in a view, delete some of them (conditionally), then commit
those deletes. Hopefully the code below solves your question.

Dim Row As DataRowView

For Each Row In View
If Condition = True Then
Row.Delete()
End If
Next
View.Table.AcceptChanges()

 

[Cor Ligthert [MVP]]

Sam,

Sorry I forgot to answer that part, I was meaning something as Virgil wrote
however than extended with the RejectChanges if it not should be done.

http://msdn2.microsoft.com/en-us/library/system.data.datatable.rejectchanges.aspx

Be aware that you do before the AcceptChanges forever an update if that is
necessary, because your changes are by the AcceptChanges not anymore
recognisable.

I hope this helps,

Cor

 

[Sam]

Hi,

Thank to both of you for helping me out.
Here's my code, base on what I had, mixed with Virgil's sample:

For Each row As DataRowView In dvOrig
'how many times to we have the QueryGroupId in the table
drResult = dvOrig.FindRows(row("QueryGroupId"))
'if it appears more than once then add it to dtDest and
'delete it from dtOrig
If Not drResult.Length > 1 Then
row.Delete()
End If
Next

The issue occurs on the 3rd shot in the loop as it raises the
exception: There is no row at position 3 in the view.
This is because the row was marked as Delete, and now it tries to use
FindRows.
How can I get around this ?

 

[Cor Ligthert [MVP]]

Sam,

This in one of the good examples why you should do deleting in a collection
forever down to top.
(I did not look that well at the sample of Virgil)

For i as integer = mycollection.count-1 to 0 step -1
'the code
Next

Cor

 

[Sam]

Yes!!!!!!!!!!
Thank you so much Cor, it works !

Just one more question if you don't mind ! Before I delete the row, I
want to add it to another dataview. How can I do that, I can't figure
it out... Here's my code so far:

Dim drResult As DataRowView()
dvOrig.Sort = "QueryGroupId"

For i As Integer = dvOrig.Count - 1 To 0 Step -1
drResult = dvOrig.FindRows(dvOrig(i).Item("QueryGroupId"))

If Not drResult.Length > 1 Then
'HERE I WANT TO ADD THE ROW TO ANOTHER DATAVIEW, WHICH
HAS THE SAME
'COLUMNS (SAME STRUCTURE) AS dvOrig.
dvOrig.Item(i).Delete()
End If
Next
dvOrig.Table.AcceptChanges()

 

[Cor Ligthert [MVP]]

Sam,

As I thought does there go something wrong,

A dataview is a view on a table. To delete it from a view, you only have to
change the part that is in the rowfilter from that.

Now you are removing the rows completely from your table. From your message
I understand now that, that is not the purpose.

So as sample

dim dv1 as new dataview(mytable)
dim dv2 as new dataview(mytable)

dv1.rowfilter = "City = 1)"
dv2.rowfilter = "City = 2)"

Now you have not to delete in your loop however something as

for i as integer = dv1.count -1 to 0
drv("City") = "2"
next

I hope this helps,

Cor




"Sa

 

[Sam]

This is not that simple. Here's what I'm trying to achieve.

I have two dataviews with same structure. They both have a field named
'QueryGroupId'. In dvDest, all the records have QueryGroupId = NULL. In
dvOrig, there are records which have the same QueryGroupId.

I'm trying to delete records from dvOrig where the QueryGroupId is
unique, that is, it does not appear more than once. But before I delete
this record, I want to move it to dvDest.

Therefore I can't use rowfilter, unless you know a way to get the
records that were rejected by the filter ?
Does it make it clearer ?

 

[Sam]

Ok, I've managed to do it. It looks a bit ugly though, so if someone
has a more elegant way to do it, I'd like to hear from you

Dim dt As DataTable = dvDest.ToTable

Dim drResult As DataRowView()
dvOrig.Sort = "QueryGroupId"

For i As Integer = dvOrig.Count - 1 To 0 Step -1
drResult = dvOrig.FindRows(dvOrig(i).Item("QueryGroupId"))

If Not drResult.Length > 1 Then

Dim r As DataRow = dt.NewRow
r("WebId") = dvOrig(i).Item("WebId")
r("QueryId") = dvOrig(i).Item("QueryId")
r("QueryName") = dvOrig(i).Item("QueryName")
r("Attributes") = dvOrig(i).Item("Attributes")
r("SortOrder") = dvOrig(i).Item("SortOrder")
r("QueryGroupId") = dvOrig(i).Item("QueryGroupId")
r("QueryGroupName") = dvOrig(i).Item("QueryGroupName")
dt.Rows.Add(r)

dvOrig.Item(i).Delete()
End If
Next
dvOrig.Table.AcceptChanges()

dvDest.Table = dt

 

[Cor Ligthert [MVP]]

Sam,

A dataview holds not any data. It shows data in a datatable depening on the
"Sort" and "RowFilter" settings.

Therefore you cannot delete data from a dataview. You can use it to delete
datarows from a datatable.

If you mean that the dataview is referencing to different tables. Than it
can be another point.
Than you can remove a row from a DataTable. What is not the same as deleting
a datarow.

In fact are you setting the reference in the DataTable rowcollection to zero
while the DataRow still exist however becomes unavailable and will be
therefore be released by the Gargabe Collector.

However if you than first do

Dim dr as datarow = Table1.row(x)
'and than remove it
Table1.rows.remove(dr)
'than you can add it to a another table with exact the same description
Table2.rows.add(dr)

I hope that this gives the idea?

Cor

 

[Sam]

Cor,
Are you sure you can do that ?

Dim dtdest As DataTable = dvDest.ToTable
Dim dtOrig As DataTable = dvOrig.ToTable

For i As Integer = dvOrig.Count - 1 To 0 Step -1
drResult = dvOrig.FindRows(dvOrig(i).Item("QueryGroupId"))
If Not drResult.Length > 1 Then

Dim dr As DataRow = dtOrig.Rows(i)
dtOrig.Rows.Remove(dr)
dtdest.Rows.Add(dr) <------------ raises
exception: This row already belongs to another table
End If
Next

dtOrig.AcceptChanges()
dtdest.AcceptChanges()

dvDest.Table = dtdest
dvOrig.Table = dtOrig

 

[Cor Ligthert [MVP]]

Sam,

Let us make it easier, how did you make that dataview, now I am up with all
kind of code and a little bit writing things I have not the grip on.

Cor

 

[Sam]

Cor,

Don't worry, I can stick with the method I've come up a little bit
earlier as it works anyway. But if you decide to help me and let me
know about a clever way to do it, here are some explanations :


1. SQL Table : [Queries] {QueryId, QueryName, QueryGroupId}

2. myDataTable contains records of this table above.

3. dvDest and dvOrig are dataviews on myDataTable. dvDest filters those
records with QueryGroupId = NULL and dvOrig contains those records with
QueryGroupId <> NULL

4. If a QueryGroupId appears only ONCE in dvOrig, then it should be
removed from dvOrig and be put into dvDest.

Hope it makes it clearer ?

 

[Cor Ligthert [MVP]]

Sam

So you mean something as not tested.

dvOrg.Sort = "QueryGroupId"
for i as integer = dvOrg.Count - 1 to 0
Dim dvTest as new DataView(myDataTable)
dvTest.Rowfilter = "QuerygroupId = '" & dvOrg(i)(QueryGroupId) & "'"
if dvTest.Count = 1 then dvOrg(0)(QueryGroupId) = dbValue.Null
next


Cor

 

[Sam]

Oh yes, this is amazing Cor !

SInce the source datatable is the same for both dataview, the records
which don't match dvOrg anymore (because you set QueryGroupId to null)
are moved to dvDest automatically !!!

Here is the final code, and it works so well :!! Thank you again !

'filter queries which don't belong to any group
dvSingleQueries.Table = dtQueries
dvSingleQueries.RowFilter = "QueryGroupId is NULL"

'filter queries which belong to a group
dvGroupQueries.Table = dtQueries
dvGroupQueries.RowFilter = "QueryGroupId is not NULL"

dvGroupQueries.Sort = "QueryGroupId"
For i As Integer = dvGroupQueries.Count - 1 To 0 Step -1
Dim dvTest As New DataView(dtQueries)
dvTest.RowFilter = "QuerygroupId = '" &
dvGroupQueries(i)("QueryGroupId") & "'"
If dvTest.Count = 1 Then
dvGroupQueries(i)("QueryGroupId") = DBNull.Value
Next