Search code examples
vb.netdatasetdatagridviewcomboboxcell

Dataset to set default SelectedValue in DatagridViewComboboxColumn rows

I have a DataGridView (dgwList), into which I load set of items. It's an intermediate form, where user needs to select Action and Category, for which I created DataGridViewComboBoxColumns and set them in Form_Load event like this:

    dtCats = ds.Tables(0)      ' Datatable with Categories
    dtActions = ds.Tables(1)   ' Datatable with Actions

    Dim colCat As DataGridViewComboBoxColumn = Me.dgwList.Columns("Category")
    Dim colActions As DataGridViewComboBoxColumn = Me.dgwList.Columns("Action")

    colCat.ValueMember = "ID"
    colCat.DisplayMember = "CategoryText"
    colCat.DataSource = dtCats
    colCat.DataPropertyName = "Category"

    colActions.ValueMember = "ID"
    colActions.DisplayMember = "Action"
    colActions.DataSource = dtActions
    colActions.DataPropertyName = "Action"

Everything works fine, when I load items (using another dataset) without default values of Action and Category, I can select them from the DropDownList. However, when I load the second dataset (ds) and use i.e. "Action" to load a value as a default SelectedValue like this:

    Dim cmdtext As String = "SELECT ****, 2 as Action, *** WHERE ****; "
    Dim ds As DataSet
    ds = DAL.GetQueryResults(cmdtext)  ' using own Data Access Layer to get dataSet

    Me.dgwList.AutoGenerateColumns = False
    Me.dgwList.DataSource = ds.Tables(0)  ' load the data table

I get "Datagridviewcomboboxcell Value Is Not Valid" error. It seems, that it tries to load it as DisplayMember, or simply a Text property of the DatGridViewComboBoxCell, as it appears as selected text.

enter image description here

I spend a lot of time browsing forums and I found nothing related to dataset (except Set selectedValue in DataGridViewComboBoxColumn where was missing DataPropertyName). Everything is about loops and events raised by user over the comboboxcell.

I would like to avoid the error and use the dataset (not looping to modify each cell) to fill the default values of comboboxes. Any idea?

Solution

  • Ok,

    the Devil is in the detail. I had ID set to tinyint data type in the database, while when I was trying to set i.e. 2, it was handled as int32.

    I forced input value to int16 and set ID to smallint in DB and then it works - I can set a value: 

        Dim CBox As DataGridViewComboBoxCell = CType(Me.dgwList.Rows(2).Cells("Category"), DataGridViewComboBoxCell)
    CBox.Value = CType(2, System.Int16)  ' set value to 2

    So I found the problem, but it wouldn't qualify for an answer to my question, because it handles a particular cell, not the dataset. But adding CAST(.... as smallint) to SQL query fully resolves the issue (in combination with setting ID in database to smallint too):

     SELECT ...
 CAST(CASE COL.Reason WHEN 'Service Fault' THEN 2 WHEN 'Program Fault' THEN 9 END as smallint)  as Category
 WHERE ... ORDER BY ....;