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

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



Show quoteHide quote

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

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()

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

Show quoteHide quote

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 ?

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

Show quoteHide quote

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()

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
m" <samuelberthe***@googlemail.com> schreef in bericht
Show quoteHide quote

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 ?

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

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

Show quoteHide quote

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

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

Show quoteHide quote

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 ?

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

Show quoteHide quote

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

Source and design in two windows
SELECT @@IDENTITY
Convert double to string
How to put all code generated by the designer in external files (VS2005) ?
.NET 2.0 Conversion Wizard Flags Me.Cursor?
How to use a PDB file
fonts at runtime
Property 'FlowBreak' accessible only at design mode ?
Read a text file word by word
Intermittent VS.Net Pauses during VB.NET code writing