Binding Combobox to Foreign Key in Datagrid

[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

 

[Earl]

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.

 

[Guest]

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

 

[Earl]

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