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

graham_ballAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Oliver AmayaEntrepeneurCommented:
How are you setting the ComboBox's text?
0
graham_ballAuthor 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.
0
monarch_ilhanCommented:
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.

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

graham_ballAuthor Commented:
Previous post answers that (posts must  have crossed)
0
Oliver AmayaEntrepeneurCommented:
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.
0
graham_ballAuthor Commented:
That's what the combobox does. Or am I being stupid here ?
0
Oliver AmayaEntrepeneurCommented:
Your combobox is using a differente query than the one you posted?
0
graham_ballAuthor 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.
0
Oliver AmayaEntrepeneurCommented:
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?
0
graham_ballAuthor Commented:
both number(10
0
Oliver AmayaEntrepeneurCommented:
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

0
graham_ballAuthor 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

0
Oliver AmayaEntrepeneurCommented:
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

0
graham_ballAuthor 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.
0
Oliver AmayaEntrepeneurCommented:
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

0
graham_ballAuthor Commented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vee_ModCommented:
Closed, 250 points refunded.
Vee_Mod
Community Support Moderator
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.