How to bind dropdownlist from database using c# and stored procedure
<p>I would like to know, what is the correct way to bind data on a drop down list from a database using stored procedure and C#. I have a drop down list <code>ddlEmployees</code> and an <code>Employee</code> table which has a column of <code>empFname</code> aswell as a stored procedure <code>selectEmployeePaycheck</code> which has a parameter of <code>@fname</code>, that matches the value in the <code>Employee</code> table which has a column of <code>empFname</code>. I'm using the code in the bottom to bind the data from database, but unfortunately it doesn't work. </p>

<pre><code>SqlConnection con;
SqlCommand cmd;
SqlDataAdapter adr;
DataTable dt;
string conStr;

conStr = ConfigurationManager.ConnectionStrings["payRollConnection"].ConnectionString;
con = new SqlConnection(conStr);
con.Open();
cmd = new SqlCommand("selectEmployeePayCheck", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@fname", ddlEmployees.SelectedValue);
adr = new SqlAdapter(cmd);
dt = new DataTable();
adr.Fill(dt);
cmd.ExecuteReader();
ddlEmployees.DataTextField = "empFname";
ddlEmployees.DataValueField = "empFname";
ddlEmployees.DataSource = dt;
ddlEmployees.DataBind();
cmd.Dispose();
con.Close();
</code></pre>

<p>Any suggestions, corrections, answers are very well accepted.</p>