[RESOLVED] Populate datagridview combobox based on index

[Chrissy]

Hello,

Most of my experience is with VB6 and VBA. I am new to .NET.

I have a datagridview that I am creating dynamically because the number of columns is based on the data returned from the database. First I am creating the bones of the datagridview... the 1st column is a description and is static, then each column represents a person (column header). All columns are combo boxes and have the same selections.



Once the above is created, I query the database based on the column header to populate the combo box based on the stored index value. I cannot get the combobox to select the item based on the index. I have a tried a thousand things, but clearly not the right thing!

Below is my code... any suggestions/comments are appreciated. I am new to VB.NET....

Dim xRow As Integer = 1
Dim xColCount As Integer = 0
Dim db As New DataAccess.sqlDataBase("hp_accting")
Dim reader As SqlClient.SqlDataReader = db.GetRealtedRecords("ownership o", "partner p", "p.ocode", "o.ocode",
                                                "p.name, o.ocode", "Tax_projection = '" & cmbYear.Text &
                                                "' AND entity_num = '" & cmbEntity.Text.Substring(0, 8).Trim & "'", "p.ocode")
        Try

            'This loads the partner
            If reader.HasRows Then

                dtgAllocation.Columns.Add("Description", "Taxable Income/(Loss)")

                While reader.Read
                    xColCount = xColCount + 1
                    Dim cmb As New DataGridViewComboBoxColumn()
                    cmb.Name = reader.Item("ocode").ToString
                    cmb.HeaderText = reader.Item("name")
                    cmb.MaxDropDownItems = 3
                    cmb.DisplayMember = "Percent_Type"
                    cmb.ValueMember = "percent_code"

                    cmb.Items.Add("Direct")
                    cmb.Items.Add("Indemnification")
                    cmb.Items.Add("Other")
                    dtgAllocation.Columns.Add(cmb)
                End While

            End If

            reader.Close()
            dtgAllocation.Update()
            reader = db.GetRecords("Tax_allocation", , , "allocation_code")

            While reader.Read
                dtgAllocation.Rows.Add(reader.Item("allocation_name"))
                xRow = xRow + 1
            End While

            reader.Close()
            dtgAllocation.Update()

            For i = 1 To xColCount - 1

                xRow = 0
                reader = db.GetRecords("tax_Owner_Allocations", , "projection_year = '" & cmbYear.Text &
                                       "' AND ocode = '" & dtgAllocation.Columns(i).Name.ToString &
                                       "' AND entity_num = '" & cmbEntity.Text.Substring(0, 8).Trim & "'", "allocation_code")

                If reader.HasRows Then
                    While reader.Read
                'THIS IS THE PROBLEM LINE OF CODE
                        dtgAllocation.Rows(xRow).Cells(reader.Item("ocode").ToString).Selected = reader.Item("percent_type").ToString
                        xRow = xRow + 1
                    End While

                End If
                reader.Close()
            Next

            dtgAllocation.Update()

            db = Nothing
            reader = Nothing

        Catch ex As Exception
            MsgBox(Err.Description, MsgBoxStyle.OkOnly, Err.Number.ToString)
        End Try

End Sub

Public Function GetRecords(table As String, Optional fields As String = "*", Optional where As String = "", Optional orderBy As String = "") As SqlDataReader

        Dim q As String = "SELECT " & fields & " FROM " & table

        'Check for where clause
        If where <> "" Then
            q &= " WHERE " & where
        End If

        'Check for order by
        If orderBy <> "" Then
            q &= " ORDER BY " & orderBy
        End If

        Try
            Dim cmd As New SqlCommand(q, _connection)
            Return cmd.ExecuteReader()

        Catch ex As Exception

            MsgBox("An error occured while fetching data." & vbNewLine &
                   "Query string: " & q & vbNewLine &
                   "Error Message: " & ex.Message, MsgBoxStyle.Critical)
        End Try

        Return Nothing

End Function

Public Function GetRealtedRecords(table1 As String, table2 As String, onField1 As String,
                                      onField2 As String, Optional fields As String = "*",
                                      Optional where As String = "", Optional orderby As String = "") As SqlDataReader

        Dim q As String = "SELECT " & fields & " FROM " & table1 & " INNER JOIN " & table2 &
            " ON " & onField1 & "=" & onField2

        'Check for where clause
        If where <> "" Then
            q &= " WHERE " & where
        End If

        'Check for order by
        If orderby <> "" Then
            q &= " ORDER BY " & orderby
        End If

        Try
            Dim cmd As New SqlCommand(q, _connection)
            Return cmd.ExecuteReader

        Catch ex As Exception

            MsgBox("An error occured while fetching data." & vbNewLine &
                   "Query string: " & q & vbNewLine &
                   "Error Message: " & ex.Message, MsgBoxStyle.Critical)

        End Try

        Return Nothing

 End Function

Thank you!!

[wes4dbt]

Once the above is created, I query the database based on the column header to populate the combo box based on the stored index value

Not exactly clear on what your saying.

Are you saying you want to fill all the CB's under "HP Operating" based on an index value stored in a database table?

That doesn't seem to be the case because you said "All columns are combo boxes and have the same selections". So if all the CB's have the same selections then what is the real question.

[Chrissy]

Are you saying you want to fill all the CB's under "HP Operating" based on an index value stored in a database table?

Yes

That doesn't seem to be the case because you said "All columns are combo boxes and have the same selections". So if all the CB's have the same selections then what is the real question.

All combo boxes have the same list values. All have a choice of "Direct", "Indemnification" or "Other". The database stores the corresponding index value (0-Direct, 1-Indemnification, 2-Other). Based on the person (column header), I want to populate the selection stored in the database. I can't figure out how to take the index number and show the combobox selection on the grid.

Did that make sense?

[wes4dbt]

I want to populate the selection stored in the database

Do you mean, you want to Display either "Direct", "Indemnification" or "Other" in the CB depending on the value stored for the Column Header? Since "All combo boxes have the same list values"

Also, do all the rows have the same CB values or does each row have an index value stored in a database table for the DGV column header.

[Chrissy]

Do you mean, you want to Display either "Direct", "Indemnification" or "Other" in the CB depending on the value stored for the Column Header? Since "All combo boxes have the same list values"

Yes

Also, do all the rows have the same CB values or does each row have an index value stored in a database table for the DGV column header.

Yes. I was able to populate the drop downs with the choices successfully, I just can't get them "selected".

Every combo box on the entire grid has all the same choices, but each one has different value stored based on the column header and the row.

Basically, I just need to know how to populate the combobox on a datagridview using the index value that is stored in the database.

[wes4dbt]

Are you having trouble retrieving the store index or displaying the information in the CB, or both.

One thing you need to know is a DGV comboBox column does not have a SelectedIndex or SelectedValue property that you can set. You must set the dgv.row(someRow).Cells(somecolumn).Value = "something"

Here a good link, http://stackoverflow.com/questions/2...ridview-vb-net

[Chrissy]

Are you having trouble retrieving the store index or displaying the information in the CB, or both.

One thing you need to know is a DGV comboBox column does not have a SelectedIndex or SelectedValue property that you can set. You must set the dgv.row(someRow).Cells(somecolumn).Value = "something"

Here a good link, http://stackoverflow.com/questions/2...ridview-vb-net

I am having trouble displaying the information in the CB. I can retrieve the data fine, just can't populate the combobox using the index.

Is there a way around not having a selectedindex? Would using ENUM work? (not really sure how to do this either!)

I will take a look at the link. Thank you.

[Chrissy]

I am having trouble displaying the information in the CB. I can retrieve the data fine, just can't populate the combobox using the index.

Is there a way around not having a selectedindex? Would using ENUM work? (not really sure how to do this either!)

I will take a look at the link. Thank you.

I figured it out... here is my code for future reference.

        Dim db As New DataAccess.sqlDataBase("hp_accting")
        
        Try
            dtgAllocation.Columns.Add("Description", "Taxable Income/(Loss)")

            Dim reader As SqlClient.SqlDataReader = db.GetRecords("Tax_allocation", , , "allocation_code")

            While reader.Read
                dtgAllocation.Rows.Add(reader.Item("allocation_name"))
                xRow = xRow + 1
            End While

            reader.Close()
            dtgAllocation.Update()

            reader = db.GetRealtedRecords("ownership o", "partner p", "p.ocode", "o.ocode",
                                                "p.name, o.ocode", "Tax_projection = '" & cmbYear.Text &
                                                "' AND entity_num = '" & cmbEntity.Text.Substring(0, 8).Trim & "'", "p.ocode")
            'This loads the partner
            If reader.HasRows Then

                While reader.Read
                    xColCount = xColCount + 1
                    Dim cmb As New DataGridViewComboBoxColumn()
                    cmb.Name = reader.Item("ocode").ToString
                    cmb.HeaderText = reader.Item("name")
                    cmb.MaxDropDownItems = 3
                    'Got rid of value member in order to use "Item"
                    cmb.Items.Add("Direct")
                    cmb.Items.Add("Indemnification")
                    cmb.Items.Add("Other")
                    dtgAllocation.Columns.Add(cmb)
                    xRow = 0

                    dtgAllocation.Update()

                    Dim dbPop As New DataAccess.sqlDataBase("hp_accting")
                    Dim readerPop As SqlClient.SqlDataReader = dbPop.GetRecords("tax_Owner_Allocations", , "projection_year = '" & cmbYear.Text &
                            "' AND ocode = '" & reader.Item("ocode").ToString &
                            "' AND entity_num = '" & cmbEntity.Text.Substring(0, 8).Trim & "'", "allocation_code")

                    If readerPop.HasRows Then
                        While readerPop.Read

                            dtgAllocation.Rows(xRow).Cells(xColCount).Value = cmb.Items(readerPop.Item("percent_type"))
                            xRow = xRow + 1

                        End While

                    End If
                    dbPop = Nothing
                    readerPop = Nothing
                End While

            End If

            reader.Close()
            dtgAllocation.Update()