[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!!