Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

deleting tables and rows from dataset asp.net 1.1

Posted on 2006-11-03
4
Medium Priority
?
370 Views
Last Modified: 2012-05-07
Hello experts I would just like some opinions on the most efficient way of deleting rows from a datatable and at the same time deleting a datatable from a dataset.

Basically i use a for each loop to loop all datatables in a dataset and also another loop to loop all rows in each datatable.
I check the row item values if they equal nothing I delete the row from the datatable I also want to check if the datatable has no rows delete it from the dataset.  I found the for each loop wont work because it changes the collection.  What is the best way to go about doing this?

  Dim shold, shold2 As String
            Dim shold3, shold4, shold5, shold6, shold7, shold8 As String()
       
 For Each dt As DataTable In main_dataset.Tables
                For Each dr In dt.Rows
                    shold = dt.Rows(x).Item(0)
                    shold2 = dt.Rows(x).Item(1)
                    shold3 = dt.Rows(x).Item(2).tostring.Split("-")
                    shold4 = dt.Rows(x).Item(3).tostring.Split("-")
                    shold5 = dt.Rows(x).Item(4).tostring.Split("-")
                    shold6 = dt.Rows(x).Item(5).tostring.Split("-")
                    shold7 = dt.Rows(x).Item(6).tostring.Split("-")
                    shold8 = dt.Rows(x).Item(7).tostring.Split("-")
                    If shold3(0) = " " And shold4(0) = " " And shold5(0) = " " And shold6(0) = " " And shold7(0) = " " And shold8(0) = " " Then
                        dt.Rows(x).Delete()
                    End If
                    x += 1
                Next
               main_dataset.Tables("dt").AcceptChanges()
                If dt.Rows.Count = 0 Then
                    main_dataset.Tables.Remove(dt)
                    main_dataset.AcceptChanges()
                End If
                x = 0
            Next
0
Comment
Question by:tentavarious
  • 2
4 Comments
 

Author Comment

by:tentavarious
ID: 17867815
I took out the for each loop and just used a for loop with counters how to I compensate for the change in the row and table counts so I dont get an out of index error

  For z As Integer = 0 To main_dataset.Tables.Count - 1
                For y As Integer = 0 To main_dataset.Tables(z).Rows.Count - 1
                    shold = main_dataset.Tables(z).Rows(y).Item(0)
                    shold2 = main_dataset.Tables(z).Rows(y).Item(1)
                    shold3 = main_dataset.Tables(z).Rows(y).Item(2).ToString.Split("-")
                    shold4 = main_dataset.Tables(z).Rows(y).Item(3).ToString.Split("-")
                    shold5 = main_dataset.Tables(z).Rows(y).Item(4).ToString.Split("-")
                    shold6 = main_dataset.Tables(z).Rows(y).Item(5).ToString.Split("-")
                    shold7 = main_dataset.Tables(z).Rows(y).Item(6).ToString.Split("-")
                    shold8 = main_dataset.Tables(z).Rows(y).Item(7).ToString.Split("-")
                    If shold3(0) = " " And shold4(0) = " " And shold5(0) = " " And shold6(0) = " " And shold7(0) = " " And shold8(0) = " " Then
                        main_dataset.Tables(z).Rows(y).Delete()
                        main_dataset.Tables(z).AcceptChanges()
                        If y <> 0 Then
                            y -= 1
                        End If
                    End If
                Next
                If main_dataset.Tables(z).Rows.Count = 0 Then
                    main_dataset.Tables.Remove(z)
                    main_dataset.AcceptChanges()
                    If z <> 0 Then
                        z -= 1
                    End If
                End If
            Next
0
 
LVL 25

Expert Comment

by:nauman_ahmed
ID: 17868118
The foreach should work fine. Where are you getting the error?

--Nauman.
0
 

Author Comment

by:tentavarious
ID: 17869360
Foreach wont work if I try deleting the row I get the error: Collection was modified; enumeration operation may not execute,  the error occurs when I try to go to the next row after deleting a row.  My for loop above will delete the table rows during the loop but throws an item does not exist when it gets to one row after the last row in the datatable, since the table.rows.count is being changed during the loop, because of the deletion I dont know if its possible to delete within the for loop.
I think I got it working by adding two arrays to record the row and table numbers that need to be deleted but it seems unnecessary, there must be a better way.


 Dim rowhold(11) As Integer
            Dim tablehold(14) As Integer
            Dim shold, shold2 As String
            Dim shold3, shold4, shold5, shold6, shold7, shold8 As String()
            Dim x As Integer = 0
            Dim d As Integer = 0
            Dim p As Integer = 0
             Dim dr As DataRow
            For z As Integer = 0 To main_dataset.Tables.Count - 1
                For y As Integer = 0 To main_dataset.Tables(z).Rows.Count - 1
                    '  For y As Integer = 0 To main_dataset.Tables(z).Rows.Count - 1
                    shold = main_dataset.Tables(z).Rows(y).Item(0).ToString
                    shold2 = main_dataset.Tables(z).Rows(y).Item(1).ToString
                    shold3 = main_dataset.Tables(z).Rows(y).Item(2).ToString.Split("-")
                    shold4 = main_dataset.Tables(z).Rows(y).Item(3).ToString.Split("-")
                    shold5 = main_dataset.Tables(z).Rows(y).Item(4).ToString.Split("-")
                    shold6 = main_dataset.Tables(z).Rows(y).Item(5).ToString.Split("-")
                    shold7 = main_dataset.Tables(z).Rows(y).Item(6).ToString.Split("-")
                    shold8 = main_dataset.Tables(z).Rows(y).Item(7).ToString.Split("-")
                    If shold3(0) = " " And shold4(0) = " " And shold5(0) = " " And shold6(0) = " " And shold7(0) = " " And shold8(0) = " " Then
                        rowhold(d) = y
                        d += 1
                    End If
                Next
                For na As Integer = LBound(rowhold) To UBound(rowhold)
                    main_dataset.Tables(z).Rows(rowhold(na)).Delete()
                Next
                main_dataset.Tables(z).AcceptChanges()
                If main_dataset.Tables(z).Rows.Count = 0 Then
                    tablehold(p) = z
                End If
            Next
            For na As Integer = LBound(tablehold) To UBound(tablehold)
                main_dataset.Tables.Remove(tablehold(na))
            Next
            main_dataset.AcceptChanges()
0
 
LVL 4

Accepted Solution

by:
satish_nagdev earned 750 total points
ID: 17879911
hi,
could you please try following
                DataRow[] drs = ds.Tables[0].Select("field1 = ' ' and field2 = ' ' and ..."); //whatever you want t odo
      for(int Cnt=0; Cnt < drs.Length; Cnt++)
      {
            ds.Tables[0].Rows.Remove(drs[i]);
      }
      ds.Tables.Remove(indx/name); //i dont know other than this method to remove a table.

hope it helps.

regards,
satish
                  
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
Integration Management Part 2
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question