1 Attachment(s)
[RESOLVED] Populate datagridview combobox based on index
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.
Attachment 144275
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.... :confused:
Code:
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!!
Re: Populate datagridview combobox based on index
Quote:
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.
Re: Populate datagridview combobox based on index
Quote:
Originally Posted by
wes4dbt
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
Quote:
Originally Posted by
wes4dbt
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?
Re: Populate datagridview combobox based on index
Quote:
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.
Re: Populate datagridview combobox based on index
Quote:
Originally Posted by
wes4dbt
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
Quote:
Originally Posted by
wes4dbt
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.
Re: Populate datagridview combobox based on index
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
Re: Populate datagridview combobox based on index
Quote:
Originally Posted by
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
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.
Re: Populate datagridview combobox based on index
Quote:
Originally Posted by
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.
Code:
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()
