We help IT Professionals succeed at work.

Using Linq to SQL and VB how to loop through a record set and add each item to a drop down list.

3,055 Views
Last Modified: 2013-11-11
I have a simple Linq query that returns a couple dozen records.  I want to loop through these records and add them to a DropDown box.  I don't want to simply bind the drop down box to the dataset because i want to inject some extra items and change the records before they go into the list.

Thanks in advance for any help provided
Dim Database As New DataClasses1DataContext
        Dim Query = From D In Database.Departments _
                    Select D.Department, D.ID
 
        drpDepartment.Items.Clear()
        drpDepartment.Items.Add("Select Department")
 
        ' Add Selected records to the dropdown here
        ' I would like The Selected Items to be the Department
        ' field and Selected Value to be the ID

Open in new window

Comment
Watch Question

Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Hi RMAHelpDesk;

Here is some sample code showing how this can be done.

Fernando

Public Class Form1
 
    Class DepartmentInfo
        Private _dept As String
        Private _id As String
 
        Public Sub New(ByVal dept As String, ByVal id As String)
            Me._dept = dept
            Me._id = id
        End Sub
 
        Public Property Department()
            Get
                Return _dept
            End Get
            Set(ByVal value)
                _dept = value
            End Set
        End Property
 
        Public Property ID() As String
            Get
                Return _id
            End Get
            Set(ByVal value As String)
                _id = value
            End Set
        End Property
    End Class
 
    Private Sub Button1_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles Button1.Click
 
        Dim deptInfo As New ArrayList()
        Dim Database As New DataClasses1DataContext
        Dim Query = From D In Database.Departments  _
                    Select D.Department, D.ID
 
        drpDepartment.Items.Clear()
        deptInfo.Add(New DepartmentInfo("Select Department", ""))
 
        Dim idx As Integer = drpDepartment.Items.Count
        For Each item In Query
            deptInfo.Add(New DepartmentInfo(item.Departments, item.ID))
        Next
 
        drpDepartment.DataSource = deptInfo
        drpDepartment.DisplayMember = "Department"
        drpDepartment.ValueMember = "ID"
 
    End Sub
 
End Class

Open in new window

Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
The use of this inner class in my code example:

    ' This class is used to hold the Display and Value members of the DropDown list
    ' it allows you to fill an ArrayList of these items and assign it to the
    ' DataSource of the control
    Class DepartmentInfo
        Private _dept As String
        Private _id As String

        ' used to adssign values to the class when a instance is created.
        Public Sub New(ByVal dept As String, ByVal id As String)
            Me._dept = dept
            Me._id = id
        End Sub

        ' The public properties are used by the DataSource of the control
        ' to assign Display and Value members of the control
        Public Property Department()
            Get
                Return _dept
            End Get
            Set(ByVal value)
                _dept = value
            End Set
        End Property

        Public Property ID() As String
            Get
                Return _id
            End Get
            Set(ByVal value As String)
                _id = value
            End Set
        End Property
    End Class
Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Have you tried out the solution?
ddlDepts.Items.Clear()

Dim tmpItem As new ListItem("Select a Department", "0")
ddlDepts.Items.Add(tmpItem)

For Each record in qryDepartments
        ddlDepts.Items.Add(new ListItem(cstr(record.DeptName), cstr(record.DeptID)))
Next
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
This was a very simple way to solve my issue and it works great.
Thanks
Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Hi RMAHelpDesk;

Thank you so much for your professionalism in maintaining communications with those that are trying to help you.

Great job.

Fernando

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.