Solved

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

Posted on 2010-09-09
23
817 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
  • 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
 
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:azarc3
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:azarc3
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:azarc3
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 11

Expert Comment

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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)…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now