Before I moved to the .Net world, I used to write a lot of Ms Access and VB6 applications. In Access it is really easy to set up a combo box, with multiple dropdown columns. All you need to do is add a combobox to the form, a wizard will pop up, you follow the wizard, select your key column and display column, you can also choose to display both, and you are set. Your form will easily display something like this.
Now, in visual studio 2005, it is not as easy as that. I actually was surprised to find out how much of a pain it was to create a multicolumn combobox. In visual studio when you add a combo box (that you want to bind to a datasource) it gives you the options to choose the data source, display member, value member and selected member. The music starts to play when you need two columns as the display member, like above, say productID and Description. But when a user selects something, you only need to get the productID selected and stored.
Multicolumn Lists
There are two ways you can accomplish this. The first one is to create your own custom control, that inherits from combobox, and add functionality for the datasource, display, value and selected members. The other one is to create a simple generic class, and add the same functionality. In this post I’ll show you how to create this second one.
The Logic.
What we need is a connection to the database, a stored procedure or a select statement that returns the display members, something simple like SELECT ProductID, Description FROM Products. All we need is for it to return your display members, and that is all.
So I created this class.
Imports System.Data
Imports System.Data.SqlClient
Public Class MultiColumnList
Private connString As String
Private sp As String
Private source As New Dictionary(Of String, String)
Public Property Stored_Procedure() As String
Get
Return sp
End Get
Set(ByVal value As String)
sp = value
End Set
End Property
Public Property ConnectionString() As String
Get
Return connString
End Get
Set(ByVal value As String)
connString = value
End Set
End Property
Public Function GetSelectedValue(ByVal input As String) As String
If input = Nothing Then
Return ""
Else
Return source(input)
End If
End Function
Public Function DisplayItems() As String()
'This Function returns a string array as the display member
'clear the dictionary before adding new values to avoid duplication
source.Clear()
'a list for easy move of items to array
Dim list As New List(Of String)
Using cn As New SqlConnection(ConnectionString)
cn.Open()
Dim cmd As New SqlCommand()
cmd.Connection = cn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = Stored_Procedure
Dim da As SqlDataReader = cmd.ExecuteReader
While da.Read
'saving the items in the dictionary in the format
'source("ProductID - Description","ProductID")
'We’ll use the combined display as key to get the stock-no
source.Add(String.Format("{0} -{1}", da.GetString(0), _
da.GetString(1)), da.GetString(0))
list.Add(String.Format("{0} -{1}", da.GetString(0), da.GetString(1)))
End While
da.Close()
cn.Close()
End Using
'string array that will be returned as source for combo/listbox dropdown display
Dim res(list.Count - 1) As String
'move the items from the list to our return string array
list.CopyTo(res, 0)
list = Nothing
Return res
End Function
End Class
As you can see in the class, I used a generic string dictionary to store the returned rows, then I added them to a string array, to be used as the items collections for a dropdowm control like a combobox or a listbox.
I also added a simple function that gets the selected item, uses it as a key to read the return value from the dictionary, which is actually what will be stored in your application.
There are also two properties, to set and get the stored procedure and connection string respectively. In this case I decided to use a stored procedure, but you can easily modify the code to use a direct select statement, or you can also decide not to use a database and input your own values with a little twist to the code.
Implementation:
To implement it you can use a combobox or a listbox. In this case I’m going to use a dialog box, that has a list box control ( which I’ll now fill up with my multicolumn list). The setting is that I have a bound textbox on my form, next to it a linklabel or a button that pops up the dialog box.
So first, add a dialog to your project. Right click on your project, choose Add > New Item > Dialog
And then add a list box to your dialog form. On the form load event, instantiate the multicolumn class, set the connectionString property and then the stored_procedure property to the name of your stored procedure. Then handle the ok and cancel buttons click events appropriately. See the complete code listing for the dialog form below.
Imports System.Windows.Forms
Public Class Authorizers
Dim multi As New MultiColumnList
Public Selected As String
Private Sub OK_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK_Button.Click
Me.DialogResult = System.Windows.Forms.DialogResult.OK
Selected = multi.GetSelectedValue(ListBox1.SelectedItem)
Me.Close()
End Sub
Private Sub Cancel_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Cancel_Button.Click
Me.DialogResult = System.Windows.Forms.DialogResult.Cancel
Me.Close()
End Sub
Private Sub Authorizers_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
ListBox1.Items.Clear()
multi.ConnectionString = My.Settings.UsersConnectionString
multi.Stored_Procedure = "GetAuthorizers"
ListBox1.Items.AddRange(multi.DisplayItems)
End Sub
End Class
I create the public variable Selected to store the selected value, which you can later use on your form as required. This helps a lot if you have to use the dialog on multiple forms.
Implementation on main form.
To implement the dialog on my form, I added a textbox and a linklabel. When a user clicks the linklabel, the dialog is shown, they choose an item, and the value is stored in the text box. Simple as that. See the code for the linklabel, or a button, or whatever control of functionality you might choose to use for accessing the dialog.
Private Sub LinkLabel2_LinkClicked(ByVal sender As System.Object, ByVal e As System.Windows.Forms.LinkLabelLinkClickedEventArgs) Handles LinkLabel2.LinkClicked
If Authorizers.ShowDialog() = Windows.Forms.DialogResult.OK Then
Me.AuthorizedByTextBox.Text = Authorizers.Selected
End If
LinkLabel1.LinkVisited = True
End Sub
The dialog is show like in this image, see the displayed text is in the format Username-Title, but when a user selects an item, only the username is returned for use.
and then your value is displayed in the textbox as seen below.
I know this is not the easiest way to accomplish this, and this is not the cleanest, concise code to do the job, but I hope it’ll help you or give you an idea of how to go about this.
All in all I think the VS designers should have made it a bit easier to create multicolumn comboboxes, for the newbies or for the lazy ones.
A more advanced and a better idea as I said above is to create a custom combobox that inherits from system.windows.forms.combobox and add all this functionality for display, datasource and selected value. This class here does not inherit from anything, so it makes it free to use and bind to any dropdown list.
Till next time. Yours truly.
