searching a class instance in VB.Nett


I'm transferring a application from vba to, This selects data from a SQL database and sends updates back.
My application has around 20 combo-boxes.
In VBA I used multi column combo-boxes with the RecordID from the database in a hidden column so when the user saved I only had to get the value out of the hidden column of the selected row and send the update to the database.

What is the best way to accomplish this in
From what I can see you can only have one column in a combobox?

What sort of Data am I talking about?

Staff Names
Product Groups (Multiple groups)
Product pack sizes
Customer Groups (Multiple groups)

Do i load them all into Instances of their classes and fill the combo-boxes from the classes?
Then when I need to update the database can I retrieve the RecordID from the Class?

For Example with Staff

Create a Class with RecordID, Name, Phone, Position
1. On starting the Application create and instance of the class (Called "AllStaffRecords") that selects all the records from the SQL database.
2. Fill the Combo-box with the Names (From the "AllStaffRecords" Instance)
3. When the User needs to Search for a Name it will search the "AllStaffRecords" Instance of the class
4. When saving I lookup the "AllStaffRecords" instance to get the RecordID to update the database.

Is this a totally wrong approach?

Or should I be?
1. populating the Combo-box with a call to the Database
2. Calling the database each time the user needs to search for a staff name
3. call to the database to retrieve the RecordID before updating

The Second option would seem to have at a bare minimum 20 needless calls to the database for each record that is saved

What is the Best/Fastest way to accomplish this?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fernando SotoRetiredCommented:
Hi  p-plater;

The below code snippet will show you how it can be done
Public Class Form1

    '' List of classes that were filled from the database
    Dim AllStaffRecords As New List(Of Staff)

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        '' Fill the above list
        AllStaffRecords.Add(New Staff() With {.RecordID = 1, .Name = "Tom", .Phone = "1234567890", .Position = "Mgr"})
        AllStaffRecords.Add(New Staff() With {.RecordID = 2, .Name = "Alice", .Phone = "1234567891", .Position = "Sectatary"})
        AllStaffRecords.Add(New Staff() With {.RecordID = 3, .Name = "Paul", .Phone = "1234567892", .Position = "Computer Tech"})

        '' Assign the DataSource for the ComboBox
        ComboBox1.DataSource = AllStaffRecords
        '' Display member that is inserted into the ComboBox to be selected
        ComboBox1.DisplayMember = "Name"
        '' The Value member associated with the name
        ComboBox1.ValueMember = "RecordID"

    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        '' To Get the selected value from the selected Name
        TextBox1.Text = ComboBox1.SelectedValue

    End Sub
End Class

'' The class that is filled from the Database
Public Class Staff
    Public Property RecordID As Integer
    Public Property Name As String
    Public Property Phone As String
    Public Property Position As String
End Class

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
p-platerAuthor Commented:
Hello Fernando

when I try that code I get errors that the following are not members of combobox


I should have mentioned that I was using WPF - Not sure if that makes the difference?
Fernando SotoRetiredCommented:
I have not done much with WPF but if you post the XAML I may be able to come up with something.
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

p-platerAuthor Commented:
I've decided to go with Windows Forms so this now works fine now - Thanks

I've two Questions about it.

Is this the Best way for the scenario I described?

Can you fill a list(of T) with only some of the Fields/Properties of a class?
For example this Staff Class will have 15-20 Fields/Properties but 99% of the time only 4 will be needed. (Creating the List with the whole class would waste memory even if no values were actually entered wouldn't it?)

Another Option would be to go with some 3rd party controls that have a Multicolumn Combobox

What would you recommend?
Fernando SotoRetiredCommented:
To your question, "Is this the Best way for the scenario I described?",  yes, I believe so.

To your question, "Can you fill a list(of T) with only some of the Fields/Properties of a class?", If you are talking about accessing the database using standard ADO.Net objects and calls, then yes. If you are using any of the newer technologies such as Linq to SQL or Linq to Entity Framework, then no. Let me explain on the newer technology. When you use Linq to query the database lets say a table such as Employee then you can bring back all columns of that table in an Employee object but if you only want to bring back just a couple of columns that you need to use an object other then Employee. You can bring back an Anonymous type or create a class identifying the columns to be returned and let the Linq query fill those classes for you.

To your statement, "Another Option would be to go with some 3rd party controls that have a Multicolumn Combobox", well if you implement a class as I suggested in my previous post you are assigning that class object to the ComboBox object and therefore have access to all its fields. So it displays only one column but it is like all the other fields as hidden columns. For example in the code I posted above you can do this
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        '' To Get the selected value from the selected Name
        TextBox1.Text = ComboBox1.SelectedValue

        '' Get the selected object with all its properties
        Dim person As Staff = ComboBox1.SelectedItem
        '' You can get access to all the properties of the selected object
        Console.WriteLine("{0} : {1} : {2} : {3}", person.RecordID, person.Name, person.Phone, person.Position)
    End Sub

Open in new window

p-platerAuthor Commented:
Thanks again
Yes I am using standard ADO.Net to access the database.

So if I have two more fields/Properties in the Staff Class "Salary" and "Budget" how do I only populate the list(of T) with only the original 4?
p-platerAuthor Commented:
I suppose what I am really asking is can you make a class object that contains only a couple of select Felds of the class.
Which I suppose would be done with a second Construct method in the class?
Fernando SotoRetiredCommented:
Hi p-plater;

To your most recent post. You could create another class with only the 4 values you want but if you ask me that would be over kill because the ComboBox control will only display the value of the class which you tell it to the other values are not displayed.
p-platerAuthor Commented:
Looking back on it I didn't say what was behind the Question.

Staff has 20 Properties

When you make a list(of staff) and only add 4 of the Properties to the list - Does memory get allocated for the other 16 Properties that aren't in the list?
Or is memory only used for the ones that are actually added?
Fernando SotoRetiredCommented:
Hi p-plater;

To your question, "When you make a list(of staff) and only add 4 of the Properties to the list - Does memory get allocated for the other 16 Properties that aren't in the list?, well that depends on how the object/class staff is defined in your code. If you are talking about the the DataTable that is holding the data returned back from a SQL query that data structure is not a staff object per say but does hold the data from the data table from the database. The DataTable object holds Row objects having multiple column object which are filled from the return of the query. So the Row objects will have all 20 columns if in fact the query returned all 20 columns. So back to the original question if you have a List(Of staff) that means in your code you must have a Class called staff, the number of properties defined in your class will determine what is persisted in the class. So if you staff class has only four public properties to hold four columns from the database then only those four data values are persisted but if in fact it defines all 20 columns then all 20 values are persisted.
p-platerAuthor Commented:
Thanks very much Fernando
Fernando SotoRetiredCommented:
Not a problem p-plater, glad I was able to help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.