Solved

deleting tables and rows from dataset asp.net 1.1

Posted on 2006-11-03
4
359 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 250 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.

752 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