Binding Combobox to Foreign Key in Datagrid

  • Thread starter Thread starter Steve S
  • Start date Start date
S

Steve S

I am having a problem with getting a bound combobox to display the
correct value. I'm hoping someone can point out where I've gone wrong.

The scenario is a main table of Quotations by Author (Aristotle,
Einstein, Will Rogers, Yogi Berra, etc.) and a foreign key for the
quotation's Genre. The look up table, "Genre" has 2 columns Genre and
Genre_id. The data in the look up table looks like:
Genre_id Genre
0 Undefined (This is the default for the foreign key in the
main table)
1 Humorous
2 Insightful
3 Inspirational
etc.

I have set many of the qutations' foreign key to the appropriate
genre_id but most are the default of 0 for undefined.

I've loaded a treeview with quotation Authors. Selecting the Author
loads a dataset with the authors quotations that is then bound to a
datagrid. Selecting the quotation in the grid loads bound text fields
for editing and should position the combobox to the Genre for the
quotation. Key here is should. This is where I seem to have lost it
and am asking for help.

The grid style contains the foreign key as well as the other fields I
want to edit. The combobox is loaded with all the Genre records just
not showing the correct genre for the record selected in the grid; it
always shows the first record. I am able to force the correct combobox
display but not when bound

Here is the pertinent code:

Dim cn As New SqlCeConnection(LocalCnxStr)
Dim cmd as SqlCeCommand
dim da as new SqlCeDataAdapter
dim cell as new DataGridCell

if isnothing (dsQ) then
dsq = new DataSet
'// first time thru set the select command for the look up
data
cmd = New SqlCeCommand("Select genre as Genre, " & _
"convert (nvarchar,genre_id) as
Genre_id " & _
"from genre ",cn)
da.SelectCommand = cmd
da.Fill(dsQ,"Genre") '// first table(0)

'// then load the look up cbo
cboGenre.DataSource = dsq.Tables("Genre")
cboGenre.DisplayMember = "Genre"
cboGenre.ValueMember = "Genre_id"

else '// clear records in 2nd data table
'// from previous selection
'//cboGenre.SelectedIndex = -1
'dsq.Relations.Clear
dsq.Tables("AQuotes").Clear
dgQuotes.TableStyles.Clear '// clear the grid style

'//unBindQuotes '// unbind the editor fields
txtAuthor.DataBindings.Clear
txtQuote.DataBindings.Clear
txtContext.DataBindings.Clear
lblguid.DataBindings.Clear
cboGenre.DataBindings.Clear

end if

'// set the parameter value with the selected tree node
daAuthor.SelectCommand.Parameters.Item(0).Value =
trvwQ.SelectedNode.Text

'// fill the dataset with the 2nd named table
'// daAuthor commands set in InitAuthorAdapter
daAuthor.Fill(dsQ,"AQuotes") '// 2nd table(1)

'// relation doens't appear to have any affect with
'// binding foreign key to combo box
'dsq.Relations.Add("QuoteGenre",dsq.Tables("AQuotes").Columns("Genre_id"),
_
'
dsq.Tables("Genre").Columns("Genre_id"),False)
'// setting constraint to true genereates error
'// as not all records in parent use all look up table
records
'// need all look up records to change Genre for selected
record

'// bind the grid to the table
dgQuotes.DataSource = dsQ.Tables("AQuotes")
'//BindQuotes '// bind the editor fields
txtAuthor.DataBindings.Add("Text", dgQuotes.datasource,
"Author")
txtQuote.DataBindings.Add("Text", dgQuotes.datasource,
"Quote")
txtContext.DataBindings.Add("Text", dgQuotes.datasource,
"Context")
lblguid.DataBindings.Add("Text", dgQuotes.DataSource,
"quote_id")

'// Bind cbo to foreign key in grid
'// doesn't seem to work
cboGenre.DataBindings.Add("SelectedItem",
dgQuotes.DataSource, "Genre_id")

'// grid style includes fk and all fields for editing
QgridStyle '// set the grid style

'// not able to get the cbo binding working correctly
'// so force the current cell changed event to fire
'// and set the cbo selected index appropriately there
'cell = dgquotes.CurrentCell
'dgquotes.CurrentCell = new DataGridCell(0,1)
'dgquotes.CurrentCell = cell

'// code from grid cellchanged event
'dim dg as new DataGrid
'dg = sender
'// this is bad coding since the item index and the key
'//are assumed to be the same
'// may not always be the case
'// should use for each loop instead - slower
'cboGenre.SelectedIndex = dg.item(dg.CurrentCell.RowNumber,3)


Any and all assistance in resolving this will be appreciated.

Steve
 
Your combo cboGenre should be bound to the SelectedValue instead of the
SelectedItem. As an aside, note that
ideally, you would not need to convert your genre_id in the database. This
should be an integer value. Also, release and re-bind is resource-intensive.
 
Earl,

Thanks for the reply. That solved my problem.

The rebind and integer suggestions were also implemented and worked
well. I didn't realize how resource intensive the rebind was,
definitley speeded up the app.

Thanks again

Steve
 
Great ... working with CF, we do need to enhance the processing speed with
every tip and trick we can find.
 
Back
Top