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