[RESOLVED][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

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