Solved

VB.Net How to fill a ComboBox and retrieve the row value

Posted on 2010-09-09
23
847 Views
Last Modified: 2013-11-27
I am using the below loop to fill a combobox. How do I set the row value and retrieve it later? Just can't get on with the VS2010 help.
' Fill Company object in ArrayList
reader1 = cmd1.ExecuteReader()
While reader1.Read()
  With Me.cboCompany
    .Items.Add(reader1("CompanyName"))
    ' Add value here = reader1("IDCompany")
  End With
End While
reader1.Close()

Open in new window

0
Comment
Question by:TeDeSm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 5
  • 5
  • +3
23 Comments
 
LVL 53

Assisted Solution

by:Dhaest
Dhaest earned 100 total points
ID: 33635970
How to set values into ComboBox Items
http://www.codeproject.com/KB/combobox/valuemembersample.aspx


(example converted to vb.net)
Dim Authors As New ArrayList()

While rsAutors.Read()

	Authors.Add(New AddValue(rsAutors.GetString(1), rsAutors.GetInt32(0)))
End While
rsAutors.Close()
Me.conn.Close()

Me.cboAuthors.DataSource = Authors
Me.cboAuthors.DisplayMember = "Display"
Me.cboAuthors.ValueMember = "Value"




Public Class AddValue
	Private m_Display As String
	Private m_Value As Long
	Public Sub New(Display As String, Value As Long)
		m_Display = Display
		m_Value = Value
	End Sub
	Public ReadOnly Property Display() As String
		Get
			Return m_Display
		End Get
	End Property
	Public ReadOnly Property Value() As Long
		Get
			Return m_Value
		End Get
	End Property
End Class

Open in new window

0
 
LVL 22

Expert Comment

by:Om Prakash
ID: 33636024
Try
' Fill Company object in ArrayList
reader1 = cmd1.ExecuteReader()
With Me.cboCompany
    .DataSource = dt
    .DisplayMember = "Disk_Name"
    .ValueMember = "Disk_Key"
    .SelectedIndex = 0
  End With
reader1.Close()

Open in new window

0
 
LVL 12

Expert Comment

by:rajapandian_81
ID: 33636029

While reader1.Read()
  ListItem LI = new ListItem(reader1("CompanyName"), reader1("IDCompany"));
  With Me.cboCompany
    .Items.Add(LI)
  End With
End While

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Expert Comment

by:z_alex
ID: 33636066
********** Fill Data to List Box
    Dim conn As String
    Dim DbConn As OleDbConnection
    Dim cmd As OleDbCommand
    Dim sda As OleDbDataReader
    Dim dbclass As CoffrageLib = New CoffrageLib

    Private Sub lstBoxPost(ByVal xobj As Object, Optional ByVal xField As String = "ITEMCODE ", Optional ByVal xCondition As String = "")
        '***Data Table
        Dim str As String
        If xCondition = "" Then
            str = ("select top 20 " & xField & " & '-' & ITEMDESC1 AS X, * from item")
        Else
            str = ("select top 20 " & xField & " & '-' & ITEMDESC1 AS X, * from item where " & xCondition)
        End If

        Dim xDa As New OleDbDataAdapter(Str, DbConn)
        Dim dt As New DataTable

        xDa.Fill(dt)
        xDa.Dispose()
        xobj.DataSource = dt
        xobj.DataTextField = "X"
        xobj.DataValueField = "ITEMdESC1"
        xobj.DataBind()
    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        DbConn = New OleDbConnection(dbclass.CoffrageConn)

        If Not Page.IsPostBack Then
            lstBoxPost(LstDepartment)  'LstDepartment (listbox/combo box) object
        End If
    End Sub

****** retrieve the row value
TextBox3.Text = LstDepartment.SelectedValue
0
 
LVL 11

Expert Comment

by:Kelvin McDaniel
ID: 33636157
To answer your question directly, there is no "value" associated with the ComboBox Control  other than the visible Text.

Here's the MSDN article about it and the specific part you're interested in (good news or bad news):
http://msdn.microsoft.com/en-us/library/system.windows.forms.combobox.aspx

    "A ComboBox displays a text box combined with a ListBox, which enables the user to select items from the list or enter a new value."

As a side note, You really don't want to use a "naked" reader to populate anything... they can lead to stability issues if used incorrectly, such as in your example. Not harping on it, and you may never see the side effects of what I'm talking about, but nevertheless the danger is there.

Here's what I consider the proper way to do what you're trying to do (assuming that you want to use the reader)...

 
Using conn As New SqlConnection(connectionString)
    Dim sqlString As String = "SELECT * FROM SOMETHING"
    Using cmd As New SqlCommand(sqlString, conn)
        Using reader1 As SqlDataReader = cmd.ExecuteReader()
            While reader1.Read
                ComboBox1.Items.Add(reader1("CompanyName"))
            End While
        End Using
    End Using
End Using

Open in new window

0
 

Author Comment

by:TeDeSm
ID: 33636319
Class works great. Which event should be used to retrieve the value?
0
 
LVL 11

Expert Comment

by:Kelvin McDaniel
ID: 33636325
Without testing it, z_alex's example appears viable, but if I'm not mistaken it requires that Option Strict be set to Off. That's a no-no in all the environments I work in, but if's allowed where you are then great!

If it works, I do like the way it "tricks" Control into accepting values in ways that weren't expressly intended.
0
 
LVL 11

Expert Comment

by:Kelvin McDaniel
ID: 33636419
... and it looks like I was incorrect in the assertion "there's no value other than what's visible". I guess it helps to read a little further?  :) Vastly simplifies things down to the following, including your provided values...

... and I prefer this method over z_alex's as it uses the properties as exposed by the control, so it doesn't require Option Strict Off...

... and you can use whatever event exists after Form_Load() to retrieve the values from the ComoBox. More than likely, you'll want it to be either some _Click() or _IndexChanged() event though... based on user interaction with your form.
Using conn As New SqlConnection(connectionString)
    Using cmd As New SqlCommand("SELECT * FROM SOMETHING", conn)
        Using reader1 As SqlDataReader = cmd.ExecuteReader()
            ComboBox1.DataSource = reader1
            ComboBox1.DisplayMember = "CompanyName"
            ComboBox1.ValueMember = "IDCompany"
        End Using
    End Using
End Using

Open in new window

0
 

Author Comment

by:TeDeSm
ID: 33636657
azarc3. Interesting about the 'naked reader'. I have been trying to use you suggestion and can't seem to get my declared variables in the right order. Could you take a look please. I am inclined to keep things simple.

Dim sqlConnection As New System.Data.SqlClient.SqlConnection _
 ("Data Source=FLOSERV2\FLOSQL;Initial Catalog=FloydPreAlert;Integrated Security=True") _
 Catalog=FloydPreAlert;Integrated Security=True")
Dim cmd1 As New System.Data.SqlClient.SqlCommand
Dim reader1 As System.Data.SqlClient.SqlDataReader
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33636675
A complete example....


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim Companys As New ArrayList()


        Companys.Add(New AddValue("Company1", "1"))
        Companys.Add(New AddValue("Company2", "2"))
        Companys.Add(New AddValue("Company3", "3"))
        Companys.Add(New AddValue("Company4", "4"))
        Companys.Add(New AddValue("Company5", "5"))
        Companys.Add(New AddValue("Company6", "6"))
        Me.cboCompany.DataSource = Companys
        Me.cboCompany.ValueMember = "Value"
        Me.cboCompany.DisplayMember = "Display"
end sub

    Private Sub cboCompany_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboCompany.SelectedIndexChanged
        Dim x As AddValue = cboCompany.SelectedItem
        MessageBox.Show(x.Value & " " & x.Display)
    End Sub



Public Class AddValue
    Private m_Display As String
    Private m_Value As Long
    Public Sub New(ByVal Display As String, ByVal Value As Long)
        m_Display = Display
        m_Value = Value
    End Sub
    Public ReadOnly Property Display() As String
        Get
            Return m_Display
        End Get
    End Property
    Public ReadOnly Property Value() As Long
        Get
            Return m_Value
        End Get
    End Property
End Class

Open in new window

0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33636703
FYI: with the code above, I would add the eventhandler dynamic in the code, so that the combobox is filled before even one event could occur

Me.cboCompany.DataSource = Companys
        Me.cboCompany.ValueMember = "Value"
        Me.cboCompany.DisplayMember = "Display"

        AddHandler cboCompany.SelectedIndexChanged, AddressOf cboCompany_SelectedIndexChanged
0
 
LVL 11

Expert Comment

by:Kelvin McDaniel
ID: 33637062
TeDeSm, it always goes in the following order...
1. Connection
2. Command
3. Reader

So in the case of what you've provided...


Using sqlConnection As New System.Data.SqlClient.SqlConnection _
 ("Data Source=FLOSERV2\FLOSQL;Initial Catalog=FloydPreAlert;Integrated Security=True")

    Using cmd1 As New System.Data.SqlClient.SqlCommand("{your sql text}", sqlConnection)

        Using reader1 As System.Data.SqlClient.SqlDataReader = cmd1.ExecuteReader()

            ' your reader1-based code goes here

        End Using

    End Using

End Using 

Note: The "End Using"'s properly close and dispose of each of these objects that implement IDisposable.

Open in new window

0
 

Author Comment

by:TeDeSm
ID: 33637135
azarc3: I now have the following to handle the reader. However I get an exception on the line Using Reader1 about Complex DataBinding and using an IList or IListSource.
Dim sqlConnection As New System.Data.SqlClient.SqlConnection _
  ("Data Source=FLOSERV2\FLOSQL;Initial _ 
  Catalog=FloydPreAlert;Integrated Security=True")
sqlConnection.Open()
Using cmd1 As New System.Data.SqlClient.SqlCommand("SELECT  _ 
  IDCompany, CompanyName FROM dbo.Companies ORDER BY _ 
  CompanyName",sqlConnection)
   Using reader1 As System.Data.SqlClient.SqlDataReader = _
   cmd1.ExecuteReader()
	Me.cboCompany.DataSource = reader1
	Me.cboCompany.DisplayMember = "CompanyName"
	Me.cboCompany.ValueMember = "IDCompany"
   End Using
End Using

Open in new window

0
 

Author Comment

by:TeDeSm
ID: 33637171
azarc3: Also had a problem with the sqlConnection not being open. Changed to Dim to enable the code to run. I guess there is a method to open the connection as part of the Using. Great that the objects are disposed of without having to 'cleanup' at the end.
0
 

Author Comment

by:TeDeSm
ID: 33637198
Dhaest: Not too sure on how to use the AddHandler . I understand what is supposed to happen but I suspect there is more to it.
0
 
LVL 11

Accepted Solution

by:
Kelvin McDaniel earned 400 total points
ID: 33638272
You can still use the .Open() command as you normally would... my bad on that; my normal DALs pass back an already open connection.

I've updated what you last provided, but...
1. Put back the SqlConnection with the appropriate action
2. Switched the reader to a datatable
3. Moved a couple of long strings so the code's a bit easier to read

I believe this should work for you.
Dim connString As String = "Data Source=FLOSERV2\FLOSQL;Initial Catalog=FloydPreAlert;Integrated Security=True"
Dim sqlString As String = "SELECT IDCompany, CompanyName FROM dbo.Companies ORDER BY CompanyName"

Using sqlConnection As New System.Data.SqlClient.SqlConnection(connString)

    sqlConnection.Open()
    Using da As New SqlDataAdapter(sqlString, sqlConnection)

        Using dt As New DataTable("temporary_table")
            da.Fill(dt)
            Me.cboCompany.DataSource = dt
            Me.cboCompany.DisplayMember = "CompanyName"
            Me.cboCompany.ValueMember = "IDCompany"
        End Using

    End Using

End Using

Open in new window

0
 

Author Comment

by:TeDeSm
ID: 33644752
azarc3: The code runs with no errors but the combobox is not filled on running the project. A breakpoint confirms the sub is run on form load.

As a test of a theory I Dim dt and used a With dt block for filling the combobox and that works. I'm guessing that the End Using dt destroys the temporary table and the data falls out of scope emptying the combobox. What do you think?
Dim connString As String = "Data Source=FLOSERV2\FLOSQL;Initial _ 
  Catalog=FloydPreAlert;Integrated Security=True"
Dim sqlString As String = "SELECT IDCompany, CompanyName FROM _ 
  dbo.Companies ORDER BY CompanyName"

Using sqlConnection As New System.Data.SqlClient.SqlConnection _ 
  (connString)

  sqlConnection.Open()
  Using da As New SqlClient.SqlDataAdapter(sqlString, _ 
   sqlConnection)

  Dim dt As New DataTable("temporary_table")
     With dt
       da.Fill(dt)
       Me.cboCompany.DataSource = dt
       Me.cboCompany.DisplayMember = "CompanyName"
       Me.cboCompany.ValueMember = "IDCompany"
     End With
   End Using
End Using

Open in new window

0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33644758
Give this a try
Dim connString As String = "Data Source=FLOSERV2\FLOSQL;Initial _ 
  Catalog=FloydPreAlert;Integrated Security=True"
Dim sqlString As String = "SELECT IDCompany, CompanyName FROM _ 
  dbo.Companies ORDER BY CompanyName"

Dim dt As DataTable

Using sqlConnection As New System.Data.SqlClient.SqlConnection _ 
  (connString)

  sqlConnection.Open()
  Using da As New SqlClient.SqlDataAdapter(sqlString, _ 
   sqlConnection)

    dt = New DataTable("temporary_table")
     With dt
       da.Fill(dt)
       Me.cboCompany.DataSource = dt
       Me.cboCompany.DisplayMember = "CompanyName"
       Me.cboCompany.ValueMember = "IDCompany"
     End With
   End Using
End Using

Open in new window

0
 

Author Comment

by:TeDeSm
ID: 33644771
azarc3: Confirmed the temporaryt table is the problem. If I dt.dispose() after my End With dt code the temp table is destroyed and the combobox is empty.
0
 

Author Comment

by:TeDeSm
ID: 33644825
azarc3: Ok that works as long as I don't dispose of dt. Thank you, a neat and tidy solution.

I will start a new thread with regards to possibly adding an event handler for the cboCompany_SelectedIndexChanged event which fires for each of Me.Company lines. I only need this event for when the user selects a row.
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33644877
AddHandler cboCompany.SelectedIndexChanged, AddressOf cboCompany_SelectedIndexChanged
0
 

Author Comment

by:TeDeSm
ID: 33644958
Ok I have dealt with the cboCompany.SelectedIndexChanged problem with a quick search. Time to close this solution, many thanks.
0
 

Author Closing Comment

by:TeDeSm
ID: 33644985
Two solutions provided, one using an ArrayList the other using a DataAdapter. Both worked, both have their merits. If using the DataAdapter do not dispose of the variable holding the temporary table.
0

Featured Post

More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question