asked on

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

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

Fernando Soto

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 Soto

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 Soto

Have you tried out the solution?

AaronNance

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

ASKER CERTIFIED SOLUTION

AaronNance

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

RMAHelpDesk

ASKER

This was a very simple way to solve my issue and it works great.
Thanks

Fernando Soto

Hi RMAHelpDesk;

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

Great job.

Fernando