We help IT Professionals succeed at work.

Datagridview comboboxcolumn Oracle datasource problem

I have a cobobox column in a datagridview that works fine with the attached code; it displays the correct text for the value it retrieves from the database.
However, If I make a minor alteration to the code to handle null values, it doesn't work. I get the
{"DataGridViewComboBoxCell value is not valid."} error.

When I handle the error and cancle straight out of it, the key value is displayed in the combobox, which is a valid value, as it worked fine previously.

I've tried handling the null value as follows, but again, I just get the value (1009107) displayed in the combobox, not it's equivalent text.
In both cases, clicking on the combobox removes the key value and displays the dropdown list as expected.

string SQL
	= "select tr.recommendation_id  "
	+ ", substr(tco.COMMON_DESCRIPTION,instr(tco.COMMON_DESCRIPTION,' ') +1) Result  "
// This works fine
	+ ", tr.FIRST_TIME_ACCEPTANCE_CODE_ID RFT_CODE_ID "
// This doesn't
	+ ", nvl(tr.FIRST_TIME_ACCEPTANCE_CODE_ID, 1009107) RFT_CODE_ID "
// Neither does this
	+ ", decode(tr.FIRST_TIME_ACCEPTANCE_CODE_ID, null, 1009107, tr.FIRST_TIME_ACCEPTANCE_CODE_ID) RFT_CODE_ID "
	+ "from t_recommendations tr  "
	+ "	, t_products tp  "
	+ "    , exii.t_codes tc "
	+ "    , t_common tco  "
	+ "    , t_providers tpr "
	+ "where tr.PRODUCT_ID = tp.PRODUCT_ID  "
	+ "and tr.PREMIUM_FREQUENCY_CODE_ID = tc.code_id  "
	+ "and tr.factfind_id = '" + factfindID + "' "
	+ "and tr.letter_id = '" + letterID + "' "
	+ "and tr.ACCEPTED_CODE_ID = tco.COMMON_ID "
	+ "and tr.PROVIDER_ID = tpr.PROVIDER_ID(+)  "
	+ "order by 1 "
	;
OracleDataAdapter da = new OracleDataAdapter(SQL, Connection);
DataTable dtRFT = new DataTable();
da.Fill(dtRFT);
dgvRFT.DataSource = dtRFT;

Open in new window

Comment
Watch Question

Oliver AmayaEntrepeneur

Commented:
How are you setting the ComboBox's text?

Author

Commented:
DataGridViewComboBoxColumn comboboxColumn = new DataGridViewComboBoxColumn();
comboboxColumn.DataPropertyName = "RFT_CODE_ID";
comboboxColumn.HeaderText = "RFT";
comboboxColumn.Name = "RFT_CODE_ID";
comboboxColumn.DropDownWidth = 90;
comboboxColumn.Width = 110;
comboboxColumn.MaxDropDownItems = 10;
comboboxColumn.FlatStyle = FlatStyle.Flat;
comboboxColumn.SortMode = DataGridViewColumnSortMode.Automatic;

CodesTable codes = new CodesTableAdapter(Connection).GetCodesByTypeID(525, false);
comboboxColumn.DataSource = codes;

comboboxColumn.ValueMember = codes.CodeIDColumn.ColumnName;
comboboxColumn.DisplayMember = codes.CodeDescriptionColumn.ColumnName;
//comboboxColumn.ReadOnly = true;
dgvRFT.Columns.Add(comboboxColumn);


Codes are as follows
CODE_ID      CODE_DESCRIPTION
1009103      Unsuitable
1009104      Factfind
1009105      Clarify
1009106      Suitability letter/report
1009107      Accept
1009108      Corporate stance not followed
1009300      Return to FC

code_id is number(10), as is the FIRST_TIME_ACCEPTANCE_CODE_ID.
Where do you set Value and Text fields of combobox?

dgvRFT.DataValueFiled="RFT_CODE_ID"
dgvRFT.DataTextField="RFT_CODE_ID" or something different for txt field.

Author

Commented:
Previous post answers that (posts must  have crossed)
Oliver AmayaEntrepeneur

Commented:
And how are you getting that description from you query? when you use nvl for the ID that's only going to work in that particular column, is not goint to automatically fetch the description associated with that ID.

Author

Commented:
That's what the combobox does. Or am I being stupid here ?
Oliver AmayaEntrepeneur

Commented:
Your combobox is using a differente query than the one you posted?

Author

Commented:
It's populated here
CodesTable codes = new CodesTableAdapter(Connection).GetCodesByTypeID(525, false);
comboboxColumn.DataSource = codes;
comboboxColumn.ValueMember = codes.CodeIDColumn.ColumnName;
comboboxColumn.DisplayMember = codes.CodeDescriptionColumn.ColumnName;

The way I understand it is that the combobox dataproperty is used to match against the list of keys that have been defined (as above ). The dataproperty is the value of the underlying cell.
comboboxColumn.DataPropertyName = "RFT_CODE_ID";

When the cell value and key match the associated description is displayed.
As I said before, this works fine for the first case, but not for the other 2.
Oliver AmayaEntrepeneur

Commented:
What DataType is the column tr.FIRST_TIME_ACCEPTANCE_CODE_ID from the query? and what DataType is the column CODE_ID from the ComboBox?

Author

Commented:
both number(10
Oliver AmayaEntrepeneur

Commented:
Ok, try using this as the value to your query:
", nvl(tr.FIRST_TIME_ACCEPTANCE_CODE_ID, CAST(1009107 AS tr.FIRST_TIME_ACCEPTANCE_CODE_ID%TYPE)) RFT_CODE_ID "

Open in new window

Author

Commented:
Ah ! It appears I haven't made myself clear.
There is a valid value in the underlying data, which displays the correct description in the combobox when I run with the first piece of code.
Using the NVL version stops it displaying correctly, even though the underlying value hasn't changed.

I'll be happy if someone can point me to an alternative solution other than using NVL().
I have also tried this code (attached), but even though the value matches an entry in the combobox datasource, I just get the underlying value displayed in the combobox instead of the description (as it does using the NVL version).
private void dgvRFT_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{
	if (e.ColumnIndex == 5) // combobox 
	{
		if (e.Value.ToString().Length == 0)
		{
			e.Value = 1009107;
			dgvRFT.Rows[e.RowIndex].ReadOnly = false;
		}
		else
		{
			dgvRFT.Rows[e.RowIndex].ReadOnly = true;
		}
	}
}

Open in new window

Oliver AmayaEntrepeneur

Commented:
Another way to do it is like this, setting the NullValue property when you define the column:
comboboxColumn.DefaultCellStyle.NullValue = "Accept";

Open in new window

Author

Commented:
That displays the combobox correctly when there is a null value, but it doesn't update the underlying cell value.
So when I go to update the database, there is still a null value there.
If I change the combobox selection, a value gets put into the underlying cell, but not for the default.
Oliver AmayaEntrepeneur

Commented:
There's an additional property to control the value of the datasource, try adding this where you define your combobox column:
comboboxColumn.DefaultCellStyle.DataSourceNullValue = 1009107;

Open in new window

No, sorry - that didn't work either. A weird place to put data defaults IMHO.
Anyway, I have now go it to work.
A roundabout way of doing it , but it works.
I used the cell-formatting event to set a default value in the datagrid itself - using e.value just didn't work.
I've spent the best part of a day trying to get (yet again!) a simple MS control to work properly !!!!!
Thanks anyway for your help.

Cheers
private void dgvRFT_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{
	if (e.ColumnIndex == 5) // combobox 
	{
		if (e.Value.ToString().Length == 0)
		{
			dgvRFT.Rows[e.RowIndex].Cells[e.ColumnIndex].Value = 1009107;
		}
	}
}

Open in new window

Commented:
Closed, 250 points refunded.
Vee_Mod
Community Support Moderator

Explore More ContentExplore courses, solutions, and other research materials related to this topic.