Group By on Datatable 2

[RonRepp]

Hi all:

Through thread796-1127305 I see how to filter a datatable, but can you also group by and other SQL syntax?

Any help is greatly appreciated.



Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[djj55]

Would it be posible to do the group by at download by setting it in the commandtext?
djj

 

[RonRepp]

DJJ:

Yes, it is possible, but not what I had in mind.

Basically, I have a form that when the user hits a "commit" button, it writes a line to a datagrid. In turn, another section of the form displays the payment info. If one vendor is used more than once, I would like to group it.

Alternatively, I could (and probably will if I don't get an answer in this forum) send the dataset to a temp table in SQL and parse it there, but I didn't want to do that.

Thanks for the input, though.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[djj55]

Sorry I am out of ideas, have you looked at DataGridSortCommandEventArgs Class?
djj

 

[jebenson]

Here's a way to do this...I think.

Don't use the datatable that is actually holding the records to display the records in the grid, but use a separate datatable and dataview to show the grouping.

First, create the datatable for the grouping:

Dim dtGroups As Datatable

dtGroups = New Datatable

'Add columns to the datatable

Dim dcVendorName as DataColumn
Dim dcVendorID as DataColumn
Dim dcAmount as DayaColumn

dcVendorName = New DataColumn
dcVendorName.ColumnName = "VendorName"
dcVendorName.DataType = System.Type.GetType("System.String")

dcVendorID = New DataColumn
dcVendorID .ColumnName = "VendorID "
dcVendorID .DataType = System.Type.GetType("System.Int32")

dcAmount = New DataColumn
dcAmount.ColumnName = "dcAmount"
dcAmount.DataType = System.Type.GetType("System.Double")

dtGroups.Columns.Add(dcVendorName)
dtGroups.Columns.Add(dcVendorID)
dtGroups.Columns.Add(dcAmount)

'make the dataview
Dim dvGroups As DataView

dvGroups= dtGroups.DefaultView

'bind dataview to datagrid
dgGroups.DataSource = dvGroups

Next, when users click the "commit" button, after the data are added to the "real" datatable (the one holding individual records) have the program do something like this:

dvGroups.Sort("VendorID")

Dim i as Integer = -1

'intCurrentVendorID is the VendorID for the current record being entered
i = dvGroups.Find(intCurrentVendorID)

Dim drv As DataRowView

If i >= 0 Then 'vendor is already in the table

'get the appropriate record
drv = dvGroups(i)

'update data in vendor's record
drv.Item("Amount") += ThisRecordAmount

Else 'vendor is not already in the table, so add it

drv = dvGroups.AddNew()

'add data in new vendor's record
drv.Item("VendorName") = ThisVendorName
drv.Item("VendorID") = ThisVendorID
drv.Item("Amount") += ThisRecordAmount

drv.EndEdit()

End If


This should provide you with a grouping of the vendors as records are added. You can tweak this to allow for records being deleted - basically just reverse what was done with dtGroups and dvGroups. If the value of a vendor's Amount drops to 0, remove them from the table.

This is kind of a lot of work for this, but I think it will be better than having to go to the database every time.

Let me know if you need any clarification, etc.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[RonRepp]

Thanks to both for your valuable insight.

jebenson,

I'll try your code when I get home. This is for a side job that I'm doing for a construction company, and the inventory portion is bogging me down.

Thanks again.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.