[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Datagridview comboboxcolumn Oracle datasource problem

Posted on 2008-01-28
18
Medium Priority
?
1,181 Views
Last Modified: 2013-12-07
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

0
Comment
Question by:graham_ball
17 Comments
 
LVL 10

Expert Comment

by:Oliver Amaya
ID: 20758998
How are you setting the ComboBox's text?
0
 

Author Comment

by:graham_ball
ID: 20759066
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
 
LVL 6

Expert Comment

by:monarch_ilhan
ID: 20759073
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

Author Comment

by:graham_ball
ID: 20759207
Previous post answers that (posts must  have crossed)
0
 
LVL 10

Expert Comment

by:Oliver Amaya
ID: 20759272
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
 

Author Comment

by:graham_ball
ID: 20759309
That's what the combobox does. Or am I being stupid here ?
0
 
LVL 10

Expert Comment

by:Oliver Amaya
ID: 20759364
Your combobox is using a differente query than the one you posted?
0
 

Author Comment

by:graham_ball
ID: 20759429
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
 
LVL 10

Expert Comment

by:Oliver Amaya
ID: 20759885
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
 

Author Comment

by:graham_ball
ID: 20759939
both number(10
0
 
LVL 10

Expert Comment

by:Oliver Amaya
ID: 20760306
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
 

Author Comment

by:graham_ball
ID: 20760419
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
 
LVL 10

Expert Comment

by:Oliver Amaya
ID: 20761797
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
 

Author Comment

by:graham_ball
ID: 20766317
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
 
LVL 10

Expert Comment

by:Oliver Amaya
ID: 20767774
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
 

Accepted Solution

by:
graham_ball earned 0 total points
ID: 20767970
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
 
LVL 1

Expert Comment

by:Vee_Mod
ID: 20875354
Closed, 250 points refunded.
Vee_Mod
Community Support Moderator
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

611 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question