I'm attempting to allow a DataGridView to allow the user to edit fields (for simplicity's sake, let's say it's just Items, each of which can have a Condition, with allowable values taken from a second table [Conditions]
e.g.
Data Structure (simplified)
Items Table
- ID (Primary key--not to be shown)
- ItemNum
- Qty
- ConditionAbbrev (e.g. "New", "Used", "Recert") -- used as a key to an item in the Conditions table below
Conditions Table
- ConditionAbbrev (e.g. "New", "Used", "Recert", etc.
- ConditionDescription (lengthy description of condition)
Desired Appearance of Grid:
My Grid should look like:
Item # Qty Condition
123456 10 [ New v] <-- A drop-down
234567 55 [ Used v]
345678 99 [ New v]
etc.
The Strategy:
I'm attempting to set this up by:
Binding the grid to the first Items table (grabbing the first three columns which contain the actual values of each line of the Items table)
Creating a new DataGridViewComboBoxColumn ("CondCombo") and binding all allowable items from the Conditions table to it,
Looping through the grid and setting the value of CondCombo for each row to the value of the Conditions row
Hiding the Conditions (text) column.
The Problem:
I'm able to get the column added and loaded with Conditions values, but I'm utterly failing at setting the selected value to match the Condition from the Items table; furthermore, any selections I'm making in a combo is being blanked the moment I tab or click to another cell.
Here's the code I've got so far: any help would be HUGELY appreciated!
Sub SetupGrid(byref myGrid as DataGridView,
myConn as sqlite.sqliteConnection)
Dim myAdapter As System.Data.SQLite.SQLiteDataAdapter
myGrid.VirtualMode = true
myAdapter = new system.data.sqlite.sqliteadapter(_
"Select ID, ItemNum, Qty, Condition FROM Items", myConn)
myAdapter.SelectCommand.CommandType = CommandType.Text
' Fill the main grid with the item data
dim ds as new DataSet
myAdapter.Fill(ds)
myGrid.DataSource = ds.Tables(0)
' Now create and load the ComboBox column
dim cboColumn as new DataGridViewComboBoxColumn
With cboColumn
.DataPropertyName = "ConditionAbbrev"
.name = "CondCombo"
.HeaderText = "Cond"
' Bind the ComboColumn
Dim conditionsAdapter As System.Data.SQLite.SQLiteDataAdapter
Dim condTable As DataTable
using cmd as new Sqlite.sqliteCommand(_
"SELECT ConditionAbbrev FROM Conditions", myconn)
conditionsAdapter.selectCommand = cmd
conditionsApapter.fill(condTable)
end using
.DataSource = condTable
.DataPropertyName = "ConditionAbbrev"
.ValueMember = "ConditionAbbrev"
.DisplayMember = .ValueMember
end with
' Set the selected combo member to be the same as the Condition (text) field value:
for each curRow as dataGridViewrow in myGrid.Rows()
curRow.cells("CondCombo").value = _
curRow.Cells("Condition").value
next
' Hide the Condition (text) field)
myGrid.Columns("Condition").visible = false
' Hide the ID field
myGrid.Columns("ID").visible = false
end sub