Inserting subtotal rows into a datatable

G

GaryB

I have an untyped datatable that has financial numbers and controls that
were populated by code (not a simple fill from a DA). Now I want to insert
subtotals into it. I wrote a sub to do so that uses InsertAt(myrow,i).
When I trace through the sub it is doing the insertat and the value of i is
correct. But when I bind my dataset to a datagrid all of my inserted rows
are at the end? code is below.

What am I doing wrong?
thanks,
G

Private Sub InsertSubtotals()
'spin through the datatable and insert subtotal records
Dim col1Sub As Decimal
Dim col1Fin As Decimal
Dim col2Sub As Decimal
Dim col2Fin As Decimal
Dim V1Sub As Decimal
Dim V1Fin As Decimal
Dim col3Sub As Decimal
Dim col3Fin As Decimal
Dim col4Sub As Decimal
Dim col4Fin As Decimal
Dim V2Sub As Decimal
Dim V2Fin As Decimal
Dim i As Integer
Dim PriorMajCat As String
Dim myCount As Integer = dsDisplay.Tables(0).Rows.Count - 1
For i = 0 To dsDisplay.Tables(0).Rows.Count - 1
Dim row As DataRow = dsDisplay.Tables(0).Rows(i)
If i = 0 Then
PriorMajCat = row("MajCat")
End If
If row("MajCat") <> PriorMajCat Then
Dim newRow As DataRow = dsDisplay.Tables(0).NewRow()
newRow("MajCat") = "TOT" & Val(i) & PriorMajCat
newRow("Account") = "Subtotal " & PriorMajCat
newRow("Col1") = col1Sub
newRow("Col2") = col2Sub
newRow("V1") = V1Sub
newRow("Col3") = col3Sub
newRow("Col4") = col4Sub
newRow("V2") = V2Sub
newRow("ShowFilter") = "show"
dsDisplay.Tables(0).Rows.InsertAt(newRow, i) 'I realize
that I need to adjust i
col1Sub = 0
col2Sub = 0
V1Sub = 0
col3Sub = 0
col4Sub = 0
V2Sub = 0
PriorMajCat = row("MajCat")
Else
'add to subtotals
col1Sub = col1Sub + row("Col1")
col2Sub = col2Sub + row("Col2")
V1Sub = V1Sub + row("V1")
col3Sub = col3Sub + row("Col3")
col4Sub = col4Sub + row("Col4")
V2Sub = V2Sub + row("V2")
'add to final totals
col1Fin = col1Fin + row("Col1")
col2Fin = col2Fin + row("Col2")
V1Fin = V1Fin + row("V1")
col3Fin = col3Fin + row("Col3")
col4Fin = col4Fin + row("Col4")
V2Fin = V2Fin + row("V2")

End If
Next
'insert last subtotal
Dim lastRow As DataRow = dsDisplay.Tables(0).NewRow()
lastRow("MajCat") = "TOT" & PriorMajCat
lastRow("Account") = "Subtotal " & PriorMajCat
lastRow("Col1") = col1Sub
lastRow("Col2") = col2Sub
lastRow("V1") = V1Sub
lastRow("Col3") = col3Sub
lastRow("Col4") = col4Sub
lastRow("V2") = V2Sub
lastRow("ShowFilter") = "show"
dsDisplay.Tables(0).Rows.Add(lastRow)

End Sub
 
J

John Saunders

GaryB said:
I have an untyped datatable that has financial numbers and controls that
were populated by code (not a simple fill from a DA). Now I want to insert
subtotals into it. I wrote a sub to do so that uses InsertAt(myrow,i).
When I trace through the sub it is doing the insertat and the value of i is
correct. But when I bind my dataset to a datagrid all of my inserted rows
are at the end? code is below.

What am I doing wrong?

How did you set the sort order for your DataTable? How did you set it for
your DataGrid?

John Saunders
 
G

GaryB

There is no "SortOrder" property for either a datagrid or a datatable that I
can find.

the rows appear in the sequence they were added which happens to be the
sequence they were in from another dataset.
G
 
S

Steven Cheng[MSFT]

Hi Gary,

From your description, you're using the InsertAt method of
DataRowCollection to insert new rows into a manually generated DataTable
and then bind it onto a webform datagrid. However, you found the new added
rows always display at the bottom of the datagrid, yes?

As John has mentioned, the means how to bind the datatable to the grid may
also change the display order since Only the raw "Rows" collection of the
DataTable will reflect the correct rows order in the collection. If we use
some other methods such as Select() or GetChildRows to retreive datarows
from DataTable, the retuned rows may not represent the actual order in the
Rows collection. So would you provide some info on how you bind the
DataTable onto the grid? Also, I suggest you manually use
for(i=0;i<Table.Rows.Count; i++)
....

to printout all the rows in the DataTable after you inserting new rows to
verify whether the actual order in the Rows collection is correct.

In addition, from the code you provided, seems you will insert new rows
in the following loop

For i = 0 To dsDisplay.Tables(0).Rows.Count - 1
Dim row As DataRow = dsDisplay.Tables(0).Rows(i)

That may cause the Table's Rows Collection be modified during the for loop
and thus the "i" iterate index may point to the incorrect record. Not sure
whether this will also be the potential cause of this issue.

Hope helps. If you have any other findings , please also feel free to post
here. thanks.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
 
J

John Saunders

GaryB said:
There is no "SortOrder" property for either a datagrid or a datatable that
I can find.

the rows appear in the sequence they were added which happens to be the
sequence they were in from another dataset.

This sounds like the answer to your question. Your total rows were added
last, so they show up at the bottom.

If you want to impose a sort order, then consider using a DataView and
binding to the DataView and not to the DataSet.

John Saunders
 
G

GaryB

Sorry for the distraction. My code was working fine. I was just looking at
output after an additional databind.
G
 
G

GaryB

Sorry for wasting your time. it was a bogus issue. The code was working
fine - I was just looking at the wrong output.
Gary
 
S

Steven Cheng[MSFT]

Hi GaryB,

Thanks for your followup and glad that the problem is figured out. Have a
nice day!

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top