Add Item to DropDownList

Hi!

In this post I'll explain how to add a custom-item in a DropDownList using ListItem object.

As written in MSDN:

"ListItem represents a data item in a data-bound list control"

"A ListItem control represents an individual data item within a data-bound list control"

First of all, we have to connect a Database Table with a DropDownList. (you can see how populate a DropDownList here)

Protected Sub GetContact()

   Dim obj As cl_Person = Nothing
   obj = New cl_Person(System.Configuration.ConfigurationManager.AppSettings("APPConnectionString"))

   Me.ddlPerson.DataSource = obj.GetContact()
   Me.ddlPerson.DataValueField = "ContactID"
   Me.ddlPerson.DataTextField = "FullName"
   Me.ddlPerson.DataBind()

End Sub

Then in the DataBound event of the DropDownList, write this:

Protected Sub ddlPerson_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlPerson.DataBound

   Dim myItem As New ListItem
   myItem.Text = "Choose One !!!"
   myItem.Value = 0
   myItem.Selected = True
   Me.ddlPerson.Items.Add(myItem)

End Sub

As you can see, "Choose One !!!" rapresents our custom-item.



If you want, you can download this example here.
You can download and install AdventureWorksDB.msi database here.

Please remember to change in the web.config file, the two values "Data Source=pc01;" with
your sqlserver instance name. (probably "computername" with Sql2005/2008, but if you are using SqlExpress edition your instance name is "computername\SQLExpress")

That's all !

How to… populate a dropdownlist

1. creare una vista sulla tabella da cui estrarre i dati (dal db AdventureWorks);
2. creare un dataset sulla vista;

3. creare un metodo in una classe per l'estrazione dei dati;

4. utilizzare il metodo e collegarlo alla DropDownList.

1.
Tramite Management Studio, aprire il db AdventureWorks. Visualizzare l'elenco delle "Viste" quindi crearne una di nuova sulla tabella Contact (Person).
I campi da fleggare sono: "ContactID, FirstName, LastName, Title".
Affinche' il risultato finale sia del tipo: "Mr. Gustavo Achong" suggerisco di editare il codice sql in questo modo:

SELECT ContactID, FirstName, LastName, Title, Title + N' ' + FirstName + N' ' + LastName AS [Full]
FROM Person.Contact

Salvare la Vista.

2.
Creare un DataSet e con la procedura guidata aggiungere un TableAdapter e collegare la vista appena creata con tutti i campi della vista (vw_get_persons).


Qui la procedura visualizza la vista...


3.
Creare un metodo in una classe:

Public Function GetDllPersons() As DataSet1.VW_GET_PERSONSDataTable

        Dim sqlStatement As String
        Dim cnn As SqlConnection = Nothing
        Dim cmd As SqlCommand = Nothing
        Dim da As SqlDataAdapter = Nothing

        Try
            sqlStatement = "SELECT * FROM VW_GET_PERSONS"
            cnn = New SqlConnection(Me._connectionString)
            cmd = New SqlCommand(sqlStatement, cnn)
            da = New SqlDataAdapter(cmd)

            cnn.Open()

            _ds.VW_GET_PERSONS.Rows.Clear()
            da.Fill(_ds, "VW_GET_PERSONS")
        Catch ex As Exception
            'todo: code for errors!
        Finally
            GetDllPersons = _ds.VW_GET_PERSONS
            If Not (cnn Is Nothing) Then
                cnn.Close()
                cnn.Dispose()
                cnn = Nothing
            End If
            If Not (da Is Nothing) Then
                da.Dispose()
                da = Nothing
            End If
            If Not (cmd Is Nothing) Then
                cmd.Dispose()
                cmd = Nothing
            End If
        End Try

    End Function

4.
Nella pagina .aspx entrare nell'evento Page_Load e aggiungere questo codice:

 If Not IsPostBack Then 'essenziale per evitare di caricare sempre gli stessi valori
   Dim objGetdllPersons As Class1 = Nothing
   objGetdllPersons = New Class1("Data Source=wolfdale;Initial Catalog=AdventureWorks; Integrated Security=True")
   Me.ddl_Persons.DataSource = objGetdllPersons.GetDllPersons()
   Me.ddl_Persons.DataTextField = "Full"
   Me.ddl_Persons.DataValueField = "ContactID"
   Me.ddl_Persons.DataBind()
 End If

Quindi eseguire.