Solved

deleting tables and rows from dataset asp.net 1.1

Posted on 2006-11-03
4
362 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

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
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…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

628 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