Fill in a DropDownList with data from datasource RRS feed

  • Question

  • User850463362 posted

    How can I fill in a dropdownlist with data being pulled from a table? I scratched my head for 3 hours before I found a solution. I'd like to share it here just in case someone is also having a same question.

    Suppose in my EmployeeTB table I have the following columns:

    UniqueID, FirstName, LastName, Address, Phone

    And I want to fill in a dropdownlist (in DESC order) with all employees name in format "FistName, LastName" 

    So suppose my EmployeeTB table has the following entries:

    UniqueID     FirstName     LastName     Address     ect...

    123456       Mary              Janes           ............

    789098       Bill                 Miers            ............

    I want to fill in my dropdownlist with items in format:

    Please select an employee name to begin

    Bill, Miers

    Mary, Janes

    To fill in a dropdownlist automatically with data. Here is code for my dropdownlist:

    <asp:DropDownList ID="dropdownlist1" runat="server" DataSourceID="SqlDataSource1" DataMember="DefaultView" DataTextField="FullName" DataValueField="UniqueID"  onselectedindexchanged="dropdownlist1_SelectedIndexChanged" AutoPostBack="True" AppendDataBoundItems="true" BackColor="#CCCCFF">

    <asp:ListItem Value="0" Text="Please select an employee name to begin"></asp:ListItem>


    <asp:SqlDataSource ID="SqlDataSource1" runat="server"

     ConnectionString="<%$  ConnectionStrings:timesheetdbConnectionString4 %>" SelectCommand="SELECT [UniqueID], [FirstName]+', '+[LastName] AS 'FullName' FROM [EmployeeTB] ORDER BY [FullName]"></asp:SqlDataSource>

    Notice I combine column FirstName and LastName and return it into 1 column named FullName. SQL syntax is:

    SELECT column1+ ', ' +column2 AS "new_column"

    Also notice I include UniqueID column in the SELECT query because I will need that value to populate the gridview with associated information pulled from the database for the selected name from the dropdownlist.

    I added a static item "Please select an employee name to begin". It has value of 0 to make sure it always stay at the top and selected by default when the page is loaded at the first time. Also, when user select this item, nothing happens.

    To populate the gridview the information of, like "Bill Miers", I put these lines in my dropdownlist1_SelectedIndexChanged() method.

    string conn = ConfigurationManager.ConnectionStrings["timesheetdbConnectionString"].ConnectionString;

    SqlConnection connect = new SqlConnection(conn);


    SqlCommand cmd = new SqlCommand("SELECT * FROM timesheettb WHERE employeeid = '" + dropdownlist1.SelectedValue + "'", connect);

    SqlDataReader data_reader = cmd.ExecuteReader();

    GridView1.DataSource = data_reader;





    Refer back to where I create the dropdownlist, I associated the DataValueField with UniqueID being pulled from the table from the SELECT query.....DataValueField="UniqueID".

    Hope someone will find this helpful.




    Friday, December 3, 2010 10:38 AM

All replies

  • User970350993 posted


    Use parameterise query to avoid sql injection


    Friday, December 10, 2010 7:08 AM