Link/Synchronize combobox and textbox

[routman]

Hi all,

I am fairly new to VB.NET and I am VERY new to all of this SQL/ADO stuff and need help. I have a SQL DB table that has 5 fields... ID, Product Type, Product Name, Product Code and Product Description

I have a windows form with a combobox and textbox on it. I am populating the combobox with the Product Code field contents using VB code (I think this is called "unbound"?) based on a SELECT filter (see code below). I would like to populate the textbox with the Product Description from the same record that was selected in the combobox.

I thought this would be as easy as setting the DataBindings:Text property of the textbox to the same binding source I am using to populate the combobox, but it does not work. I get an initial value in the textbox, but the textbox value does not change when a new value is selected in the combobox.

Dim strSQL As String = "SELECT [Product Code] FROM ProductCodes WHERE [Product Type] like '%DT%'"
Dim da As New SqlDataAdapter(strSQL, conn)
Dim ds As New DataSet
da.Fill(ds, "ProductCodes")

With cboProdCode[INDENT].DataSource = ds.Tables("ProductCodes")
.DisplayMember = "Product Code"
.ValueMember = "Product Code"
.SelectedIndex = 0
[/INDENT]
End With

Can someone give me some tips on getting this to work?

Thanks in advance!

 

[jmcilhinney]

I think this is called "unbound"

It's the opposite, i.e. you are binding the data. Data-binding means creating a relationship between the data in a control and the data in a list of some sort such that any changes in one will be propagated to the other. By setting the DataSource of any control you are binding it. Obviously using the DataBindings property is data-binding.

So, first things first, your DataSet is pointless. If you're only using a single DataTable then just create a single DataTable. Secondly, while it's a small thing, you should set the DisplayMember and ValueMember before the DataSource. Also, there's no piint setting the SelectedIndex to zero. That is selecting the first item in the list, which is done implicitly anyway. If you want to select no record then you have to set it to -1.

Dim table As New DataTable

adapter.Fill(table)

With cboProdCode
    .DisplayMember = "Product Code"
    .ValueMember = "ID"
    .DataSource = table
End With

myTextBox.DataBindings.Add("Text", table, "Product Description")

Note that this requires that you actually include the columns you want to display in your SQL query, which you currently aren't. Note that I have also set the ValueMember to "ID", which is the logical option if that is the primary key column.

Finally, while it is legal, I strongly recommend that you don't include spaces and other special characters in your column names. It's just too easy for them to cause issues. You should treat identifiers in SQL the same way as identifiers in VB.

 

[routman]

Thanks jmcilhinney!

That is some great advice! As I said, I am very new to ADO/SQL. Trying to do some simple things with a SQL database.

I was thinking that "bound" referred to controls that are bound through their properties at design time and "unbound" was for controls that are bound with code at run-time. Thanks for clearing that up!

I went ahead and renamed the columns using an underscore where the spaces were. Is that sufficient to prevent potential issues?

Can you give me some pointers on filling the datatable with the columns I need. I assume I will need the ID, Product_Code and Product_Description colums as those are the three that contain the contents I am concerned about. I am trying to read as much as possible about this, but there is so much info and so many different ways of doing the same thing (it seems), that I am not sure if I am headed in the right direction.

 

[routman]

I think I have gotten this figured out (with some questions)...

I tried the code you posted, but kept getting a "Cannot bind to the property or column..." error when I ran the code. After doing some searching on that error, I discovered a thread from about 6 years ago where ironically enough someone else was trying to do a similar thing and you (or at least someone with your name) helped them out! hehe

[RESOLVED] How to bind a text box to a dataset - VBForums

That thread actually helped me to piece all of this together!

After re-reading your post a few times, I realized that you were talking about my SELECT statement not including all of the necessary columns! So I just changed it to selecting values from all of the columns rather than just the "Product_Code" column.

Now it works with a couple of minor exceptions...

1) There seems to be a disconnect between the selected row value in the combobox (populated by Product_Code field) and the displayed row value in the textbox (populated by Product_Description field). For some reason they are not in sync. How can I correct this?

2) Is there a way to make the textbox not display anything until a value is selected in the combobox? Like with the .SelectedIndex = -1 property of the combobox? As it is now, when the form displays, there is an initial value in the textbox.

Also to comment on one of your tips...
When I tried setting the .DataSource prior to the .DisplayMember and the .ValueMember I was only getting "System.Data.DataRowView" in the combobox. When I put the .DataSource first, it populated the combobox correctly. Not sure what the deal is with that.


Thanks again for your help!

 

[routman]

Think I figured both of these issues out...

1) Was due to having the Product_Code combobox Sorted property set to True. The values in the DB rows are out of order, so I think it was throwing things off. Setting it to False corrected the issue.

2) I figured this out by clearing the DataBindings if there is nothing selected in the cboProdCode combobox. It's still a little quirky, but I will play with it some more to see if I can clean it up.

Thanks!