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
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