I'm posting this for archive, for benefit of other beginners, because I had trouble finding a solution to this. I'm coming from solid Access background, trying to rebuild an access database application, struggling with .net's strange new hoops.
In previous versions of VB (i.e. Access, VB6, etc), binding a combobox was rather easy. You could do it without coding anything, much less a whole rigamorole with connections, datasets, and dataadapters. If you use a regular combo box, the process is still similar to the old (easy) way, but limited. For example, you cannot easily list multiple fields, while keying on a different one. An example of when you need that, is if user is choosing company name from the combobox--internally, you want to use the UID (key field) of that company, not its text name. It's not easy and obvious how to do this in .net.
The simplest way to show one field (e.g. company name) and key on another (e.g. companyID) is to bind the combobox to a dataset, via a dataadapter. If this sounds like chinese to you (as it did to me for many excruciating hours), here's a simple explanation: A Dataset is a representation or a mapping or a handle onto some portion or portions of a database. This is the second layer in the communication with the database, using the Connection, and serving a DataAdapter. So for example, you can put in a dataset "select * from companies" and/or "select uid, companyid,amount from orders where companyid=4", and so on. The Dataadapter is a memory-based replica of what's in the dataset (roughly like arrays of records), and it's this layer with which you deal with the data (reading or modifying). You have to fill up the Dataadapter manually, and after you make changes to data in the dataadapter, you have to initiate the updating of the dataset, which then makes the changes back to the server.
(I'm oversimplifying for sure, and certainly for enterprise stuff, writing to database without using triggers and other stored procedures is a no-no. But my interest is in RAD, for small business. I'm talking about the quickest and simplest route from data to app.)
When you use toolstripcombobox, the way to key on an ID field is even more obscure. For some reason a combobox in the toolstrip doesn't expose the .datasource method. You have to use cboMyComboBox.COMBOBOX.DataSource instead.
Here's my code for a rudimentary navigation class, which uses two comboboxes in the toolstrip:
- tsCompany is the first combobox in the toolstrip, lists company names. It controls the second combobox's contents
- tsBillMo is the second combobox, listing all the months for which the selected company had billings
When user changes selected tsCompany, it must reconstruct tsBillMo's list. Do this by handling the tsCompany.SelectedIndexChanged event, and calling RefreshBillingMonths() when it fires. I put this navigation stuff in a navigation class:
One important note. At one point the comboboxes were filling with multiple copies of the intended lists. It took me hours to find the cause. The SelectedIndexChanged event fires three times before the form displays, and so (for some reason) the list was aggregating to have 3 copies. All attempts to manually clear the combobox list before refilling were unsuccessful; had no effect. The problem was obscure: I had moved the DataAdapter and DataSet variables to be class-global (and thus persistent), and somehow this caused the list items to accumulate rather than overwrite the old ones. The code above works correctly.
Note also, that you could fill the combobox manually, rather than binding them, using this code to replace the three lines beginning with Form1.tsBillMo..., with this:
That works to fill the list, but again, the key field is lost, and you'd have to look up that keyfield using the textual-date selected (ugly! problematic!). I think a more formal way is to make a custom object (a class) which you use in place of "s" above, which would hold (and properly return) both fields. In any case, that's pretty far from "R"AD. (Compared to Access or other RAD environment)
Hope that helps someone.
netnewb
In previous versions of VB (i.e. Access, VB6, etc), binding a combobox was rather easy. You could do it without coding anything, much less a whole rigamorole with connections, datasets, and dataadapters. If you use a regular combo box, the process is still similar to the old (easy) way, but limited. For example, you cannot easily list multiple fields, while keying on a different one. An example of when you need that, is if user is choosing company name from the combobox--internally, you want to use the UID (key field) of that company, not its text name. It's not easy and obvious how to do this in .net.
The simplest way to show one field (e.g. company name) and key on another (e.g. companyID) is to bind the combobox to a dataset, via a dataadapter. If this sounds like chinese to you (as it did to me for many excruciating hours), here's a simple explanation: A Dataset is a representation or a mapping or a handle onto some portion or portions of a database. This is the second layer in the communication with the database, using the Connection, and serving a DataAdapter. So for example, you can put in a dataset "select * from companies" and/or "select uid, companyid,amount from orders where companyid=4", and so on. The Dataadapter is a memory-based replica of what's in the dataset (roughly like arrays of records), and it's this layer with which you deal with the data (reading or modifying). You have to fill up the Dataadapter manually, and after you make changes to data in the dataadapter, you have to initiate the updating of the dataset, which then makes the changes back to the server.
(I'm oversimplifying for sure, and certainly for enterprise stuff, writing to database without using triggers and other stored procedures is a no-no. But my interest is in RAD, for small business. I'm talking about the quickest and simplest route from data to app.)
When you use toolstripcombobox, the way to key on an ID field is even more obscure. For some reason a combobox in the toolstrip doesn't expose the .datasource method. You have to use cboMyComboBox.COMBOBOX.DataSource instead.
Here's my code for a rudimentary navigation class, which uses two comboboxes in the toolstrip:
- tsCompany is the first combobox in the toolstrip, lists company names. It controls the second combobox's contents
- tsBillMo is the second combobox, listing all the months for which the selected company had billings
When user changes selected tsCompany, it must reconstruct tsBillMo's list. Do this by handling the tsCompany.SelectedIndexChanged event, and calling RefreshBillingMonths() when it fires. I put this navigation stuff in a navigation class:
VB.NET:
Public Class Navigation
Const datafile As String = "D:\data.mdb"
Dim con As New OleDb.OleDbConnection
Private Sub LoadBillingMonths()
' load up month selectors
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim sql As String
Dim s As String
Dim conum As Integer
conum = Form1.tsCompany.SelectedItem.item(0) '0= first column; key field
s = Form1.tsCompany.SelectedItem.item(1) '1=second column; display field
con.Open()
sql = "select uid,billdate from billings where company=" + _
conum.ToString + " order by billdate desc"
OleDb.OleDbDataAdapter(sql, con)
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "BillingDates")
con.Close()
Form1.tsBillMo.ComboBox.DataSource = ds.Tables("BillingDates")
Form1.tsBillMo.ComboBox.ValueMember = "uid"
Form1.tsBillMo.ComboBox.DisplayMember = "billdate"
End Sub
Private Sub LoadCompanies()
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim sql As String
Dim cname As String
con.Open()
sql = "Select * from companies"
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "CompanyList")
con.Close()
Form1.tsCompany.ComboBox.DataSource = ds.Tables("CompanyList")
Form1.tsCompany.ComboBox.ValueMember = "id"
Form1.tsCompany.ComboBox.DisplayMember = "cname"
End Sub
Public Function RefreshBillingMonths() As Boolean
' load up dates, after companies change
LoadBillingMonths()
End Function
Public Function Initialize() As Boolean
' load up data first time
con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source=" + _
datafile
LoadCompanies()
Form1.tsCompany.SelectedIndex = 0 ' this should default to last one
LoadBillingMonths()
Form1.tsBillMo.SelectedIndex = 0
End Function
End ClassOne important note. At one point the comboboxes were filling with multiple copies of the intended lists. It took me hours to find the cause. The SelectedIndexChanged event fires three times before the form displays, and so (for some reason) the list was aggregating to have 3 copies. All attempts to manually clear the combobox list before refilling were unsuccessful; had no effect. The problem was obscure: I had moved the DataAdapter and DataSet variables to be class-global (and thus persistent), and somehow this caused the list items to accumulate rather than overwrite the old ones. The code above works correctly.
Note also, that you could fill the combobox manually, rather than binding them, using this code to replace the three lines beginning with Form1.tsBillMo..., with this:
VB.NET:
Form1.tsBillMo.Items.Clear()
Dim cnt As Integer
For cnt = 0 To ds.Tables("BillingDates").Rows.Count - 1
s = ds.Tables("BillingDates").Rows(cnt).Item(1)
Form1.tsBillMo.Items.Add(s)
Next cntHope that helps someone.
netnewb
Last edited by a moderator:
)
