-
[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.
-
Re: [2005] DatagridView expand feature?
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 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
-
Re: [2005] DatagridView expand feature?
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
-
Re: [2005] DatagridView expand feature?
@ 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.
-
Re: [2005] DatagridView expand feature?
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
-
Re: [2005] DatagridView expand feature?
@ 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.
-
Re: [2005] DatagridView expand feature?
-
Re: [2005] DatagridView expand feature?
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
-
Re: [2005] DatagridView expand feature?
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
-
Re: [2005] DatagridView expand feature?
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.
-
Re: [2005] DatagridView expand feature?
@ 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
-
Re: [2005] DatagridView expand feature?
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..........
-
Re: [2005] DatagridView 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?
-
Re: [2005] DatagridView expand feature?
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
-
Re: [2005] DatagridView expand feature?
-
Re: [2005] DatagridView expand feature?
@ 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?
-
Re: [2005] DatagridView expand feature?
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
-
Re: [2005] DatagridView expand feature?
@ 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
-
Re: [2005] DatagridView expand feature?
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.
-
Re: [2005] DatagridView expand feature?
@ 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?
-
Re: [2005] DatagridView expand feature?
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?
-
Re: [2005] DatagridView expand feature?
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
-
Re: [2005] DatagridView expand feature?
:) My 100th post :)
Lets fix the duplicate button problem first.
I'm confused about your OK button. Why would you click on it twice?
-
Re: [2005] DatagridView expand feature?
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).
-
Re: [2005] DatagridView expand feature?
id theform with the datagridview, the same form with the ok button, or is it on a different form?
-
Re: [2005] DatagridView expand feature?
its all in the same form....the datagridview population is inside the button all in the same form.
-
Re: [2005] DatagridView expand feature?
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.
-
Re: [2005] DatagridView expand feature?
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.
-
Re: [2005] DatagridView expand feature?
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
-
Re: [2005] DatagridView expand feature?
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
-
Re: [2005] DatagridView expand feature?
oh!!! That works perfect. Creates only one column........
-
Re: [2005] DatagridView expand feature?
by the way.......did u have any idea on the id issue?
-
Re: [2005] DatagridView expand feature?
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.
-
Re: [2005] DatagridView expand feature?
ok. said and done. Works perfect
-
Re: [2005] DatagridView expand feature?
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.
-
Re: [2005] DatagridView expand feature?
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)
-
Re: [2005] DatagridView expand feature?
There is already a vb method called Average. This might be your problem. Rename it and try it.
-
Re: [2005] DatagridView expand feature?
i renamed it to SambaTriste and still got the error
Code:
Unable to cast object of type 'SambaTriste' to type 'System.Data.DataRowView'.
-
Re: [2005] DatagridView expand feature?
Post your code for the cellclick.
-
Re: [2005] DatagridView expand feature?
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
-
Re: [2005] DatagridView expand feature?
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
-
Re: [2005] DatagridView expand feature?
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
-
Re: [2005] DatagridView expand feature?
Can you display the code you use to populate the datagridview.
-
Re: [2005] DatagridView expand feature?
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"
-
Re: [2005] DatagridView expand feature?
Wow, :ehh: :ehh: :ehh: :ehh: :ehh:
I'm interested in the population of the datagridview
-
Re: [2005] DatagridView expand feature?
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
-
Re: [2005] DatagridView expand feature?
Time to resolve this thread. :) :o :D ;) :p :rolleyes: :wave: :lol: :thumb: :afrog:
-
Re: [2005] DatagridView expand feature?
heheh, yup :afrog: :thumb: :wave: :D :p
I hope we can make it!!! :thumb:
-
Re: [2005] DatagridView expand feature?
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
-
Re: [2005] DatagridView expand feature?
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
-
Re: [2005] DatagridView expand feature?
What database are you using?
-
Re: [2005] DatagridView expand feature?
Microsoft SQL server express
-
Re: [2005] DatagridView expand feature?
I think you should be looking at stored procedures to get your data. Any chance you can send me a copy of your database?
-
Re: [2005] DatagridView expand feature?
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)
-
Re: [2005] DatagridView expand feature?
See if you can attach the access db for now. Compact it first to decrease the file size.
-
Re: [2005] DatagridView expand feature?
-
Re: [2005] DatagridView expand feature?
I think its time to close this thread. We are getting off its subject and its to long.
I sent you private message.
-
Re: [2005] DatagridView expand feature?
ok...ill close it then. Btw i just saw the PM...Thx for the help