Fill in a DropDownList with data from datasource

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:DropDownList>

<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);

connect.Open();

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

SqlDataReader data_reader = cmd.ExecuteReader();

GridView1.DataSource = data_reader;

GridView1.DataBind();

data_reader.Close();

connect.Close();

=================

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.

Re: Fill in a DropDownList with data from datasource

Use parameterise query to avoid sql injection

http://davidhayden.com/blog/dave/archive/2005/10/24/2528.aspx