Grouping rows of a datatable in VB asp.net 2.0

Question

Hello,

As the name suggests I am trying to group rows in a datatable. To go into further detail this table has identical rows except for one field(column). Basically what I am trying to do is put all the different fields of the identical rows and put them in single field whilst deleting the other rows.

Here is the syntax that I am currently using

   Dim i As Integer
    Dim j As Integer
    For i = 0 To (ds.Tables(0).Rows.Count() - 1) Step 1
        If (i < ds.Tables(0).Rows.Count()) Then
            roleHtml = "<table><tr><td>" + ds.Tables(0).Rows(i).Item("roleName") + "</td></tr>"
            For j = (ds.Tables(0).Rows.Count() - 1) To 0 Step -1
                If (ds.Tables(0).Rows(i).Item("UserName") = ds.Tables(0).Rows(j).Item("UserName")) And (ds.Tables(0).Rows(i).Item("roleName") IsNot ds.Tables(0).Rows(j).Item("roleName")) Then
                    roleHtml += "<tr><td>" + ds.Tables(0).Rows(j).Item("roleName") + "</td></tr>"
                    ds.Tables(0).Rows.Remove(ds.Tables(0).Rows(j))
                    i -= 1
                End If
            Next j
            roleHtml += "</table>"
            ds.Tables(0).Rows(i).Item("roleName") = roleHtml
        End If
    Next i

The problem is when deleting the rows their index changes and basically the field gets thrown in another row that has nothing to do with it.

Answer 1

I had to do something similar recently with a control-break style report.

I ended binding the data to a repeater control where the item template was just a literal control. Then I handled the OnItemDataBound event and had code that vaguely resembled yours to check that as long as the "control" (in the control-break sense rather than the web control sense) columns matched just add the value of the remaining column to a class-level variable and set e.Item.Visible to false. When they no longer match I leave e.Item.Visible as true (default) and set the Text property of the literal control to the html required for the row I was working on.

I'm sure there's a better way to do it, but it's just not easy to find info on control break reports with asp.net: the control word has a double meaning when searching in this domain that is hard to overcome.

Answer 2

That's pretty hard to read, which usually means it's ready to be restructured a bit.

Can you outline more what you want to achieve? What do you want left in your DataTable?

Does the data come sorted or can you do that in the db? If it's sorted then you only have to have a single loop through.

I'd do the following:

• sort the datatable
• make a new datatable for your results
• step through in order, keeping a variable of the last username and the current list of roles. If the username isn't the same as last then add a new row to your datatable with the last username and the current list of roles. If the username is the same then append to the list of roles. Don't forget to add the last row when you get to the end.

This gets around the problem of deleting rows from the collection you're stepping through.

Answer 3

Well, I can help with the looping structure. This doesn't match what you're doing exactly (it leaves the table intact and just builds a big string, and also assumes the table is sorted a particular way), but it will demonstrate classic control-break processing using your actual data. For this to work the table needs to be sorted by user and then role.

Dim i As Integer = 0
Dim CurUser As String = ""
Dim CurRole As String = ""
Dim result As new StringBuilder()
Dim r as DataRowCollection = ds.Tables(0).Rows

While i < r.Count
    'Next User:'
    CurUser = r(i)("UserName")
    result.AppendFormat("<h2>{0}</h2>", CurUser).AppendLine()
    result.AppendLine("<table>")

    While i < r.Count AndAlso CurUser = r(i)("UserName")
        'Next Role:'
        CurRole = r(i)("roleName")
        result.AppendFormat("<tr><td>{0}</td></tr>", CurRole).AppendLine()

        While i < r.Count AndAlso CurUser = r(i)("UserName") AndAlso CurRole = r(i)("roleName")
            i += 1 'Next Record: same user, role '
        End While
        'Finished this role'
    End While
    'Finished this user:'
     result.AppendLine("</table>").AppendLine()
End While

This has 3 nested loops, rather than just your two. However, it still gets linear performance: it will only iterate over each record once. It works because all the loops share the same counter, which is only incremented in the inner loop, and they all have the same base end condition.

Answer 4

Well apparently my code works. The line i -=1 was extra. Sorry for any inconvinience caused and thanks for the help

Answer 5

The looping part looks like it is covered.

But the deleting items selectively (or all) while looping can get nasty fast if you don't realize that the trick is to just work from the bottom up (or from the last element to the first, or however you want to phrase it).

That's the whole answer for the deletions part of the question.

When you work from last to first, deleting an item in a numbered list will only affect the indexes of the items that you have already processed - and who cares then, right?

-T.