如何使用c＃和存储过程从数据库绑定dropdownlist

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 ddlEmployees and an Employee table which has a column of empFname aswell as a stored procedure selectEmployeePaycheck which has a parameter of @fname, that matches the value in the Employee table which has a column of empFname. I'm using the code in the bottom to bind the data from database, but unfortunately it doesn't work.

我想知道，使用存储过程和C＃将下拉列表中的数据绑定到数据库的正确方法是什么。我有一个下拉列表ddlEmployees和一个Employee表，它有一列empFname以及一个存储过程selectEmployeePaycheck，其参数为@fname，与Employee表中具有empFname列的值相匹配。我正在使用底部的代码来绑定数据库中的数据，但不幸的是它不起作用。

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

Any suggestions, corrections, answers are very well accepted.

任何建议，更正，答案都被广泛接受。

3 个解决方案

#1

I have made small modifications in your code, just replace it with this:

我在您的代码中进行了少量修改，只需将其替换为：

SqlConnection con; 
SqlCommand cmd;
SqlDataAdapter adr;
DataTable dt;
string conStr;

conStr = ConfigurationManager.ConnectionStrings["payRollConnection"].ConnectionString;
using(con = new SqlConnection(conStr)) {
        using(cmd = new SqlCommand("selectEmployeePayCheck", con)) {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@fname", ddlEmployees.SelectedValue);
        // or cmd.Parameters.AddWithValue("@fname", ddlEmployees.SelectedText.ToString()); 
        // according to your query at the backend
        adr = new SqlAdapter(cmd);
        dt = new DataTable();
        adr.Fill(dt);
        }
    }

ddlEmployees.DataSource = dt;
ddlEmployees.DataTextField = "empFname";
ddlEmployees.DataValueField = "empFname";
ddlEmployees.DataBind();

Hope it helps. :)

希望能帮助到你。 :)

#2

Try by moving data source before field set, also you don't need excute reader here

尝试在字段设置之前移动数据源，此处也不需要执行读取器

adr.Fill(dt);
//cmd.ExecuteReader();
//set data source first
ddlEmployees.DataSource = dt;
ddlEmployees.DataTextField = "empFname";
ddlEmployees.DataValueField = "empFname";
ddlEmployees.DataBind();

#3

Looking at the comments and the original question what I believe to be your scenario is the following:

查看评论和原始问题我认为您的方案如下：

You have an Employees table containing employee records and some sort of table that has paycheck details.

您有一个Employees表，其中包含员工记录和某种具有薪水详细信息的表。

What you are trying to achieve is when the user selects an employee you show their paychecks in the dropdown?

您要实现的目标是当用户选择您在下拉列表中显示薪水的员工时？

If this is the case I would approach it as follows:

如果是这种情况，我会按如下方式处理：

Use 2 dropdownlists, one for you employees and one for the paycheck records.

使用2个下拉列表，一个为您的员工，一个为薪水记录。

Use the code from Damiths answer to populate your employee dropdown. Make sure that this dropdown has autopostback enabled and that you hook into the selectedindexchanged event for this dropdown.

使用Damiths代码中的代码填充您的员工下拉列表。确保此下拉列表已启用autopostback，并且您为此下拉列表挂钩了selectedindexchanged事件。

On the selected index changed event use your original code, using the ddlEmployee.selectedvalue, to get the selected employee and populate your paycheck dropdown.

在选定的索引更改事件上，使用原始代码（使用ddlEmployee.selectedvalue）获取所选员工并填写薪水检查下拉列表。

Also, make sure that if you are populating your employee dropdown in the page_load event that you wrap it in

此外，如果要在封装它的page_load事件中填充员工下拉列表，请确保这样做

if(!isPostback)
{

}

otherwise the dropdown will repopulate on every postback, causing the salary records of the first employee to always be shown.

否则下拉列表将在每次回发时重新填充，从而导致始终显示第一个员工的工资记录。

I hope this answers helps you to see the direction that you need to follow.

我希望这些答案可以帮助您了解您需要遵循的方向。

#1