[RESOLVED][2005] DatagridView expand feature?

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

Code:

---

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

Code:

---


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
http://www.codeproject.com/useritems...d/CoolGrid.JPG
taken from: http://www.codeproject.com/useritems/CoolGrid.asp
But to expand only in the IHP column.

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

Code:

---

SUM(G6.IHP)

---

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

Code:

---

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

---

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

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

If ShipName is column 2 then:

VB Code:

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

@ 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

http://img.photobucket.com/albums/v2...l/untitled.jpg

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

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.

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:

VB Code:

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

@ 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

Code:

---

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.

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

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
http://img.photobucket.com/albums/v2...caototal/1.jpg

with expand. Please assume that the IHP on this one is the SUM of the 2
http://img.photobucket.com/albums/v2...tal/expand.jpg

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

Code:

---

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

Now just have to think on how to show the diferent IHP per product :confused:
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

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.

@ 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 :D

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

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

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

Code:

---

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

Code:

---

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:

http://img.photobucket.com/albums/v2...untitled-1.jpg

Is it possible that .NET dosent have anything similar?

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
http://www.ondotnet.com/dotnet/2003/...cs/Figure1.jpg
on expand
http://www.ondotnet.com/dotnet/2003/...cs/Figure2.jpg

C# code

VB 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 :D

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

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

Code:

---

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

VB Code:

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?

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

Quote:

---

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

VB Code:

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:

VB Code:

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

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

VB Code:

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

VB Code:

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

VB Code:

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

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.

@ 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

VB Code:

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

Code:

---

Error        1        Name 'displayDetail' is not declared.

---

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

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.

VB Code:

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?

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

VB Code:

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

VB 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

Code:

---

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

VB Code:

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

:) My 100th post :)

Lets fix the duplicate button problem first.

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

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

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

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

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.

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.

Then you will have to use a flag.

In the form Public Class section"

VB Code:

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

Posted to quickly.

Then you will have to use a flag.

In the form Public Class section"

VB Code:

Private blnButtonLoaded as boolean = false

On OK button click

VB Code:

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

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

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

Next step:

Capture the button click event

VB Code:

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.

ok. said and done. Works perfect

Now to get the id.

VB Code:

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.

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

Code:

---

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

VB Code:

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

VB Code:

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

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

i renamed it to SambaTriste and still got the error

Code:

---

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

---

Post your code for the cellclick.

VB Code:

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

Your dataGridView is populated by code, not a dataTable, right?

That would explain your error and why the button was not counted as a column.

Try:

VB Code:

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)
     If e.ColumnIndex = 0 Then MessageBox.Show(dgvResults.CurrentRow.Cells(1).Value.ToString)
 
  End Sub

yes...that indeed. I populate it with a SQL. The code works great!. Whenever i click on the button it shows me the id(which in my case is the client name) in a msg box

Can you display the code you use to populate the datagridview.

VB Code:

strSql = "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 (" & vbCrLf
                For i = 0 To currencies.Count - 1
                    Dim curr As String = currencies(i)
                    strSql &= "SELECT G3.Name, G3.Average AS AUM, ISNULL(G4.Multiplication, 0)AS IHP, G4.ProdName, G3.Currency, G3.VAT, G3.mngmt_fee, G3.retrocession " & vbCrLf
                    strSql &= "FROM (SELECT cd_clients.Name, AVG(client_data.val) AS Average, cd_clients.Currency, cd_clients.VAT, cd_clients.mngmt_fee, cd_clients.Retrocession " & vbCrLf
                    strSql &= "FROM cd_clients INNER JOIN client_data ON cd_clients.id = client_data.cd_clientID where client_data.valDate Between '" & mindate & "' And '" & maxdate & "' " & vbCrLf
                    strSql &= "GROUP BY cd_clients.Name,cd_clients.Currency,cd_clients.VAT,cd_clients.mngmt_fee, cd_clients.Retrocession) AS G3 " & vbCrLf
                    strSql &= "LEFT JOIN (SELECT G2.Name, G1.ProdName, (SUM(G1.MonthlyAvg*G2.Pos)/3) AS Multiplication " & vbCrLf
                    strSql &= "FROM(SELECT cd_prod.name AS ProdName, AVG(prod_data." & curr & ") AS MonthlyAVG, MONTH(prod_data.valdate) AS MonthPart " & vbCrLf
                    strSql &= "FROM cd_prod INNER JOIN prod_data ON cd_prod.id = prod_data.cd_prodID " & vbCrLf
                    strSql &= "WHERE YEAR(prod_data.valdate) = '" & cbYear.Text & "' AND MONTH(prod_data.valdate) BETWEEN " & mone & " AND " & mthree & " " & vbCrLf
                    strSql &= "GROUP BY MONTH(prod_data.valdate), cd_prod.name) AS G1 " & vbCrLf
                    strSql &= "LEFT JOIN (SELECT cd_clients.Name, pos_data.product as ProdName, SUM(cast(pos_data.Shares as Money)) AS POS, MONTH('" & dateone & "') AS MonthPart " & vbCrLf
                    strSql &= "FROM pos_data RIGHT JOIN cd_clients on pos_data.reference = cd_clients.Name " & vbCrLf
                    strSql &= "WHERE pos_data.period BETWEEN '12/31/2001' AND '" & dateone & "' " & vbCrLf
                    strSql &= "GROUP BY pos_data.reference, pos_data.product, cd_clients.Name " & vbCrLf
                    strSql &= "UNION ALL " & vbCrLf
                    strSql &= "SELECT cd_clients.Name, pos_data.product AS ProdName, SUM(cast(pos_data.Shares as Money)) AS POS, MONTH('" & datetwo & "') AS MonthPart " & vbCrLf
                    strSql &= "FROM pos_data RIGHT JOIN cd_clients on pos_data.reference = cd_clients.Name " & vbCrLf
                    strSql &= "WHERE pos_data.period BETWEEN '12/31/2001' AND '" & datetwo & "' " & vbCrLf
                    strSql &= "GROUP BY pos_data.reference, pos_data.product, cd_clients.Name " & vbCrLf
                    strSql &= "UNION ALL " & vbCrLf
                    strSql &= "SELECT cd_clients.Name, pos_data.product AS ProdName, SUM(cast(pos_data.Shares as Money)) AS POS, MONTH('" & maxdate & "') AS MonthPart " & vbCrLf
                    strSql &= "FROM pos_data RIGHT JOIN cd_clients on pos_data.reference = cd_clients.Name " & vbCrLf
                    strSql &= "WHERE pos_data.period BETWEEN '12/31/2001' And '" & maxdate & "' " & vbCrLf
                    strSql &= "GROUP BY pos_data.reference, pos_data.product, cd_clients.Name) AS G2 on G2.ProdName = G1.ProdName AND G2.MonthPart = G1.MonthPart " & vbCrLf
                    strSql &= "GROUP BY G1.ProdName, G2.Name) AS G4 on G3.Name = G4.Name "
                    strSql &= "INNER JOIN (select Column_name from information_schema.columns where Column_name ='" & curr & "') As G5 on Column_name = G3.Currency" & vbCrLf
                    If i = currencies.Count - 1 Then
                        IsFirstSubquery = False
                    Else
                        strSql &= "UNION ALL " & vbCrLf
                    End If
                Next i
                strSql &= ")As G6 GROUP BY G6.Name,G6.AUM, G6.Currency, G6.VAT, G6.mngmt_fee, G6.retrocession ORDER BY G6.Name"

Wow, :ehh: :ehh: :ehh: :ehh: :ehh:

I'm interested in the population of the datagridview

VB Code:

Dim QueryResult As New ArrayList
            While dr.Read
                Dim dummy As New Average
                dummy.NAME = dr("name")
                dummy.Currency = dr("Currency")
                dummy.VAT = dr("VAT")
                dummy.Flat_fee = dr("mngmt_fee")
                dummy.Retrocession = dr("Retrocession")
                If Not IsDBNull(dr("ihp")) Then
                    dummy.IHP = dr("ihp")
                Else
                    dummy.IHP = "0"
                End If
                If Not IsDBNull(dr("net_aum")) Then
                    dummy.NET_AUM = dr("net_aum")
                Else
                    dummy.NET_AUM = "0"
                End If
 
                If Not IsDBNull(dr("aum")) Then
                    dummy.AUM = dr("aum")
                Else
                    dummy.AUM = "0"
                End If
                QueryResult.Add(dummy)
            End While
            dgvAverage.DataSource = QueryResult

and they are asign to the Average class i told you earlier

Time to resolve this thread. :) :o :D ;) :p :rolleyes: :wave: :lol: :thumb: :afrog:

heheh, yup :afrog: :thumb: :wave: :D :p
I hope we can make it!!! :thumb:

good morning Ken B
Just a question left: Should i make the other form pop up on the button click? As far as design i think i should make a small form with a datagridview inside right? And last but not least, can i "save" the msgbox value to place it inside of my SQL code in the other form?

thx for all the help

EDIT:
Well i created a new form, but i forgot one detail: The SQL i run needs the inputs from the main form. So if i run a new SQL in the new form i would still need the inputs from the other form.
Unless i run the SQL in the main form, then make an array of its values, and then try to populate the other form with the array i made (if its possible) :confused:
And yet another issue. If i try to run a new SQL when the button in the cell is clicked i would need it to have the same input values of the previous SQL. Because a situation like this can happen: The user choses a few parameters and press ok (the datagridview is populated). Then he decide to change a few parameters. But he gives up on the new parameters and still want to use what is in the datagrid. If he presses the cell button now the new SQL will have dif parameters from the datagridview :cry:

And also from the 2nd time on, when i run the ok button the ok column button dosent work. It is working only on the 1st time

EDIT 2:
I found a master/detail code for C# for datagridview here:
http://www.dotnetmonitor.com/DataGri...-details-.html

tell me what you think about this one please

EDIT 3: look what i found
http://www.applicationaspect.com/Sit...ailsImage.aspx
This is what i want.
from here http://www.applicationaspect.com/Sit.../Overview.aspx
I think they dont give us the code

EDIT 4: Im gonna check this one
http://blogs.msdn.com/markrideout/ar...08/510700.aspx

EDIT 5:
I actually just donwloaded a program that lets us see the code, and it runs a hierarchy master/detail think
the website i got it from is: http://www.cnet.com.au/downloads/0,2...573865s,00.htm

And the whole code of the HierarchyNot project is as follows

VB Code:

Private dt As DataTable
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        InitForm()
    End Sub
 
    Private Sub InitForm()
        Dim gdParent As Guid
 
        dt = New DataTable()
 
        FillTableDefinition()
 
        gdParent = FillTable(System.DBNull.Value, "North America", "Continent")
        FillTable(gdParent, "Canada", "Country")
        gdParent = FillTable(gdParent, "USA", "Country")
        FillTable(gdParent, "Nevada", "State")
        FillTable(gdParent, "Ohio", "State")
 
        gdParent = FillTable(System.DBNull.Value, "Europe", "Continent")
        FillTable(gdParent, "Spain", "Country")
        FillTable(gdParent, "France", "Country")
 
        gdParent = FillTable(System.DBNull.Value, "South America", "Continent")
        FillTable(gdParent, "Brasil", "Country")
        FillTable(gdParent, "Chile", "Country")
 
        dt.DefaultView.Sort = "name"
 
        InitHierarchy()
 
    End Sub
 
    Private Sub InitHierarchy()
        hierarchicalDataGridView1.PrimaryKeyColumnName = "id"
        hierarchicalDataGridView1.HierarchicalColumnName = "id_parent"
        hierarchicalDataGridView1.RootValue = System.DBNull.Value
        hierarchicalDataGridView1.DataSource = dt
    End Sub
 
    Private Function FillTable(ByVal gd As Object, ByVal Name As String, ByVal Description As String) As Guid
        Dim dr As DataRow
        Dim gdc As Guid
 
        dr = dt.NewRow()
        gdc = Guid.NewGuid
        dr("id") = gdc
        dr("name") = Name
        dr("description") = Description
        dr("id_parent") = gd
        dt.Rows.Add(dr)
 
        Return gdc
    End Function
 
    Private Sub FillTableDefinition()
        Dim dc As DataColumn
 
        dc = New DataColumn()
        dc.DataType = System.Type.GetType("System.Guid")
        dc.ColumnName = "id"
        dt.Columns.Add(dc)
 
        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.String")
        dc.ColumnName = "name"
        dt.Columns.Add(dc)
 
        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.String")
        dc.ColumnName = "description"
        dt.Columns.Add(dc)
 
        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.Guid")
        dc.ColumnName = "id_parent"
        dt.Columns.Add(dc)
    End Sub
End Class

Now ill try to find out what to do with this in my code. This guy is the one!!!!! But aparently it fills the datatable with values he wrtoe in the InitForm(). Mine would use that crazy SQL i posted earlier.

And this is a screenshot of it:
http://img.photobucket.com/albums/v2...untitled-2.jpg

Maybe you could give some heads up, cuz i really dont know where to start, even though its dosent look too complicated.

http://img.photobucket.com/albums/v2...untitled-3.jpg
here is another screen shot. Usa is hierarchal inside of North america

My main issue here so far is:
You can see that the datagridview is populated with the values from InitForm() in the order of FillTable() which is a Private Function. So, with a SQL how could i make my population is this conditions?

And second You will see that each master and detail are wrote separetely. In my case the big (or whole) population is made by one single SQL. And then each detail would have to be linked seperatly as a function of the client name but still maitaining the parameters set for the 1st population. I can manage to get the data of the detail in one SQL too, but the id have get from them all only the one that the client has, in a way that when i expand the + button the detail showed would be only of the refereced client

And in my case i get this with my SQL

Code:

---

        NAME        AUM        IHP        NET_AUM          Currency        Flat_fee        VAT        Retrocession
        APOLLO        198,688        171,753        26,936          USD                  1.50%        No        0.00%

---

And i want to use another SQL to get the child data (details) that would be

Code:

---

NAME      Prod          IHP
APOLLO        GDF_EUR              10876.90
APOLLO        GDF_USD              160875.64

---

So basically if there was a way to bind by name. Because basically with this 2 SQL's i get all clients and not only one as showed

EDIT: I found those 2 sources but dont know if it helps
http://support.microsoft.com/kb/248915
http://blog.devstone.com/aaron/archi...09/28/282.aspx


Ok. I found something to try to work with

VB Code:

Imports System.Data
Imports System.Data.SqlClient
 
Public Class Form1
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        RefreshData()
    End Sub
 
    Public Sub RefreshData()
        Dim cn As SqlConnection = New SqlConnection(My.MySettings.Default.dsn.ToString())
        Dim cmd As SqlCommand
        Dim da As SqlDataAdapter
        Dim dt As DataTable = New DataTable()
        Dim sSQLStmt As String
 
        cn.Open()
        sSQLStmt = "SELECT * FROM GeographyEditableComplex"
        cmd = New SqlCommand(sSQLStmt, cn)
        da = New SqlDataAdapter(cmd)
        da.Fill(dt)
 
        dt.DefaultView.Sort = "name"
        InitHierarchy(dt)
    End Sub
 
    Private Sub InitHierarchy(ByVal dt As DataTable)
        hierarchicalDataGridView1.PrimaryKeyColumnName = "id"
        hierarchicalDataGridView1.HierarchicalColumnName = "parent_id"
        hierarchicalDataGridView1.RootValue = 0
        hierarchicalDataGridView1.DataSource = dt
    End Sub
 
 
    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        Dim frm As New Form2
        Dim p As Integer
 
        If hierarchicalDataGridView1.SelectedRows.Count = 1 Then
            p = hierarchicalDataGridView1.SelectedRows(0).Cells("id").Value
        Else
            p = 0
        End If
 
        frm.Id = 0
        frm.ParentId = p
        frm.ShowDialog(Me)
        hierarchicalDataGridView1.SelectRow(frm.Id)
    End Sub
 
    Private Sub btnChange_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnChange.Click
        Dim frm As Form2 = New Form2()
        Dim p As Integer
 
        If hierarchicalDataGridView1.SelectedRows.Count = 1 Then
            p = hierarchicalDataGridView1.SelectedRows(0).Cells("id").Value
 
            frm.Id = p
            frm.ShowDialog(Me)
            hierarchicalDataGridView1.SelectRow(frm.Id)
        Else
            MsgBox(My.Resources.SelectRowMessage)
        End If
    End Sub
 
    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        If hierarchicalDataGridView1.SelectedRows.Count = 1 Then
            Dim p As Integer
            Dim p_del As Integer
            Dim dr As DialogResult
            Dim dt As DataTable
            Dim rw As DataRow
            Dim r As DataRow
 
            p = hierarchicalDataGridView1.SelectedRows(0).Cells("id").Value
 
            Dim b As Boolean = hierarchicalDataGridView1.IsParent(p)
            If b Then
                dr = MessageBox.Show(My.Resources.DeleteAllRowsMessage, My.Resources.DeleteActionMessage, MessageBoxButtons.YesNo)
                If dr = Windows.Forms.DialogResult.Yes Then
                    dt = hierarchicalDataGridView1.GetAllChildRows(p)
                    For Each rw In dt.Rows
                        DeleteRow(Convert.ToInt32(rw("id").ToString()))
                    Next
                    r = hierarchicalDataGridView1.GetParentRow(p)
                    p_del = Convert.ToInt32(r("id").ToString())
                    DeleteRow(p)
                    hierarchicalDataGridView1.SelectRow(r("id"))
                End If
            Else
                r = hierarchicalDataGridView1.GetParentRow(p)
                p_del = Convert.ToInt32(r("id").ToString())
                DeleteRow(p)
                hierarchicalDataGridView1.SelectRow(r("id"))
            End If
            else
            MessageBox.Show(My.Resources.SelectRowMessage)
        End If
    End Sub
 
    Private Sub DeleteRow(ByVal p As Integer)
        Dim sSQLStmt As String
        Dim cmd As SqlCommand
        Dim cn As SqlConnection = New SqlConnection(My.MySettings.Default.dsn.ToString())
 
        Try
            cn.Open()
            sSQLStmt = "DELETE FROM GeographyEditableComplex WHERE id=" & p
            cmd = New SqlCommand(sSQLStmt, cn)
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show("Error: " & ex.ToString())
        End Try
        cn.Close()
        RefreshData()
    End Sub
 
    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub
End Class

i guess i wont need the add, change or delete subs but i still dont know if the code is right

What database are you using?

Microsoft SQL server express

I think you should be looking at stored procedures to get your data. Any chance you can send me a copy of your database?

i see...well i could send to you. So far it is just a sample database while i code it. Do you want the vb.net files + the sql database ones? or only the .net ones?
I have a sample database in access too(in case you want to see how my tables are orinized, and that might be easier to send to you)

See if you can attach the access db for now. Compact it first to decrease the file size.

ok
there u go
EDIT OUT

I think its time to close this thread. We are getting off its subject and its to long.

I sent you private message.

ok...ill close it then. Btw i just saw the PM...Thx for the help