473,699 Members | 2,752 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Inserting subtotal rows into a datatable

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
Nov 18 '05 #1
7 3918
"GaryB" <gb@nospam.co m> wrote in message
news:Og******** ******@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(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
Nov 18 '05 #2
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:OO******** ******@TK2MSFTN GP10.phx.gbl...
"GaryB" <gb@nospam.co m> wrote in message
news:Og******** ******@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(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?


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

John Saunders

Nov 18 '05 #3
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.)

Nov 18 '05 #4
"GaryB" <gb@nospam.co m> wrote in message
news:%2******** ********@TK2MSF TNGP11.phx.gbl. ..
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

"John Saunders" <johnwsaundersi ii at hotmail.com> wrote in message
news:OO******** ******@TK2MSFTN GP10.phx.gbl...
"GaryB" <gb@nospam.co m> wrote in message
news:Og******** ******@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(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?


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

John Saunders


Nov 18 '05 #5
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:u3******** ******@tk2msftn gp13.phx.gbl...
"GaryB" <gb@nospam.co m> wrote in message
news:%2******** ********@TK2MSF TNGP11.phx.gbl. ..
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

"John Saunders" <johnwsaundersi ii at hotmail.com> wrote in message
news:OO******** ******@TK2MSFTN GP10.phx.gbl...
"GaryB" <gb@nospam.co m> wrote in message
news:Og******** ******@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



Nov 18 '05 #6
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-******@online.m icrosoft.com> wrote in message
news:2F******** ******@cpmsftng xa10.phx.gbl...
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.)

Nov 18 '05 #7
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.)

Nov 18 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

23
2227
by: Eva | last post by:
Hi i am trying to insert a new row into one of my datatabels that i have in my dataset when a button is clicked. here is my code Dim ClientInsRow As DataRow = dtClient.NewRo ClientInsRow("Surname") = txtSurname.Tex ClientInsRow("Forename") = txtForename.Tex ClientInsRow("OrgName") = txtOrganisation.Tex ClientInsRow("Address") = txtAddress.Tex
4
22417
by: Deepankar | last post by:
Hi, I was trying to change an example for SQL Server to work with Access db to insert image data. I have everything working except getting the OleDbParameter type for the image column. The table in access is : img ( id number , name Text, img number
0
1425
by: a | last post by:
I've read and split a delimited text file into a dataset. It looks fine in a datagrid (5 columns and 5,000 rows), but I've been trying, without success, to then insert the resulting dataset called "result" into a single sql table that has an auto-increment and PK column called ID, as well as the 5 columns from the dataset. Any suggestions on a way to perform the insert of the "result" dataset into the sql table?
2
4022
by: a | last post by:
NEW Post Here's my best guess at how to insert this dataset.... the code runs, but no new records are added to the sql table. I've read and split a delimited text file into a dataset. It looks fine in a datagrid (5 columns and 5,000 rows), but I've been trying, without success, to then insert the resulting dataset called "result" into a single sql table that has an auto-increment and PK column called ID,
6
2053
by: Pushpendra Vats | last post by:
Hi , I am trying to insert records into database. I am trying to write the following code. On button1 click event i am inserting five records and after that i am calling the update method of dataadapter to update the records in database. Records are inserting but all the five records have the same value and that too of that last ones.. More over i tried to use the acceptchanges methods for datatable and dataset but of no use
3
4270
by: rcoco | last post by:
Hi, I want to share this problem. I have a datagrid that will help me Insert data into sql database. So I made a button On my form so that when I press the button a new row on datagrid should be created and I could be able to insert data. But with this code below I've failed could someone help me and tell me where I'm going wrong: private void Page_Load(object sender, System.EventArgs e) { if (! IsPostBack)
6
30042
by: Manikandan | last post by:
Hi, I need to insert the datetime with milliseconds value into a datarow. My code as below DataTable testDataTable=new DataTable(); testDataTable.Columns.Add("updatedDateTime", typeof(DateTime)); DataRow testDateRow=surveyUpdatedDateDataTable.NewRow();
4
5588
by: Manikandan | last post by:
Hi, I'm inserting a datetime values into sql server 2000 from c# SQL server table details Table name:date_test columnname datatype No int date_t DateTime C# coding
3
17536
by: joel | last post by:
Hi guys, Im new to vb.net and i have this problem how do i insert another row into my datatable... (i guess it's like creating a for loop or something i really have no idea)... in this code i can only insert a single row. help pls...
0
8617
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9035
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8914
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8884
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6534
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5875
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4376
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3057
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2347
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.