Wednesday, April 13, 2005

Combobox Databinding Woes

UPDATE: This post is for Visual Studio 2003. For Visual Studio 2005 content please see this post.

I've been noticing a lot of questions on the newsgroups related to Winforms databinding and the combobox and I thought I'd post something up here to help people out. There are a couple very common scenarios in which people use the combobox:

1. To display information from a lookup table and send the selected value into another table's field.

2. To display a list of parent table's records and use that as a filter to display related child records. For instance, as the user selects a record in the combobox, you want to display all the related child records in a grid.

As usual, the trick is setting up the data binding properly and using the currency managers. In the first case it's not necessary to set up a data relation in your dataset between the lookup table and the table you're editing, but it doesn't hurt. In the second case it is necessary to create a relation between your parent and child tables. Let's take an example from our beloved Northwind:

Private Const SQL_CONNECTION_STRING As String = _
 "Data Source=localhost;" & _
 "Initial Catalog=Northwind;" & _
 "Integrated Security=SSPI"

Try
 Dim ds As New DataSet
 Dim cnn As New SqlConnection(SQL_CONNECTION_STRING)

 Dim da As New SqlDataAdapter("SELECT * FROM Region", cnn)
 da.Fill(ds, "Region")

 da = New SqlDataAdapter("SELECT * FROM Territories", cnn)
 da.Fill(ds, "Territories")

 ds.Relations.Add("Region_Territories", _
  ds.Tables("Region").Columns("RegionID"), _
  ds.Tables("Territories").Columns("RegionID"))

 ds.DataSetName = "RegionTerritories"

Catch Exp As Exception
 MessageBox.Show(Exp.Message)
End Try
In the first scenario we want to select a Region from the combobox and have that value populated into the Territorries record. In this case you'll need to set up the following properties on your Combobox:
Me.ComboBox1.DataSource = ds.Tables("Region")
Me.ComboBox1.DisplayMember = "RegionDescription"
Me.ComboBox1.ValueMember = "RegionID"
These properties control what items are displayed in the combobox and what value is used when the user makes a selection. Now to get that value into the Territories table, you'll need to set up a data binding:
Me.ComboBox1.DataBindings.Add("SelectedValue", ds, "Territories.RegionID")
Okay we're all set, right? Well... not exactly. You'll also need to call EndCurrentEdit on the territories currency manager at some point in order to write the value back to the dataset. Depending on the style of your form you could do this from an "Update" button (similarly you could call CancelCurrentEdit from a Cancel button). However, when working with datasets I find it much easier to use the dataset methods for Accepting/Rejecting row changes. So 99.99% of the time I just call EndCurrentEdit from the SelectedIndexChanged event handler of the combobox itself:
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, _
  ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
 '-- This forces the comboxbox's value to be written to the dataset.
 Dim cm As CurrencyManager = DirectCast(Me.BindingContext(ds, "Territories"), CurrencyManager)
 cm.EndCurrentEdit()
End Sub
The cool thing (or anoying thing depending on how you look at it) about EndCurrentEdit/CancelCurrentEdit on the currency managers is that they cancel or commit only the fields in which they have bindings for where as the dataset rows' AcceptChanges/RejectChanges works on the whole row regardless of the data bindings. (It would be *really* nice if the currency manager had a property for this like "AlwaysCommitChanges" so we wouldn't have to call EndCurrentEdit all over the place.)

Now let's take our second scenario where we want to use the combobox as a row filter. In this case we have to have a relation set up between our parent and our child; in the example this is Region_Territories. The combobox properties can be set up just like the first example:
Me.ComboBox1.DataSource = ds.Tables("Region")
Me.ComboBox1.DisplayMember = "RegionDescription"
Me.ComboBox1.ValueMember = "RegionID"
Technically we don't need to specify the ValueMember property this time because we're not writing it anywhere, but it doesn't hurt to specify it. Next you'll need to set up the Datasource and DataMember properties of the DataGrid using the relation path. It is very important to get the path right otherwise the datagrid will not filter automatically as we move the position in the parent:
Me.DataGrid1.DataSource = ds
Me.DataGrid1.DataMember = "Region.Region_Territories"
Okay we're all set, right? Well... not exactly (I knew you were going to say that <g>). Unfortunately a combo box won't move the CurrencyManager's position for you like list controls do (Grids, Listboxes). So the trick is to get a hold of the parent currency manager and move the position manually by handling the combobox's SelectedIndexChanged event:
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, _
  ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
 Dim cmParent As CurrencyManager = DirectCast(Me.BindingContext(ds, "Region"), CurrencyManager)
 cmParent.Position = Me.ComboBox1.SelectedIndex
End Sub
Because you set up the datagrid to display the related territories by specifying the relation path Region.Region_Territories as the DataMember, the grid will automatically filter it's rows based on the selected parent row in the combobox.

The currency managers are your friends. You can obtain currency managers for any table/path in your dataset even if there are no control bindings set. You can also use the currency managers to disable controls when the position moves to -1 (no records) Here's an example. The currency managers maintain dataviews so you can easily access the current DataView as well as the current DataRowView:
Dim dv As DataView = DirectCast(cmParent.List, DataView)
Dim dvr As DataRowView = DirectCast(cmParent.Current, DataRowView)
Complex winforms databinding can take some practice, but once you get the hang of it you can create some very cool forms. Have fun!

30 comments:

Beth Massi said...

Yes, the fields are case sensitive. I acutally use a set of third-party controls where they all require case sensitivity and don't even throw exceptions! So I got into the habbit of making sure the fields matched case all the time. In fact, we code generate string constants based on all our entities' tables and fields and use those in code so we can catch field changes at compile time. VB's type level import really helps out in that case.

Beth Massi said...

I did get your point, sorry if I didn't articulate that. It's just that in the controls we use, *all* the fields are case sensitive so that's what prompted us to start code generating classes that declare constants for the field names in each entity. Then we import down to the type level and use the constants in our code. Doing that elimates this issues no matter what controls we use.

Anonymous said...

I'm trying to do almost the exact same thing but binding to an IList object rather than a DataSet. Do you know how to get this to work.

i.e I have a class along the lines of the following

class Controller
{

StringCollection States
{
get { return this._states; }
}

string CurrentState
{
get { return this._selectedState; }
set { this._selectedState = value;}
}

}

I've left out the code which fills the list and this._selectedState is initialised to a member of the States list.

I can bind a ComboBox to the States collection as follows.

Me.ComboBox1.DataSource = this._controller.States;

However I get an error which I try and bind the SelectedItem to the SelectedState, i.e.

Me.ComboBox1.DataBindings.Add("SelectedValue", this._controller, "SelectedState");

It complains about Me.ComboBox1.ValueMember not being set. How do you set ValueMember and DisplayMember when binding to IList?

Beth Massi said...

Combobox data binding can do the following:

1. Take a list (collection/table/view) and display those items

2. Take a list, display those items, and place the selected value into another object (row/object) which may be in another list.

The list can be anything that implements IList like a Dataset or a Collection. If you are creating custom bindable classes instead of the DataSet/DataTable, in order to enable complex binding/sorting/editing/etc. you need to implement another handful of interfaces (IBindingList, IEditableObject, etc.) If you're doing simple binding (just take the value of one object and put it in another) then implementing those interfaces is not necessary, you just need an object that implements IList to display in the combobox.

So in your example it looks like you are tring to bind the selected value in a contained StringCollection back into a property of the main object itself. As far as I know you can't use the StringCollection for anything more than displaying values. For instance, the combobox won't let you set its ValueMember to "Item" to indicate the StringCollection.Item and it doesn't look like you can set up the binding to the Text property on the Combobox either.

So to get two-way binding you should create a collection of objects that have value and display properties so you can specifically set those to the ValueMember and DisplayMember properties of the Combobox. Of course, the DisplayMember and ValueMember can be the same. So if you create a structure like this:

Public Structure State
Private m_name As String

Public Sub New(ByVal state As String)
m_name = state
End Sub

Public ReadOnly Property Name() As String
Get
Return m_name
End Get
End Property
End Structure

Then in your controller object you could populate and expose an array of these structures:

Public Class Controller

Private m_states() As State = {New State("California"), New State("Florida")}
Private m_selected As String

ReadOnly Property States() As State()
Get
Return m_states
End Get
End Property

Property SelectedState() As String
Get
Return m_selected
End Get
Set(ByVal Value As String)
m_selected = Value
End Set
End Property
End Class

Now you can set up the databinding:

Me.ComboBox1.DataSource = Me._controller.States
Me.ComboBox1.ValueMember = "Name"
Me.ComboBox1.DisplayMember = "Name"

Me.ComboBox1.DataBindings.Add("SelectedValue", _controller, "SelectedState")

Anonymous said...

Thank you very much for that. One final question, is there any way to control when the ComboBox updates SelectedState (or force it to)? The problem I've got now is that it seems to perform the update when the ComboBox loses focus rather than when the user selects a new item. The problem is I'm using the SandBar UI components and clicking on a toolbar button does not take focus away from the ComboBox so the controller doesn't get updated. I'm going to post this to SandBar as a bug but I'm guessing there's a method to force the update?

Regards

Dave

Beth Massi said...

Just like I explained in my post (scenario 1) you're going to need to call EndCurrentEdit. In your case, however, instead of getting back a CurrencyManager, you'll get a PropertyManager. They both inherit from the same base class BindingManagerBase which exposes EndCurrentEdit.

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
'-- This forces the comboxbox's value to be written to the object.
Dim bmb As BindingManagerBase = Me.BindingContext(Me._controller)
bmb.EndCurrentEdit()
End Sub

Anonymous said...

I knew I'd read that somewhere, I should have scrolled up :)

Thanks very much for the help.

Dave

Anonymous said...

This line

cbTaskLookUp.DataSource = dsTask1.Tables("Task");

returns the error

"System.Data.DataSet.Tables denotes a 'property' where a 'method' was expected"

Does this represent a difference between C# and VB or is there another problem here?

Beth Massi said...

I believe the C# syntax is:

cbTaskLookUp.DataSource = dsTask1.Tables["Task"];

Anonymous said...

Nice info. I think there is a slight problem though, when databinding the selectedvalue property.
What happens when the selectedvalue is not present in the values obtained from the datasource (like for example when getting a DBNull)?
I have made the experiment of adding Displaymember, valueMember and Datasource properties (from an SQLServer table) to the combobox. Then I have a textbox and a button, when I press the button it simply changes the selectedvalue of the combobox to the text of the textbox. When the value is present in the list in the combobox, the combobox presents the right row. Guess what, if the value is not present then it chooses the first row of the combobox, if I click the button a second time then it puts the combobox blank (which is what it should have done first time...).

It all came about because I have a windows form that I am databinding, and one of the fields is a foreign key to another table, so I want to use a combobox to help select the values. But the field is nullable, so when you are browsing (moving the currencymanager position up or down) and it first encounters a DBNull instead of displaying blank it shows the first value (row) of combobox, if the next record has also null, it then goes blank...
Funnily enough doing addnew works, and makes the combobox blank...
Do you know a solution? Thx.

Beth Massi said...

What you have to do is add a row the the combobox's datasource that has a value of DBNull and a display of String.Empty.

Anonymous said...

Yes, of course, that works because we make the value present and the problem only occurs when the value is not present. Nevertheless I can't have DBNull in a field that's primary key. Of course I could circumvent that by creating a "fake" null, defaulting the value of the foreign key field to some value which I introduce in my datasource table. Then I have to also make sure that nobody can erase that row.
I am quite certain that there is a bug with the combobox. I have been playing around with selectedindex, but it suffers from the same syndrome. If you try to set it to -1, it sets itself to 0 (unless you are in 0, then it accepts -1). Very strange...

Anonymous said...

Nice article!

I have a case that I cannot quite figure out, though, and I really hope you can help me out. I have a strongly typed dataset where table A is the parent of table B, and table B also contains foreign keys to table C (C being another parent of B with a 1-1 relation). I have 2 datagrids set up in a master-detail relation where the master is set up with the table A as datasource, and the details datagrid is set up with B as the datasource, somewhat like this (in C#):

this.dgMaster.SetDataBinding(this.myTDS, this.myTDS.tblA.TableName);
this.dgDetails.SetDataBinding(this.myTDS, "tblA.tblA_tblB");

This works just fine, but what if I want to display and edit fields from table C in a textbox? How do I set up this databinding? I tried a lot of different ways, e.g. something like this;

this.fieldFromCTextBox.DataBindings.Add("Text", this.myTDS, "TblA.tblA_tblB.tblB_tblC.myField");

But no matter what I'd tried I always end up with the error message: "Cannot create a child list for field tblB_tblC" - which is understandable as the table C isn’t a child of B, but it's parent. But how do I maneuver my way up to this parent?

Have a nice day :)

Beth Massi said...

Since it's a one-to-one relationship why don't you try adding a DataRelation between B and C called TblB_TblC making table B the parent. That way you could bind the textboxes to TblA.TblB_TblC.FieldName just like you would a grandchild. See if that works.

Anonymous said...

Off course! Badjeeze... Sometimes the obvious makes you blind :)

But as I started to work on this parent-child-parent relation and trying to find a navigation path for this, I'm a bit curious if it could be done at all. I've posted a similar post on MSDN Managed Newsgroups with the title Navigation path for “2 parents / 1 child”-relations (under microsoft.public.dotnet.framework.windowsforms.databinding). So far no answers so I'll try a post here as well. This is the case;

Design a strongly typed dataset based on the 3 tables “Orders”, “Order Details” and “Products” with their keys and relations. Add 2 datagrids to a windows form and set up a master/detail relation based on “Orders” and “Order Details”. The c# way of doing this would be for instance;

this.dgMaster.SetDataBinding(this.myTDS, this.myTDS.Orders.TableName);
this.dgDetails.SetDataBinding(this.myTDS, "Orders.OrdersToOrderDetails");

…where “Orders.OrdersToOrderDetails” would be the parent-child relation between the two tables. This works just fine – no problem at all.

Now, what do I have to do to if I add a textbox to the form and I want this textbox to bind up to the “ProductName” field in the “Products” table? I cannot figure out the correct navigation path in the databindings method for the textbox;

this.tbProductName.DataBindings.Add("Text", this.myTDS, "???????.ProductName");

Even though the changing of relation to a grandchild relation solved my previous problem, I still wonder how the navigation path for a parent-child-parent relation would look like.

I know one could make a workaround for this by adding expression columns to the child table, but this would make the column read-only. For most cases this makes perfect sense, but there could be cases where you actually want to be able to change the content of this field, and in these cases an expression columns want do the trick.

Anonymous said...

Databinding a ComboBox in dropdown mode has proved troublesome. In dropdown mode the user must be able to either select from a list or type in a value which is not included in the list.
In order to do this the Text property of the combobox is used for databinding, since using SelectedValue will not work for a user-created value. In this case the databinding only recognizes text which is typed in - not text which appears as a result of selection from the list. This can be worked around by using SendKeys.

Private Sub MyCombo_SelectedIndexChanged(ByVal Sender As Object, ByVal e As EventArgs) handles MyCombo.SelectedIndexChanged

SendKeys.Send(MyCombo.SelectedValue)
End Sub

So far, so good. However, if the ComboBox is contained in one TabPage of a TabControl and the user switches to another TabPage and then back again the value which appears in the text portion of the control will be:
a) The value for list index 0 if the bound value is a list value,
b) The correct value if the bound value is a non-list value,
c) The selected value if the user has selected a value before switching between tabpages.



Any thoughts?

Anonymous said...

I have another issue. I have three comboboxes on a form and they are all using the same datasource, displaymember and valuemember but the SelectedValue is bound to three different fields. When I select an entry in any of the comboboxes, the other two automatically change their value as well. Any comments?

Anonymous said...

Found my solution:

Use multiple dataview objects and use them to feed the datasource/displaymember/valuemember fields.

Rivetgeek said...

I know this is a bit old, but I have an interesting problem. I have a textbox and a combobox, bound to the same dataset. I then have a function that performs some calculations, which both the textbox and the combobox would ideally call whenever their values are changed. It looks something like this:

Private Sub txtarmorrating_validated(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtArmorRating.Validated

ErrorProvider1.SetError(txtArmorRating, "")
me.BindingContext(SilCore1.designs).EndCurrentEdit()
armorlabels(calc.CalcMeasure(SilCore1.designs.Rows(0)("armor"), "armor"))
calcDTV()
End Sub

Private Sub cmbMan_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbMan.SelectedIndexChanged
If SilCore1.designs.Rows.Count > 0 Then
Me.BindingContext(SilCore1.designs).EndCurrentEdit()
calcDTV()
End If
End Sub

Public Function calcDTV()
Dim dtv As Double
Dim air, airstall, ground, hover, naval, rail, zerogee, submarine, walker As Double
Dim manuever As Double
Dim armor As Double

air = SilCore1.designs.Rows(0)("air")
airstall = SilCore1.designs.Rows(0)("air_stall")
ground = SilCore1.designs.Rows(0)("ground")
hover = SilCore1.designs.Rows(0)("hover")
naval = SilCore1.designs.Rows(0)("naval")
rail = SilCore1.designs.Rows(0)("rail")
zerogee = SilCore1.designs.Rows(0)("zerogee")
submarine = SilCore1.designs.Rows(0)("submarine")
walker = SilCore1.designs.Rows(0)("walker")
armor = SilCore1.designs.Rows(0)("armor")
manuever = SilCore1.designs.Rows(0)("manuever")
'manuever = cmbMan.SelectedValue

dtv = Math.Round(((armor ^ 2) + (air - airstall) ^ 2 + ground ^ 2 + hover ^ 2 + naval ^ 2 + rail ^ 2 + zerogee ^ 2 + submarine ^ 2 + walker ^ 2) * manuever, 2)
SilCore1.designs.Rows(0)("dtv") = dtv
Me.BindingContext(SilCore1.designs).EndCurrentEdit()

CmbMan draws from a lookup table and the SelectedValue gets written to the dataset; the textbox's contents get written to the same dataset but a different field.

The problem is that none of the textboxes on the entire form will validate when I have calcDTV() in the selectedindexchanged event of cmbMan. It won't let me move off of them if I input anything. If I clear the input and select something in cmbMan, everything works fine. If I don't call calcDTV() from the SelectedIndexChanged event, everything works fine (except the dataset doesn't get updated with the SelectedValue immediately, which throws off the calculation in calcDTV()).

The armorlabels function called in the validated event does not modify anything in the dataset at all (it just sets up some display labels).

As you can see, the manuever and armor variables in calcDTV() are the fields from the dataset that I use for the calculations. Am I missing something simple? I suspect that is the problem, but can't seem to be able to ferret out why.

Beth Massi said...

In the first scenario I describe you would just set up the databinding to the datatable directly:

Me.ComboBox1.DataBindings.Add("SelectedValue", ds.Tables("Territories"), "RegionID")

If you're using VS2005 however you would use a BindingSource:

Me.TerritoriesBindingSource.DataSource = ds.Tables("Territories")

Me.ComboBox1.DataBindings.Add("SelectedValue", Me.TerritoriesBindingSource, "RegionID")

Anonymous said...

Here's a link to a C# resolution to the combobox databinding woes!

http://www.breakoutseason.com/derrick_ribilla/simple_combobox_databinding_windows_forms_binding_source_problem_solution.html

Anonymous said...

Here's a variation:

I've got a custom class that I want to bind to a combo box.

The code:

Private mCaseItem As CrimBO.GJCaseBO
cboCasePros.DataBindings.Add("SelectedValue", mCaseItem, "ProviderID")

Additional notes:

CrimBO.GJCaseBO is a custom class that holds Grand Jury Case Information.

ProviderID is a public property on the custom class.

cboCasePros is a combobox of Prosecutors.

The error I get is "Cannot bind to the property or column ProviderID on the DataSource."

Thanks for any help!

Anonymous said...

Oops! I figured it out. The property is ProsecutorID, not ProviderID. Sometimes....

Anonymous said...

what if there is another level of child i.e. Group-->Category-->Types?
I tried this approach & worked for group & category but failed for type? can't I set three dependant objects?

thanks,
sandeep

Unknown said...

reguarding
>> Anonymous said...
>> Here's a link to a C# resolution to the combobox databinding woes!
>>http://www.breakoutseason.com/derrick_ribilla/simple_combobox_databinding_windows_forms_binding_source_problem_solution.html

I also have to set the CurrencyManager in the _SelectedIndexChanged even under 2005 (c#) if i use BindingSource right?

Anonymous said...

Thanks for the article. I am attempting to do exactly what you have in your scenario 1 except in c#. Any idea how to do
Dim cm As CurrencyManager = DirectCast(Me.BindingContext(ds, "Territories"), CurrencyManager)
in c#?

thanks again, Kevin

Anonymous said...

CurrencyManager cm = (CurrencyManager)this.BindingContext[ds, "Territories"];

Anonymous said...

what
convert blu-ray to psp is blu-ray to psp Converter the convert blu-ray to wmv reason
blu-ray to wmv converter It convert dvd to apple tv for mac is mac dvd to apple tv converter not convert dvd to avi for mac elegant? Be
mac dvd to avi converter familiar convert dvd to flv for mac with

Anonymous said...

with
mac dvd to flv converter her convert dvd to gphone for mac know mac dvd to gphone converter that convert dvd to ipod for mac these mac dvd to ipod converter are
convert dvd to iphone for mac only mac dvd to iphone converter people's convert dvd to ipod for mac subjective mac dvd to ipod converter assumptions
convert dvd to mp3 for mac Bale.She mac dvd to mp3 converter is
convert dvd to mp4 for mac the mac dvd to mp4 converter company's
convert dvd to mpeg for mac portersmac dvd to mpeg converterlife.

lovemayday said...

How to Convert AVI to iMovie Mac?

You must have came across lots of problems while you import AVI to iMovie Mac, The solution is to convert AVI to iMovie on Mac compatible format.My favorite tool for accomplishing this task is Daniusoft Video Converter for Mac. It can Convert AVI to iMovie MacBelow is the step by step guide on how to Convert AVI to iMovie on Mac

After finishing all necessary settings, click Start Conversion button to start converting AVI files to iMovie on Mac. That's it. With Mac AVI to iMovie converter, you can easily convert AVI to iMovie on Mac, import AVI to iMovie Mac (iMovie HD, iMovie 08/09) and then have fun with editing AVI on Mac.