[RESOLVED][2005] DatagridView expand feature?

[super_nOOb]

Hey guys,
I was trying to find out if its possible to expand a row in Datagridview in order to view more information. For example i have a SQL

SELECT G6.Name, G6.AUM, G6.IHP,G6.NET_AUM, 
G6.ProdName, G6.Currency, G6.VAT, G6.mngmt_fee, G6.retrocession 
FROM ( Mysubqueries )As G6 ORDER BY G6.Name

And i get this output

NAME      AUM   IHP     NET_AUM   ProdName    Currency   VAT  mgt_fee  retr
APOLLO	198688	16240	182448      GDF_EUR	USD       no     1.5%     0
APOLLO	198688	16087	378126      GDF_EUR	USD       no     1.5%     0

And what i actually wanted is to have a single line with the comun columns (Name, currency,VAT, mgt_fee, retrocession) And to SUM the IHP to a single value. But then have a expand feture that shows the Prod_name and the IHP separate.

Is that possible? Or does anyone have a better idea on how i can show this stuff in one line?

Thx in advance for the help

EDIT: I found a few feature here: http://www.xmlfox.com/datagrid_datagridview.htm
which are combobox, date picker and so forth, but i still havent found any "expand" type

EDIT2: I wanted something like this

taken from: http://www.codeproject.com/useritems/CoolGrid.asp
But to expand only in the IHP column.

[super_nOOb]

i guess i should 1st start with a sum of the IHP and then try to figure the expand feature. I was running the query and tried to put in the select

SUM(G6.IHP)

but in my subquery i get the G6.IHP from this equation

(SUM(G1.MonthlyAvg*G2.Pos)/3)

And i dont know why i havent been able to sum them. I always get 2 separate value

[Ken B]

Look at the dataGridView Columns. You can set the width by assigning a value to its width.

If ShipName is column 2 then:

.Columns(2).HeaderText = Environment.NewLine & "Ship Name"
        .Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopLeft
        .Columns(2).Width = 240

[super_nOOb]

@ KEn B - Thx for the reply Ken, but what i was looking for is not to resize the column, but to manage a way that i could instead of returning 2 records of client apollo to show one where every column (but the IHP and prodname) would remain the same and that IHP would be the sum of the records. And then have a feature button that would expand inside of the datagridview to show all the separate records of IHP. I'll attach a picture of what i have



So basically i wanted to have only one column. And to show the prodname and IHP in a expande feature. And to have in place of the IHP a sum of them. But for some reason if i put SUM(G6.IHP) i still get 2 separate values

The value of AUM should be either one of them but not the sum of them. and net_aum is AUM minus HP. So if i fix the IHP that one would be fixed too.

[Ken B]

The dataGridView displays whats is returned by your sql statement.

Look at the sql GROUP BY statement. That should return one record for what the items you are grouping by.

Something like this:

SELECT G6.Name, SUM(G6.IHP) AS IHPSum 
FROM ( Mysubqueries )As G6 GROUP BY G6.Name

[super_nOOb]

@ Ken B - Thx for the reply. Actually i build my SQL to return this 2 values. I need the reference of product name(prodname) and currency in order to get the right values. So the output is exactally what i wrote in the SQL to be. My main question is then if i can group this 2 in one and then have a expand feature (like the + - in in the picture of the 1st post) in a way that when + it shows me the sum of IHP, and when - it shows me IHP and ProdName separated.
So the issue was if i can group this and have the expand feature in VB.NET (datagridview) or if anyone had a better idea on still have only one column per client and still manage to show the products (that can go from 1 to infinit).

As far as the SUM(IHP) i tried to do it and group by IHP or something but still couldnt manage it........i'll try some dif things here to see what i can come up with

EDIT: for example, if i do this

SELECT G6.Name, SUM(G6.IHP), G6.Currency, G6.VAT, G6.mngmt_fee, G6.retrocession 
FROM (mysubqueries))As G6 
GROUP BY G6.Name, G6.IHP, G6.Currency, G6.VAT, G6.mngmt_fee, G6.retrocession

I still get 2 lines instead of one.

[Ken B]

Check out http://www.codeproject.com/cs/miscctrl/OutlookGrid.asp

[super_nOOb]

thx for the link, but i guess this feature is not quiet what i needed. I have another example using the same thing in the 1st post. I tried to draw what i was looking for

without expand


with expand. Please assume that the IHP on this one is the SUM of the 2

[super_nOOb]

KEN B great tip on the GROUP BY
the Group by name worked
as follows

SELECT G6.Name,G6.AUM, SUM(G6.IHP)AS IHP, 
G6.AUM-ISNULL(SUM(G6.IHP), 0) AS NET_AUM, G6.Currency, G6.VAT, 
G6.mngmt_fee, G6.retrocession FROM (mysubqueries)As G6 GROUP BY 
G6.Name,G6.AUM, G6.Currency, G6.VAT, G6.mngmt_fee, G6.retrocession

I rated your post

Now just have to think on how to show the diferent IHP per product
I have no clue what to do with those missing data. maybe a way out would be making a query that would return me the 4 values i need (2 prodname, 2 IHP). But i a way that i could maybe run the query by expanding the (+,-) thing. I actually dont know what else i could do

[Ken B]

What might work is to have a list box on the right of your form. When the user clicks on the IHP cell, the list displays detail the information.

Look at http://www.code-magazine.com/article...printmode=true figure #26 for something like this. While the example uses a listbox, it should work on a datagridview.

[super_nOOb]

@ Ken B - Thx for the help and for the link. I'll take a detailed look at it. It might not be the best and most elegant way out for me, but if i cannot manage any other one this might do the job

EDIT: Maybe if VB.NET haf onrollover function i could try something on that matter too

[super_nOOb]

I was thinking and had an idea.....is there anyway i can place a treeview inside of a datagridview. Because if yes i could make the treeview by client and than have the expand feature..........

[super_nOOb]

i've been researching and reasearching anf the best thing i found was some tips for ASP.NET in C# for datagrid

s it possible to display parent rows and chind rows together in
datagrid in c#?

currently it is displaying link to child rows. as i click on the link
parent row is displyed in header.
I want it like, parent row then its child
+Parent1
    Child1
    Child2
+Parent2
    Child1
    Child2
Is this possible in C#?

And the reply

Yes this is possible.

You can implement this either using DataGrid or using DataList in
DataGrid,

1) If you are using datagrid then u need to write code for hiding
in item_databound
2) If you are using datalist u can do it with the help of
javascript, and item_databound. You bound the datagrid in datalist
item_databound and make the datagrid invisible by javascript as
"style=display:none". We user clicks on parent1 we need to change the
state as "style=display:block". Before this u need to attach client side
event to "parent1" like e.item.attributes.add("style","ur javascript
function which implements show/hide functionality");

I didnt find anything that looked like this aplicable to VB.NET. Is it possible in VB in a dtagrid view too?

EDIT
__________________________________________________

I found an example in probably another programing language:



Is it possible that .NET dosent have anything similar?

[super_nOOb]

ok i found what i was looking for. But its in C# >.< . Why dont everyone use Vb
link: http://www.ondotnet.com/pub/a/dotnet...html?page=last
without expand

on expand


C# code

// Connect to the database
SqlConnection cnn = new SqlConnection();
cnn.ConnectionString = "Data Source=\"(local)\";" +
    "Initial Catalog=Northwind;Integrated Security=SSPI";
 
// Set up the DataSet to hold all the data
DataSet dsMain = new DataSet();
 
// Load customers
SqlCommand cmdCustomers = cnn.CreateCommand();
cmdCustomers.CommandType = System.Data.CommandType.Text;
cmdCustomers.CommandText="SELECT * FROM Customers";
SqlDataAdapter daCustomers = new SqlDataAdapter();
daCustomers.SelectCommand = cmdCustomers;
daCustomers.Fill(dsMain, "Customers");
 
// Load orders
SqlCommand cmdOrders = cnn.CreateCommand();
cmdOrders.CommandType = System.Data.CommandType.Text;
cmdOrders.CommandText="SELECT * FROM Orders";
SqlDataAdapter daOrders = new SqlDataAdapter();
daOrders.SelectCommand = cmdOrders;
daOrders.Fill(dsMain, "Orders");
 
// Load order details
SqlCommand cmdOrderDetails = cnn.CreateCommand();
cmdOrderDetails.CommandType = System.Data.CommandType.Text;
cmdOrderDetails.CommandText="SELECT * FROM [Order Details]";
SqlDataAdapter daOrderDetails = new SqlDataAdapter();
daOrderDetails.SelectCommand = cmdOrderDetails;
daOrderDetails.Fill(dsMain, "OrderDetails");
 
// Relate tables
 
dsMain.Relations.Add(new DataRelation("relCustOrders", 
    dsMain.Tables["Customers"].Columns["CustomerID"], 
    dsMain.Tables["Orders"].Columns["CustomerID"]));
dsMain.Relations.Add(new DataRelation("relOrdersDetails", 
    dsMain.Tables["Orders"].Columns["OrderID"], 
    dsMain.Tables["OrderDetails"].Columns["OrderID"]));
 
// Calculations will go here
 
// Bind data to the user interface
dgMain.DataSource = dsMain;
dgMain.DataMember = "Customers";

If anyone can help me figure this out on VB id be very grateful

[Ken B]

Take a look at this: http://msdn2.microsoft.com/en-us/library/zkatshfa.aspx

[super_nOOb]

@ Ken B - Oh that is awesome. I putted the code in and now i get a column with buttons. I saw also that

To respond to button clicks, implement a 
System.Windows.Forms.DataGridView.CellClick event handler that displays a 
form containing the child table.

I was checking the code for that

Dim instance As DataGridView
Dim handler As DataGridViewCellEventHandler
 
AddHandler instance.CellClick, handler

so basically my SQL code for the child table should be the handler?

[Ken B]

The c code you have is for a datagrid. From the link.

To demonstrate calculated DataColumns in action, I'll build a very simple C# Windows Forms application. The user interface consists entirely of a DataGrid control named dgMain. If you'd like to follow along, start by putting this code into the form's Load event handler:

I do not believe that you can recreate this with a datagridview.

The good news is that you can create a better interface with the button you just added. When the button is click, you can either display a new form displaying the detail information, or display a panel with the information. A new form will look better.

First thing you need to do is to get the parent ID. In one of my program, I have a varaible called selectedId decleared on the form. Everytime I enter a cell I assign the id of that record to it using

Private Sub dgvResults_CellEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvResults.CellEnter
 
    'Get the selected row from the grid if there is one.
    If Me.dgvResults.CurrentRow Is Nothing Then
      Exit Sub
    End If
 
    Dim currentRow As DataGridViewRow = TryCast(Me.dgvResults.CurrentRow, DataGridViewRow)
 
    Dim selectedRow As DataRowView = DirectCast(currentRow.DataBoundItem, DataRowView)
    selectedId = CInt(selectedRow("Id"))
 
  End Sub

When the user click on a cell, I think you can see if its the button with the following:

Private Sub dgvResults_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvResults.CellClick
 
    MessageBox.Show(dgvResults.Columns(e.ColumnIndex).Name)
 
if dgvResults.Columns(e.ColumnIndex).Name = "what ever you named your button" then
  call displayDetail(selectedId)
endif
 
  End Sub

[super_nOOb]

@ Ken B - Many thanks for the reply. Yeah i saw now the C# sharp code and it is indeed for datagrid, which would not work for me!
I have a few doubts: 1st one is that the code to create the buttons, which is:

Dim buttonColumn As New DataGridViewButtonColumn
            With buttonColumn
                .HeaderText = "Details"
                .Name = "Details"
                .Text = "View Details"
                ' Use the Text property for the button text for all cells rather
                ' than using each cell's value as the text for its own button.
                .UseColumnTextForButtonValue = True
            End With
            ' Add the button column to the control.
            dgvAverage.Columns.Insert(1, buttonColumn)

is working fine but has still one bug. Whever i run the code for the datagrid view it creates the buttons. But if i want to, for some reason, run it again it will create another column (where i would have 2 now). And it will keep creating as songs as i run my form. I thought about making something like

Dim isfirsttime As boolean = true

and then running a if where from the 2nd time i run on the isfirsttime =false, and then a new column would not be created, but this will not work since every time i run it it will have to go through my =true and then isfristtime will be true again and then it will still recreate a new column.

Issue 2 is about the codes you gave me. Should i place them in a new form or should i simply place a private sub inside of my main form? what i did was to place the code inside my main form the code that follows

Private Sub dgvAverage_CellEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvAverage.CellEnter
        'Get the selected row from the grid if there is one.
        If Me.dgvAverage.CurrentRow Is Nothing Then
            Exit Sub
        End If
        Dim currentRow As DataGridViewRow = TryCast(Me.dgvAverage.CurrentRow, DataGridViewRow)
        Dim selectedRow As DataRowView = DirectCast(currentRow.DataBoundItem, DataRowView)
        Dim selectedId As String = CInt(selectedRow("Id"))
    End Sub
    Private Sub dgvAverage_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvAverage.CellClick
 
        MessageBox.Show(dgvAverage.Columns(e.ColumnIndex).Name)
 
        If dgvAverage.Columns(e.ColumnIndex).Name = "Details" Then
            Call displayDetail(selectedId)
        End If
 
    End Sub

I had to declare the selectedId As String in the 1st sub. I sitll am getting 2 issues on the second Sub. I get a error msg that displayDetail is not declared. What should i declare it like? And also i get the same msg for the Selectedid which is funny cuz the sub just above it gets the selectid values. Should i then make the 1st sub as a public one?
And now last but not least, you said that i should show the result from the on button click handler in a new form. For that should then add a private sub to hadle the on button click and then put a code to open the reffered new form there right?

many thx for all the help. Im very grateful for that

[Ken B]

1) You should only run the code to create the button once. It should be done in the code that populate the datagridview.

Do you even need a button? In my application, If the user double clicks on a row, I display the detail screen for that row. If the user right click on a row, I display a popup menu.

2) The code should be in the form that contains the datagridview.

selectedId need to be declared at the form level, not in any sub.

3) You can have the code to display your detail form in the button click event. I would have it in a seperate procedure, that is called by the button, since you might what to use it somewhere else in your program.

[super_nOOb]

@ Ken B - Thx again.
1) As far as this issue, my datagridview is populated when a ok button is clicked since i need the user to choose a few parameters before. So basically if the user choses a certain set of parameters and runs the ok button one column is created. If he runs it for a dif paramenter then an aditional one is being created.

Your idea might be better, to set the user to click on the row. But i think it should have a clear indication for him to do that, otherwise whoever is using it might not know about this feature. Did you make any layout to make that clear?

2) ok. So that i did correct. Ill take the sub off the selectedid. Well basically what i did here was to place the code that gets the selectedID inside the form load. And i think its still not it. What i have here is a form load event sub, form close event sub, and the button handler for the population of the datagridview, and then i have a few classes to orginize the columns of the datagridview. Where exactually should i place the

Dim currentRow As DataGridViewRow = TryCast(Me.dgvAverage.CurrentRow, DataGridViewRow)
        Dim selectedRow As DataRowView = DirectCast(currentRow.DataBoundItem, DataRowView)
        Dim selectedId As String = CInt(selectedRow("Id"))

If i put inside of the Public class of the form i think it might work, but i still get the

Error	1	Name 'displayDetail' is not declared.

3)So it might be good maybe to make a class for it?

[Ken B]

Time to start over. You are trying to do to much at once. Remove all the new code I gave you except for the button display stuff.

Is the button being displayed on each row? And was the problem of the second button being displayed take care of?

If yes, add the follwoing to the button click.

Dim currentRow As DataGridViewRow = TryCast(Me.dgvAverage.CurrentRow, DataGridViewRow)
        Dim selectedRow As DataRowView = DirectCast(currentRow.DataBoundItem, DataRowView)
        messageBox.show(Int(selectedRow("Id")))

Run the program and click on the button for a few rows. Did the right id for the row in a message box each time you click on the a button?

[super_nOOb]

oh ok...sorry about that...........
I'll try to detail how it is working. the code to create the button which is

Dim buttonColumn As New DataGridViewButtonColumn
            With buttonColumn
                .HeaderText = "Details"
                .Name = "Details"
                .Text = "View Details"
                ' Use the Text property for the button text for all cells rather
                ' than using each cell's value as the text for its own button.
                .UseColumnTextForButtonValue = True
            End With
            ' Add the button column to the control.
            dgvAverage.Columns.Insert(1, buttonColumn)

is creating one button for each of the rows i have in the datagridview when i run the ok button for the 1st time. If i run the ok button again, my datagridview will still keep the 1st set of buttons i created and will add as a last column another column with one button for each row. So now i have 2 columns where each row of each column with a button.

And when i place your code

Dim currentRow As DataGridViewRow = TryCast(Me.dgvAverage.CurrentRow, DataGridViewRow)
        Dim selectedRow As DataRowView = DirectCast(currentRow.DataBoundItem, DataRowView)
        messageBox.show(Int(selectedRow("Id")))

inside of my okbutton to populate the datagridview i get one error msg which is

Unable to cast object of type 'Average' to type 'System.Data.DataRowView'.

And 'Average' is actually the name of the class i use to organize my columns which works like bellow

Private Class Average
        Private _name As String
       Public Property NAME() As String
            Get
                Return _name
            End Get
            Set(ByVal value As String)
                _name = value
            End Set
        End Property
End Class

[Ken B]

My 100th post

Lets fix the duplicate button problem first.

I'm confused about your OK button. Why would you click on it twice?

[super_nOOb]

cool
Congrats for the 100th

Well about the ok button.....My form is a calculation based on the client name and a few date parameters. So lets say i run the form. I select on the combobox client1 and dates x and y for example and then press the okbutton and see the result. Then i want to do the same thing either for another client or to make the calculation for the same client but for diferent dates. At that point i change the parameters and then press ok again to get the results. And it is here that the extra column is showing up (2 times).

[Ken B]

id theform with the datagridview, the same form with the ok button, or is it on a different form?

[super_nOOb]

its all in the same form....the datagridview population is inside the button all in the same form.

[Ken B]

Ok, thats why your getting a new button each time you click on the OK button.

You should be able to solve that by moving the button creation code to the form load. Let me know.

[super_nOOb]

actually it did not work. When the form loads i only populate the controls and not the datagridview. So basically the datagridview is populated only on buttonok click event.

[Ken B]

Then you will have to use a flag.

In the form Public Class section"

Private blnButtonLoaded as boolean = false

On OK button click

If not blnButtonLoaded then
Dim buttonColumn As New DataGridViewButtonColumn
With buttonColumn
.HeaderText = "Details"
.Name = "Details"
.Text = "View Details"
' Use the Text property for the button text for all cells rather
' than using each cell's value as the text for its own button.
.UseColumnTextForButtonValue = True
End With
' Add the button column to the control.
dgvAverage.Columns.Insert(1, buttonColumn)
blnButtonLoaded =tr

[Ken B]

Posted to quickly.

Then you will have to use a flag.

In the form Public Class section"

Private blnButtonLoaded as boolean = false

On OK button click

If not blnButtonLoaded then
  Dim buttonColumn As New DataGridViewButtonColumn
  With buttonColumn
    .HeaderText = "Details"
    .Name = "Details"
    .Text = "View Details"
    ' Use the Text property for the button text for all cells rather
    ' than using each cell's value as the text for its own button.
    .UseColumnTextForButtonValue = True
   End With
   ' Add the button column to the control.
   dgvAverage.Columns.Insert(1, buttonColumn)
   blnButtonLoaded =true
endif

[super_nOOb]

oh!!! That works perfect. Creates only one column........

[super_nOOb]

by the way.......did u have any idea on the id issue?

[Ken B]

Next step:

Capture the button click event

Private Sub dgvResults_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvResults.CellClick
 
    If e.ColumnIndex = 0 Then MessageBox.Show("Hi, you click me")
 
  End Sub

Click on each column to make sure only the button displaye the message.

[super_nOOb]

ok. said and done. Works perfect

[Ken B]

Now to get the id.

Private Sub dgvResults_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvResults.CellClick
 
    Dim currentRow As DataGridViewRow = TryCast(Me.dgvResults.CurrentRow, DataGridViewRow)
    Dim selectedRow As DataRowView = DirectCast(currentRow.DataBoundItem, DataRowView)
 
    If e.ColumnIndex = 0 Then MessageBox.Show(selectedRow(0))
 
  End Sub

The datagridview I tested this on had the following columns, button, id, description. When I used selectedRow(1), the description was displayed. I changed it to zero and the id was displayed. I guess that the button is not treated as a column in this case.

So your id should be returned with selectedRow(0), with zero being replaced withe the actual column number containing the id.

[super_nOOb]

ok. understood the (0)
I run the code and still get the same error i was getting before

Unable to cast object of type 'Average' to type 'System.Data.DataRowView'.

And 'Average' is actually the name of the class i use to organize my columns which works like bellow

Private Class Average
        Private _name As String
       Public Property NAME() As String
            Get
                Return _name
            End Get
            Set(ByVal value As String)
                _name = value
            End Set
        End Property
End Class

The error usually occur in this part of the code

Dim selectedRow As DataRowView = DirectCast(currentRow.DataBoundItem, DataRowView)

[Ken B]

There is already a vb method called Average. This might be your problem. Rename it and try it.

[super_nOOb]

i renamed it to SambaTriste and still got the error

Unable to cast object of type 'SambaTriste' to type 'System.Data.DataRowView'.

[Ken B]

Post your code for the cellclick.

[super_nOOb]

Private Sub dgvAverage_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvAverage.CellClick
 
        Dim currentRow As DataGridViewRow = TryCast(Me.dgvAverage.CurrentRow, DataGridViewRow)
        Dim selectedRow As DataRowView = DirectCast(currentRow.DataBoundItem, DataRowView)
 
        If e.ColumnIndex = 0 Then MessageBox.Show(selectedRow(0))
 
    End Sub