Comples Databinding - ComboBox
I am having trouble getting databinding to work with a ComboBox.
The problem is the translation doesn't take affect. The ValueMember (Foreign Key ID) shows up instead of the translated description (DisplayMember) when I bind to 'Text'. When I bind to 'SelectedValue', the proper entries are in the drop down list but nothing is in the text portion of the control. It appears to not choose any of the available values according to the current record. Here is my test code:
Dim oConn As New SqlClient.SqlConnection(MDIfrm.sConStr)
Dim SB As New System.Text.StringBuilder()
Dim ColKeys(1) As DataColumn
Dim DS As New DataSet()
Dim oDepts() As Department = {New Department("Accounting", 1), _
New Department("Programming", 2), New Department("HR", 3)}
SB.Append("SELECT * FROM Employees")
Dim DA As New SqlClient.SqlDataAdapter()
oConn.Open()
Dim oCmd As New SqlClient.SqlCommand(SB.ToString, oConn)
oCmd.CommandType = CommandType.Text
DA.SelectCommand = oCmd
DA.Fill(DS)
Me.ComboBox1.DisplayMember = "DepartmentName" 'Class property
Me.ComboBox1.ValueMember = "DepartmentID" 'Class property
Me.ComboBox1.DataSource = oDepts 'Array of Department Objects
Me.ComboBox1.DataBindings.Add("SelectedValue", DS.Tables(0), "DeptID")
The datasource (arDepts) is an array of Department objects. Department is a simple class that merely contains two properties, DepartmentName and DepartmentID. DeptID is an actual field in the Employees table. It is the Foreign Key to the Departments table.
I have tried numerous sites, articles and books. It looks like it should work, nothing stands out in error. It simply doesn't reflect current record in teh ComboBox.
Any help is greatly appreciated.
Sorry, it's unclear (at least to me) what you're trying to do. You're binding the combobox to an array of Department items, yet you're attempting to do some sort of binding to a database table, as well.
Can you demonstrate and describe what you're trying to do with the Northwind sample, so I (or others) could try it out? I don't see much difference between the data structures, and being able to actually try it out would help get it solved.
I tried to replicate the above scenario, and this is what I came up with. All seems well..
private void Form1_Load(object sender, System.EventArgs e) {
DataSet dsOrders = new DataSet();
cnProducts.Open();
string sql = "select employeeid, lastname + ', ' + firstname from employees";
SqlDataReader reader = new SqlCommand(sql, cnProducts).ExecuteReader();
ArrayList employees = new ArrayList();
while(reader.Read()) {
employees.Add(new ListItem((int)reader[0],(string)reader[1]));
}
cnProducts.Close();
SqlDataAdapter adOrders = new SqlDataAdapter("select * from orders", cnProducts);
adOrders.Fill(dsOrders, "Orders");
dataGrid1.SetDataBinding(dsOrders, "Orders");
this.comboBox1.DataSource = employees;
this.comboBox1.DisplayMember = "Description";
this.comboBox1.ValueMember = "ID";
this.comboBox1.DataBindings.Add("SelectedValue", dsOrders, "Orders.EmployeeID");
}
private class ListItem {
private string description;
private int id;
public ListItem(int id, string description) {
this.description = description;
this.id = id;
}
public string Description {
get {return description;}
}
public int ID {
get {return id;}
}
}
Hi all, thx for your response(s).
While I was considering the best answer to Ken regarding joining a list of valid values to the current record in another datasource (and perhaps the requirement of it), I had not had the chance to get back here.
Simon, are you saying that by xlating my code to C# and pointing to Northwind, you actually have a working model where the current record dictates which, in a list of valid values, is shown in a combo box? I was never able to get that to work. I will place your code in a form and try to understand why it doesn't work in my own project.
Thx for your reply!
This is working for me as well. I think the part that had a problem wasn't posted. Here's what I have (I used Northwind's Employees table, and added a DataGrid to the form so I could see the data)
Dim DS As DataSet
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim ColKeys(1) As DataColumn
Dim DS As New DataSet()
Dim oDepts() As Department = {New Department("Accounting", 1), New Department("Programming", 2), New Department("HR", 3)}
Dim DA As New SqlClient.SqlDataAdapter()
SqlDataAdapter1.Fill(DS)
Me.ComboBox1.DisplayMember = "DepartmentName" 'Class property
Me.ComboBox1.ValueMember = "DepartmentID" 'Class property
Me.ComboBox1.DataSource = oDepts 'Array of Department Objects
DS.Tables(0).Columns.Add("DeptID")
Dim i As Integer
For i = 0 To DS.Tables(0).Rows.Count - 1
DS.Tables(0).Rows(i)("DeptID") = i Mod 3 + 1
Next
Me.ComboBox1.DataBindings.Add("SelectedValue", DS.Tables(0), "DeptID")
DataGrid1.DataSource = DS.Tables(0)
End Sub
Class Department
Dim name As String
Dim id As Integer
Sub New(ByVal s As String, ByVal i As Integer)
name = s
id = i
End Sub
Property DepartmentName() As String
Get
Return name
End Get
Set(ByVal Value As String)
name = Value
End Set
End Property
Property DepartmentID() As Integer
Get
Return id
End Get
Set(ByVal Value As Integer)
id = Value
End Set
End Property
End Class
Ok, I got the Combo Box to properly reflect which in a list of valid values is the correct value in the corresponding recordset. I found that DataBinding chokes when there is a value in the main recordset that is not contained in the valid list that you are trying to link to. I got it to work by mass updating all records to a known good value (on that field) on a whim.
The problem I am having now is that although adding one to the BindingContext object goes to the next record and all of the other associated controls (TextBoxes and DatePickers) are automatically updated/reflected, the Combo Box stays on the value that the first record had. It does not update to any subsequent record.
I have seen some examples where the DataBinding is cleared and re-established for each record. I have a problem with that when, again, all the other controls update properly by merely incrementing the BindingContext object's Position.
Any suggestions?
Thx again for all your attention.
