Solution: How to use a UID field in a toolstripcombobox

Status
Not open for further replies.

netnewb

Member
Joined
Feb 4, 2008
Messages
9
Programming Experience
10+
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:


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 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:

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 cnt
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
 
Last edited by a moderator:
Data Walkthroughs at MSDN is a good place to start learning data access the VB 2005 way, there are also many good data access learning videos, for visual development it is often easier to see how it's done than read about it. When you have learned and configured data access properly you will have some TA.FillBy methods that will replace most your code.

ComboBoxes does raise SelectedIndexChanged twice when bound, which must be a bug, but only once for later changes. If this is a problem you can RemoveHandler/AddHandler during the binding operation (which for toolstripcombo you have to do those three lines in code and would do this once in Form Load).

Some corrections: A DataAdapter is a set of objects used to communicate between a data source and a dataset, the dataset holds one or more datatables which is the local data cache. DA is used to fill dataset/datatable from the datasource. Creating queries by combining string bits is something you for many reasons simply don't do, you use the Parameters collection of the command. But as mentioned, in VB 2005 you normally don't write such statements yourself.

When you write classes in VB 2005 adding properties is basically intellisense generated where you just fill in the name and type (once), so in case of binding to a custom data proxy it's as RAD as can be. (tried to write "property" and press Tab key? ;))

With your VB6 background I'd say the best approach to VB.Net is to forget VB6 ever existed and don't make comparisons, because it will do neither you or anyone else any good. Instead try think of yourself as someone that is better equipped than absolute beginners to handle a development environment and for learning OOP programming and VB.Net. Also remember VB now has a far stronger emphasis on the Visual aspect of the development process than previous versions.
 
Thanks for the feedback

I appreciate the pointers, corrections, and markup. And the links too (I'll be busy for a while now!) My intention was to leave a breadcrumb for the next guy that comes along with similar background/intentions.

My background is strongest in Access, more than vb6. Your advice to forget vb6 ever existed may be practical from coder perspective ("shut up and love the oop!"), but from a productivity perspective, I disagree.

As I accelerate here from 0 to 0.60 in .net, I'm trying to maintain perspective on what tool is best for what. If you've never developed in Access, you probably have a fine opinion of VB.net being "RAD". But coming from an environment that had (essentially) very smart/specialized datagridviews (browse controls), an integrated report generator with good wizards, an integrated query generator and catalog... Coming from a RAD which allowed you to create and debug most of a data-complex application without writing (or knowing vocabulary for) any code at all, .net is shockingly NOT "R"AD. It's anti-RAD! It's a big painful step backwards in some important ways. Of course, if you're coming from a Visual C background, or something, you have no idea what I'm talking about.

FWIW, my tentative conclusion is that Access is far far far quicker to build simple applications (or prototypes at least). You can build/evolve both the database (complete with queries) and the application in one integrated drag-drop environment. Of course .net can make far more modern ui's, with countless other advancements, but the amount of work involved is probably not worth it (can't justify the extra cost) for many projects. So many things are so far from obvious/intuitive (although they are logical once you understand) that it's hard to even take the first steps in .net. Problem is that access is being left behind, and has more or less stagnated for more than a decade. For example, the report builder in Access is aging (growing set of limitations), and crystal reports seems to be no better. (A big feature apparently missing from both is click-through reports, dataitem mouseover events, and ability to construct an interactive report set; aka dashboard). I'm thinking that the best way to implement modern reporting abilities (in at least some types of projects) is to have a client app for editing (either Access or .net smart client), and putting the reporting abilities on the web (maybe asp.net, maybe php). I found a company selling a very rich graphing package for under $200, and that may be the way to go.

It's very strange to see the real RADs like PowerBuilder, Magellan, and Access, left behind in obscurity, and replaced by behemoths like .net. It's like having a cruiseship when you need rollerskates.


I'd never heard that building SQL with strings is bad. I've been doing it for years, especially to implement complex user-specified report parameters. To have a parameterized query for every conceivable report-query-option (e.g. include this list of clients, and dollar amounts lt/gt this amount, etc) seems totally unmanagable. Can you point me to reasons why building the sql string is so bad? (I do appreciate the style pointers!)

Also, I don't understand your reference to Property. I know what one is, but I don't know where you're saying I would use it.

Thanks.
 
Coming from a RAD which allowed you to create and debug most of a data-complex application without writing (or knowing vocabulary for) any code at all
That's how you create VB.Net data access applications too... while exceptions and more possibilities exist. The little code you see (and the really lot of code you don't see) is generated by the Designer.

I'd never heard that building SQL with strings is bad.
cjard has a ranting about this topic you can start with.
Basically, if you were to write all this code (which you don't :)) you would do this:
VB.NET:
[COLOR="Green"]'class variables[/COLOR] 
Private table As New DataTable
Private con As String = "connection string"
Private sql As String = "select uid,billdate from billings where company=@conum order by billdate desc"
Private da As New OleDb.OleDbDataAdapter(sql, con)
[COLOR="green"]'in form load:[/COLOR]
da.SelectCommand.Parameters.Add("@conum", OleDb.OleDbType.Integer)
[COLOR="green"]'when you want to fill:[/COLOR]
table.Clear()
da.SelectCommand.Parameters("@conum").Value = conum
da.Fill(table)
[COLOR="green"]'note that a DA manages connection automatically[/COLOR]
But as said it is better to use the visual development environment, you would than add the query "select uid,billdate from billings where company=@conum order by billdate desc" to the tableadapter and give it a name like "FillByCompany" and call this:
VB.NET:
Me.ta.FillByCompany(Me.dbDataset.BillingDates, conum)
Btw, the TA has ClearBeforeFill as default so you wouldn't get duplicates if your table was missing a primary key.
Also, I don't understand your reference to Property. I know what one is, but I don't know where you're saying I would use it.
If you ever were to write a class you would in most cases add a property or two to it. What I described is an example of how the VS code editor take such tasks to the limit of RAD. You mentioned doing this with a "custom object" (a custom data proxy), so what I was saying is implementing such thing would be a real RAD experience, it doesn't take more than 10 seconds writing a class with 2 properties to hold a displaymember and valuemember.
 
Re: building SQL strings, you missed my point/example. For a simple thing like looking up a uid, I can understand generally how your code is more formal/correct. But when building a multi-part user-customized query, such as for a report (e.g. choosing 1/more customers to include, and/or gt/lt a given dollar amt, and/or including only customers from a list of states, etc), parameters are not helpful as far as I know. (Also, that code is fluent for you, but awkward and extremely verbose for an old DAO or dBase programmer)

Re: custom objects and the "limit of RAD". I understand what you're saying, and I see now how that could be done. But it's just part of a rather huge lexicon of things that programmer must know how to type up. It's a huge leap less-easy than the automagic of Access; tedious excursion from the matter of conceptualizing a good interface. .net is more flexible and powerful yes, but not nearly as RAD. You'd be amazed how many things you could do without any code at all in Access (starting with multi-field comboboxes).

In any case, I appreciate that you're here to help. If there's a special forum for new .NETters to bitch about the dismemberment of RAD, and thick blatherification of code, let me know. ;)
 
But it's just part of a rather huge lexicon of things that programmer must know how to type up. It's a huge leap less-easy than the automagic of Access
We're not talking about MS Access development here ;) There are sites/forums dedicated for that.
If you want to embark a debate regarding VB.Net and its RADness we have the Debate Club forum.
 
We're not talking about MS Access development here
True, of course. But the purpose of this thread was to indicate to other Access/VB developers how to swiftly use multi-field comboboxes. And I was correcting you correcting me when you said that .net is as RAD as can be.:D
 
True, of course. But the purpose of this thread was to indicate to other Access/VB developers how to swiftly use multi-field comboboxes.
Which I corrected could be more swiftly and correctly done either way you turn it.
And I was correcting you correcting me when you said that .net is as RAD as can be.
I've never said that, and such statement would also be far on the contrary to my believes. :)
 
Yes, those are both references on how to write a property to a class in code editor, it doesn't get simpler than writing three words (at most!). Nowhere have I said .Net or Visual Studio or Visual Basic or the combination of these technologies have reached their ultimate goal of RAD as far as you can take that. ;)
 
Status
Not open for further replies.
Back
Top