add a value to a dropdownlist populated from database in vb.net

Question

this is my code -

 SqlCmd = New SqlCommand("sp_load_names", SqlCnn)
 SqlCmd.CommandType = CommandType.StoredProcedure
 SqlDR = SqlCmd.ExecuteReader()
 While SqlDR.Read
      ads_list.Items.Add(New ListItem(SqlDR(1) & ""))
 End While
 SqlDR.Close()

this shall populate the dropdownlist data, but in the value i need it to pick up the "ID" field from the stored proc. the stored proc sends two parameters, ID and Name. so i populate name, but how do i populate the id in value like this - Mark Sam Dennis

i hope im not confusing anyone

Answer 1

One of the ListItem constructor overloads sets both the text and value properties:

New ListItem("Text", "Value")

You should be able to access both columns in the SQL data reader using SqlDR(1) and SqlDR(2).

Answer 2

ListItems are objects, so you can create your own simple ListItem class to contain both the ID and Display Value

Class MyListItem
   Public ID As Integer
   Public Text As String

   Public Sub New(iID As Integer, sText As String)
      Me.ID = iID
      Me.Text = sText
   End Sub

   Public Overrides Function ToString() As String
      Return Me.Text
   End Sub
End Class

...
Dim oItem As MyListItem

While SqlDR.Read
    oItem = new MyListItem(SqlDR(0),SqlDR(1))
    ads_list.Items.Add(oItem)
End While
...

oItem = CType(ads_list.SelectedItem,MyListItem)

Answer 3

Please ive been looking for an answer for this my codes. Im trying to populate a drop down list values from a database and it won't populate. Here is the code:

<%@ Page Title="" Language="VB" MasterPageFile="~/Site.master" AutoEventWireup="false" CodeFile="PhotoAlbum.aspx.vb" Inherits="PhotoAlbum" %>

Members Of Ephesians 5:10 Photo Album!

<asp:SqlDataSource ID="categoriesDataSource" runat="server" 
    ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 



    SelectCommand="SELECT [CategoryID], [Name] FROM [Categories] WHERE ([UserId] = @UserId) ORDER BY [Name]">
    <SelectParameters>
        <asp:QueryStringParameter Name="UserId" QueryStringField="ID"/>
    </SelectParameters>
</asp:SqlDataSource>

<br />

<br />

<h1 style="font-weight:bold">Filter Pictures By Category:
   <asp:DropDownList ID="categories" runat="server" 
                    AppendDataBoundItems="True" 
              DataSourceID="categoriesDataSource" AutoPostBack="True" 
        DataTextField="Name" DataValueField="CategoryID" >
                    <asp:ListItem Value="" Text="--All Category --"/>
                </asp:DropDownList>
    </h1>

</asp:Content>