Home » Windows OSRSS

ComboBox value does not changes to null, if database value changes to null

Hi there,

I've written a simple Demo Application with VS 2008 SP1 to show my problem and I did the Databinding like that::

            comboBox1.DataBindings.Add(bComboBox1);
            comboBox1.DataSource = ds.Tables["Titel"].DefaultView;
            comboBox1.DisplayMember = "Titel";
            comboBox1.ValueMember = "Titel";
            comboBox1.DropDownStyle = ComboBoxStyle.DropDownList;
            comboBox1.SelectedIndex = -1;

If I click with the BindingNavigator from DataRow to DataRow, there appears my problem:

The ComboBox is contains the same value, if the database value changes to null. But if the Database value changes to an other value not equal null then it shows the correct value.

If I use: comboBox1.SelectedIndex = -1; then the first value is correct (weather if its null or not), but if the database value changes for the first time, again not.

There is the complete sourcecode, but I guess, you won't need it:

Many greetings from Blackforest, Germany,

Christoph Rall

using System;
using System.Data;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace DataGridViewTestDBAnbindung
{
    public partial class Form1 : Form
    {
        private SqlConnection con;
        private DataSet ds;
        private SqlDataAdapter da1;
        private SqlDataAdapter da2;
        private SqlDataAdapter da3;
        private SqlCommandBuilder cb1;
        private SqlCommandBuilder cb2;
        private BindingSource bs1;
        private BindingSource bs2;
        private DataRelation dr;

        public Form1()
        {
            InitializeComponent();

            // Anbindung des SQL Servers
            con = new SqlConnection("Data Source=MIB-VM-ETS;Initial Catalog=PIS;Integrated Security=True;");
            con.Open();
            da1 = new SqlDataAdapter("select * from person", con);
            da2 = new SqlDataAdapter("select * from adresse", con);
            da3 = new SqlDataAdapter("SELECT * FROM Titelvorschlaege ORDER BY Titel", con);
            ds = new DataSet();
            da1.FillSchema(ds, SchemaType.Mapped); // Schema Information über die Tabelle aus DB abrufen
            da2.FillSchema(ds, SchemaType.Mapped); // Schema Information über die Tabelle aus DB abrufen
            da3.FillSchema(ds, SchemaType.Mapped); // Schema Information über die Tabelle aus DB abrufen
            da1.Fill(ds, "Person");
            da2.Fill(ds, "Adresse");
            da3.Fill(ds, "Titel");
            da1.RowUpdated += new SqlRowUpdatedEventHandler(da1Updated);
            da2.RowUpdated += new SqlRowUpdatedEventHandler(da2Updated);
            ds.Tables["Person"].Columns["PersonID"].AutoIncrement = true;
            ds.Tables["Person"].Columns["PersonID"].AutoIncrementSeed = -1;
            ds.Tables["Person"].Columns["PersonID"].AutoIncrementStep = -1;
            ds.Tables["Adresse"].Columns["AdressID"].AutoIncrement = true;
            ds.Tables["Adresse"].Columns["AdressID"].AutoIncrementSeed = -1;
            ds.Tables["Adresse"].Columns["AdressID"].AutoIncrementStep = -1;
            cb1 = new SqlCommandBuilder(da1);
            cb2 = new SqlCommandBuilder(da2);

            bs1 = new BindingSource();
            bs2 = new BindingSource();

            bs1.DataSource = ds;
            bs1.DataMember = "Person";

            dr = new DataRelation("Rel", ds.Tables["Person"].Columns["PersonID"], ds.Tables["Adresse"].Columns["PersonID"]);
            ds.Relations.Add(dr);

            bs2.DataSource = bs1;
            bs2.DataMember = "Rel";

            dataGridView1.DataSource = bs1;
            dataGridView2.DataSource = bs2;

            bindingNavigator1.BindingSource = bs1;
            bindingNavigator2.BindingSource = bs2;

            Binding btextBox1 = new Binding("Text", bs1, "PersonID");
            textBox1.DataBindings.Add(btextBox1);
            Binding btextBox2 = new Binding("Text", bs1, "Nachname");
            textBox2.DataBindings.Add(btextBox2);
            Binding btextBox3 = new Binding("Text", bs1, "Vorname");
            textBox3.DataBindings.Add(btextBox3);
            Binding bComboBox1 = new Binding("Text", bs1, "Titel");
            comboBox1.DataBindings.Add(bComboBox1);
            comboBox1.DataSource = ds.Tables["Titel"].DefaultView; // Nachzuschlagende Tabelle
            comboBox1.DisplayMember = "Titel"; // Welches Feld wird aus der nachzuschlagenden Tabelle ANGEZEIGT
            comboBox1.ValueMember = "Titel"; // Welches Feld wird aus der nachzuschlagenden Tabelle GESPEICHERT
            comboBox1.DropDownStyle = ComboBoxStyle.DropDownList;
            comboBox1.SelectedIndex = -1; // Damit wird beim Start unterbunden, dass versucht wird ein Feld zu finden, wo, evt. keines da ist
            //comboBox1.AutoCompleteSource = AutoCompleteSource.ListItems;
            Binding btextBox4 = new Binding("Text", bs2, "AdressID");
            textBox4.DataBindings.Add(btextBox4);
            Binding btextBox5 = new Binding("Text", bs2, "Strasse");
            textBox5.DataBindings.Add(btextBox5);
            Binding btextBox6 = new Binding("Text", bs2, "PLZ");
            textBox6.DataBindings.Add(btextBox6);
            //Binding bcheckBox1 = new Binding("Checked", bs2, "Test");
            //checkBox1.DataBindings.Add(bcheckBox1);
        }

        private void button1_Click(object sender, EventArgs e)
        {
            // Speichern
            this.Validate();
            dataGridView1.EndEdit();
            dataGridView2.EndEdit();
            bs1.EndEdit();
            bs2.EndEdit();
            DataViewRowState drvs = DataViewRowState.Added | DataViewRowState.ModifiedCurrent;
            foreach (DataRow item in ds.Tables["Person"].Select("", "", drvs))
            {
                System.Diagnostics.Debug.WriteLine(item["Vorname"].ToString());
                System.Diagnostics.Debug.WriteLine(item["Nachname"].ToString());
            }
            da1.Update(ds.Tables["Person"].Select("", "", drvs));
            foreach (DataRow item in ds.Tables["Adresse"].Select("", "", drvs))
            {
                System.Diagnostics.Debug.WriteLine(item["Strasse"].ToString());
            }
            da2.Update(ds.Tables["Adresse"]);
            da1.Update(ds.Tables["Person"]);

            MessageBox.Show("Gespeichert!");
        }

        private void button2_Click(object sender, EventArgs e)
        {
            // Abbrechen
            dataGridView1.CancelEdit();
            bs1.CancelEdit();
            dataGridView2.CancelEdit();
            bs2.CancelEdit();
            MessageBox.Show("Abgebrochen!");
        }

        private void button3_Click(object sender, EventArgs e)
        {
            da1.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            da2.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            da1.Fill(ds, "Person");
            da2.Fill(ds, "Adresse");
            da1.MissingSchemaAction = MissingSchemaAction.Add;
            da2.MissingSchemaAction = MissingSchemaAction.Add;
            MessageBox.Show("Aktualisiert!");
        }

        private void da1Updated(object sender, SqlRowUpdatedEventArgs e)
        {
            if (e.Status == UpdateStatus.Continue && e.StatementType == StatementType.Insert)
            {
                using (SqlCommand cmd = new SqlCommand("SELECT @@Identity", con))
                {
                    e.Row["PersonID"] = cmd.ExecuteScalar();
                }
            }
        }
        private void da2Updated(object sender, SqlRowUpdatedEventArgs e)
        {
            if (e.Status == UpdateStatus.Continue && e.StatementType == StatementType.Insert)
            {
                using (SqlCommand cmd = new SqlCommand("SELECT @@Identity", con))
                {
                    e.Row["AdressID"] = cmd.ExecuteScalar();
                }
            }
        }
    }
}

 

15 Answers Found

 

Answer 1

I am not really sure what are you missing here, but if I understood you well, you would like to show no items (zero, empty comboBox) when the dataSouce is empty, correct?

If so, please do the following:

if(comboBox1.DataSource != null)
{
  //do the code if there are items to display in the comboBox
}
else 
  comboBox1.Items.Clear(); //empty the comboBox

If this is not it, please tell more splecific what is your problem.

Mitja

 

Answer 2

Dear Mitja,

no thats not what I mean:

The items in its listing (in the listbox part of the combobox) are correct all the time. But I could solve me problem now. It's in the line:

comboBox1.DropDownStyle = ComboBoxStyle.DropDownList;

If I remark it...

//comboBox1.DropDownStyle = ComboBoxStyle.DropDownList;

... then combobox  will work fine, but without the remark it won't show an empty field (in the textbox part of the combobox not in the listbox) if an null fieldvalue in the datasource (during navigating throu the data rows) appears.

It seems to me like a bug because the old value still remains in the textbox part of the combobox even if the datasource field value changes to null.

Greetings,

Christoph

 

Answer 3

I dont understand you pretty well, can you please explain why you have to change from DropDownList to only DropDown (btw, DropDown is the same as your remark). Would you like to have an empty row in a comboBox to choose "nothing", or what? Sorry, but I really didnt get you this time. Please give me some more info.

Mitja

 

Answer 4

Dear Mitja,

 

"A ComboBoxdisplays a text box combined with a ListBox, which enables the user to select items from the list or enter a new value."

=> http://msdn.microsoft.com/en-us/library/system.windows.forms.combobox.aspx

This is, what I mean: TextBox as a part of the ComboBox. This is the position where the user can type text. If I set...

1. case: comboBox1.DropDownStyle = ComboBoxStyle.DropDownList;

... the user can't type text in the textbox of the combobox  it's like readonly.

But if I set:

2. case: comboBox1.DropDownStyle = ComboBoxStyle.DropDown; // (=> you are right, this is the same as remarking)

the user is possible to type text in and in this case the textbox shows all the time the right value.

And now I will make an example to show you the bug:

 

I navigate through the DataRows with bind ComboBox to Name f.e. that rows:

ID; Name

0; "Richard"

1; "Tom"

2; null

3; Dennis

 

=>Result, what diplays the bind textbox part of the ComboBox?

1. case (wrong):

Richard

Tom

Tom

Dennis

2. case (right):

Richard

Tom

nothing

Dennis

 

=> Thats my problem, why shows it Tom and not nothing?

 

Greetings from Christoph

 

Answer 5

Please show me the code of populating your comboBox?

Do you use a DataSource, ValueMember and DisplayMember?

Otherwise, you can populate comboBox "manully", with a code:

//I will pass a DataTable parameter to the method bellow, just to use it as an example, you populate it through sql query (I hope you do).

ComboBoxPopulation(DataTable table)
{ 
   comboBox1.DropDownStyle = ComboBoxStyle.DropDownList;
   foreach(DataRow dr in table.Rows)
   {
     string rowValue = dr[0].ToString() + ". " + dr[1].ToString();
     comboBox1.Items.Add(rowValue);  
   }
}

This code has to work!

If not let me know.

Mitja

 

Answer 6

Dear Mitja,

it doesn't, because I have no problem with the items (the items are correct in the listbox of the combobox  all the time (I can see them all and use them by opening the dropdown listbox)).

I do filling the listbox with items with this statement:

            comboBox1.DataSource = ds.Tables["Titel"].DefaultView; //  => this is for the dropdown listbox

I think the problem is the Binding:

            Binding bComboBox1 = new Binding("Text", bs1, "Titel"); //  => this is for the textbox
            comboBox1.DataBindings.Add(bComboBox1);

=> There is a binding object (bComboBox1) which binds the textbox of the ComboBox to the BindingSource and wth the bindingNavigator I navigate through the BindingSource (its bind to a DataRow in a DataSet):

bindingNavigator1.BindingSource = bs1;

Yes, I use DataSource, ValueMember and DisplayMember (the whole source code is):

            Binding bComboBox1 = new Binding("Text", bs1, "Titel");
            comboBox1.DataBindings.Add(bComboBox1);
            comboBox1.DataSource = ds.Tables["Titel"].DefaultView;
            comboBox1.DisplayMember = "Titel";
            comboBox1.ValueMember = "Titel";

I use "Titel" not "name" as in my example, but its the same.

Greetings,

from Christoph

 

Answer 7

Dear Mitja,

I made it now as simple  as possible, I worte a new example program, so you can see how it works, it has the same problem, with DropDownList it has a problem and with DropDown everything is ok. I still guess its a bug in the framework...

 

using System;
using System.Data;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace ComboBoxProblem
{
	publicpartialclass Form1 : Form
	{
		private SqlConnection con;
		private DataSet ds;
		private SqlDataAdapter da1;
		private SqlDataAdapter da2;
		private BindingSource bs1;

		public Form1()
		{
			InitializeComponent();
			con = new SqlConnection("Data Source=MIB-VM-ETS;Initial Catalog=PIS;Integrated Security=True;");
			con.Open();
			ds = new DataSet();
			da1 = new SqlDataAdapter("select * from person", con);
			da2 = new SqlDataAdapter("SELECT * FROM Titelvorschlaege ORDER BY Titel", con);
			da1.Fill(ds, "Person");
			da2.Fill(ds, "Titel");
			bs1 = new BindingSource();
			bs1.DataSource = ds;
			bs1.DataMember = "Person";
			// ComboBox
			Binding bComboBox1 = new Binding("Text", bs1, "Titel");
			comboBox1.DataBindings.Add(bComboBox1);
			comboBox1.DataSource = ds.Tables["Titel"].DefaultView; // Nachzuschlagende Tabelle
			comboBox1.DisplayMember = "Titel"; // Welches Feld wird aus der nachzuschlagenden Tabelle ANGEZEIGT
			comboBox1.ValueMember = "Titel"; // Welches Feld wird aus der nachzuschlagenden Tabelle GESPEICHERT//comboBox1.DropDownStyle = ComboBoxStyle.DropDownList;
			comboBox1.DropDownStyle = ComboBoxStyle.DropDown;
			// BindingNavigator
			bindingNavigator1.BindingSource = bs1;

		}
	}
}

Greetings,

from Christoph

 

Answer 8

Use only these rows of the code:

comboBox1.DataSource = ds.Tables["Titel"];
comboBox1.DisplayMember = "Titel";
comboBox1.ValueMember = "Titel";
because you bind comboBox wtih dataSet in the 1st row. and no need to use DefaultView on dataSource. Try it.

 

 

 

Answer 9

Hi Mitja,

ok I tried this ComboBox programming now:

// ComboBox
Binding bComboBox1 = new Binding("Text", bs1, "Titel");
comboBox1.DataBindings.Add(bComboBox1);
comboBox1.DataSource = ds.Tables["Titel"];
comboBox1.DisplayMember = "Titel";
comboBox1.ValueMember = "Titel";
comboBox1.DropDownStyle = ComboBoxStyle.DropDownList;

this program has still the same problem

Greetings,

Christoph

 

Answer 10

without 1st two rows! erase or comment 1st two rows.

adnd put the code where you declare that the comboBoc has DropDownList somewhere in the control load method, not on the end of the code.

 

Answer 11

Wait... if there is no value, you would like to show it as none (like empty string)?
 

Answer 12

Hi Mitja,

I tried this code now:

//Binding bComboBox1 = new Binding("Text", bs1, "Titel");//comboBox1.DataBindings.Add(bComboBox1);
comboBox1.DataSource = ds.Tables["Titel"];
comboBox1.DisplayMember = "Titel";
comboBox1.ValueMember = "Titel";
comboBox1.DropDownStyle = ComboBoxStyle.DropDownList;

Guess we have such DataRows:

ID;Titel

0;"Dr."

1;"Prof. Dr."

2;null

3;"Prof."

Then the result is:

Dr.

Dr.

Dr.

Dr.

If I navigate through the DataRows.

=> Thats clear because the textbox (the "Text" attribute) of the ComboBox is now no longer bound to the "Titel" field of the BindingSource bs1...

 

Greetings,

Christoph

 

Answer 13

Wait... if there is no value, you would like to show it as none (like empty string)?

Yes, thats right, if there is null  in the BindingSource field "Titel", then the "text" attribute of ComboBox should show nothing, too, because its bound to the BindingSource.

Greetings,

Christoph

 

Answer 14

Hi,

I've found a workaround but I don't like it:

=> Put a row with null  value in the table "Titel". And so the combobox  can find the corresponding field value for null (in the bindingsource bs1 in the table "Person" - because there are some guys without a title ;-)) in the table "Titel", like this:

table Person:

ID;name;title

0;"Richard";"Dr."

1;"Tom";"Prof. Dr."

2;"Frank";null

3;"Dennis";"Prof."

 

table Titel:

ID;title

0;"Dr."

1;"Prof."

2;"Prof. Dr."

3;null

 

=> If I navigate through the bs1 (table "Person") I 'll see the correct output in combobox title:

Dr.

Prof. Dr.

null

Prof.

 

Greetings,

Christoph


 

Answer 15

Hi there again,

I've found a better workaround for this problem (praise to God!):

			Binding bComboBox1 = new Binding("Text", bs1, "Titel");
			comboBox1.DataBindings.Add(bComboBox1);
			comboBox1.DataSource = ds.Tables["Titel"];
			comboBox1.DisplayMember = "Titel";
			comboBox1.ValueMember = "Titel";
			comboBox1.DropDownStyle = ComboBoxStyle.DropDown;
			// DataGridView
			dataGridView1.DataSource = bs1;
			// BindingNavigator
			bindingNavigator1.BindingSource = bs1;
		}

		private void comboBox1_KeyPress(object sender, KeyPressEventArgs e)
		{
			//MessageBox.Show("KeyPress");
			e.Handled = true;
		}

I use...

			comboBox1.DropDownStyle = ComboBoxStyle.DropDown;

and so the null  values are no problem, but the user normally is able to type text in. I prevent user from typing text with ...

			e.Handled = true
;

.. so it is only allowed to use the dropdown but it's not allowd to type anything in textbox of the combobox. The textbox appears like readonly similar than with ComboBoxStyle.DropDownList.

I've got this hint from Microsoft. ;-)

=> http://msdn.microsoft.com/en-us/library/system.windows.forms.control.keypress.aspx

If someone has another better workaround, any hints are welcome. ;-)

Greetings,

Christoph

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter