How do you reset a bound combo box value to null?

[aprobe]

I'm using .Net 1.1 and Sql Server database.

I have a situtation where a combo box is bound to a dataset and will quite happily update the column when the user changes the value.
The default value for the field is null. In this case the field is a Country ID code and has a foreign key on a country table.
I only want the user to populate the country field when the country is foreign. If the user has populated incorrectly I want to remove the setting i.e. reset it to null.

I have a empty string option in the combo (although this is not a valid foreign key) and when the user selects to save the record if the empty string is selected I set the combo.Text field to null.

The problem is if I use the normal update methods (System.Data.DataRowState.Modified) the dataset does not recognise this as a change.

I could put special code in to update the table directly with Sql but this does seem unnecessary. There must be an easy way.
It's not a problem with the database as I can quite happily set the the field to null, it's simply not recognising the column has changed and must be set to null - somehow.

Any ideas ?

 

[SHelton]

Try setting the field to DBNull.Value rather than just null.

 

[aprobe]

It works if I update the column on the current row of the dataset directly to DBNull.Value.

Messy but it works.

Thanks for that.

 

[chiph]

Messy but it works.

Yeah, since you're bound, everything has to happen via the dataset.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[SHelton]

The way I usually get around this is to insert a row into the combo box data source (assuming it is a datatable) with a value member of DBNull.Value and a description like "Unknown". Then the databinding takes care of inserting the DBNull.Value if the user selects the Unknown item from the list.

Such as:

SqlDataAdapter da = new SqlDataAdapter("SELECT CountyID, County FROM Counties", conn);

da.Fill(dt);

DataRow dr = dt.NewRow();

dr[0] = DBNull.Value;

dr[1] = "Unknown";

dt.Rows.InsertAt(dr, 0);

comboBox1.DataSource = dt;

comboBox1.ValueMember = "CountyID";

comboBox1.DisplayMember = "County";

 

[aprobe]

Great thanks for that.

It's given me some good ideas!