Inserting subtotal rows into a datatable

[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.Table s(0).Rows.Count - 1
For i = 0 To dsDisplay.Table s(0).Rows.Count - 1
Dim row As DataRow = dsDisplay.Table s(0).Rows(i)
If i = 0 Then
PriorMajCat = row("MajCat")
End If
If row("MajCat") <> PriorMajCat Then
Dim newRow As DataRow = dsDisplay.Table s(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("ShowFil ter") = "show"
dsDisplay.Table s(0).Rows.Inser tAt(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.Table s(0).NewRow()
lastRow("MajCat ") = "TOT" & PriorMajCat
lastRow("Accoun t") = "Subtotal " & PriorMajCat
lastRow("Col1") = col1Sub
lastRow("Col2") = col2Sub
lastRow("V1") = V1Sub
lastRow("Col3") = col3Sub
lastRow("Col4") = col4Sub
lastRow("V2") = V2Sub
lastRow("ShowFi lter") = "show"
dsDisplay.Table s(0).Rows.Add(l astRow)

End Sub

[John Saunders]

Re: Inserting subtotal rows into a datatable

"GaryB" <gb@nospam.co m> wrote in message
news:OgEB4KFxEH A.2540@TK2MSFTN GP09.phx.gbl...[color=blue]
>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?[/color]

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

John Saunders

[GaryB]

Re: Inserting subtotal rows into a datatable

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

"John Saunders" <johnwsaundersi ii at hotmail.com> wrote in message
news:OO5vJSFxEH A.2568@TK2MSFTN GP10.phx.gbl...[color=blue]
> "GaryB" <gb@nospam.co m> wrote in message
> news:OgEB4KFxEH A.2540@TK2MSFTN GP09.phx.gbl...[color=green]
>>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(myro w,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?[/color]
>
> How did you set the sort order for your DataTable? How did you set it for
> your DataGrid?
>
> John Saunders
>
>[/color]

[Steven Cheng[MSFT]]

RE: Inserting subtotal rows into a datatable

Hi Gary,

From your description, you're using the InsertAt method of
DataRowCollecti on 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.Table s(0).Rows.Count - 1
Dim row As DataRow = dsDisplay.Table s(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.)

[John Saunders]

Re: Inserting subtotal rows into a datatable

"GaryB" <gb@nospam.co m> wrote in message
news:%231x7e6Fx EHA.4040@TK2MSF TNGP11.phx.gbl. ..[color=blue]
> 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.[/color]

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

[color=blue]
> "John Saunders" <johnwsaundersi ii at hotmail.com> wrote in message
> news:OO5vJSFxEH A.2568@TK2MSFTN GP10.phx.gbl...[color=green]
>> "GaryB" <gb@nospam.co m> wrote in message
>> news:OgEB4KFxEH A.2540@TK2MSFTN GP09.phx.gbl...[color=darkred]
>>>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(myr ow,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?[/color]
>>
>> How did you set the sort order for your DataTable? How did you set it for
>> your DataGrid?
>>
>> John Saunders
>>
>>[/color]
>
>[/color]

[GaryB]

Re: Inserting subtotal rows into a datatable

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

"John Saunders" <johnwsaundersi ii at hotmail.com> wrote in message
news:u3mOgIbxEH A.1392@tk2msftn gp13.phx.gbl...[color=blue]
> "GaryB" <gb@nospam.co m> wrote in message
> news:%231x7e6Fx EHA.4040@TK2MSF TNGP11.phx.gbl. ..[color=green]
>> 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.[/color]
>
> 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
>
>[color=green]
>> "John Saunders" <johnwsaundersi ii at hotmail.com> wrote in message
>> news:OO5vJSFxEH A.2568@TK2MSFTN GP10.phx.gbl...[color=darkred]
>>> "GaryB" <gb@nospam.co m> wrote in message
>>> news:OgEB4KFxEH A.2540@TK2MSFTN GP09.phx.gbl...
>>>>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(my row,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
>>>
>>>[/color]
>>
>>[/color]
>
>[/color]

[GaryB]

Re: Inserting subtotal rows into a datatable

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

"Steven Cheng[MSFT]" <v-schang@online.m icrosoft.com> wrote in message
news:2FrxfOUxEH A.1288@cpmsftng xa10.phx.gbl...[color=blue]
> Hi Gary,
>
> From your description, you're using the InsertAt method of
> DataRowCollecti on 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.Table s(0).Rows.Count - 1
> Dim row As DataRow = dsDisplay.Table s(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.)
>[/color]

[Steven Cheng[MSFT]]

Re: Inserting subtotal rows into a datatable

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