得到错误，有人可以帮我解决吗？

问题描述

我有两个表是Route和Schedule，我想在下拉列表中显示ScheduleDepartDate。但它说已经有一个与此命令关联的开放DataReader必须先关闭。

I have two table which are Route and Schedule, I want to display the ScheduleDepartDate in my drop down list. But it say "There is already an open DataReader associated with this Command which must be closed first."

protected void iDD2_SelectedIndexChanged(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(conStr);
            con.Open();
            SqlCommand cmdRouteID = new SqlCommand("SELECT RouteID From Route WHERE RouteDepartPlace = @DepartPlace",con);
            cmdRouteID.Parameters.AddWithValue("@DepartPlace", DropDownList1.Text);
            SqlDataReader rdr = cmdRouteID.ExecuteReader();
            SqlCommand cmdDate = new SqlCommand("SELECT ScheduleDepartDate From Schedule WHERE RouteID = @Des", con);
            cmdDate.Parameters.AddWithValue("@Des", rdr);
            SqlDataReader reader = cmdDate.ExecuteReader();

            DropDownList3.DataSource = reader;
            DropDownList3.DataValueField = "ScheduleDepartDate";
            DropDownList3.DataTextField = "ScheduleDepartDate";
            DropDownList3.DataBind();
        }

推荐答案

using (SqlConnection con = new SqlConnection(conStr))
{
    con.Open();

    object routeID;
    using (SqlCommand cmdRouteID = new SqlCommand("SELECT RouteID From Route WHERE RouteDepartPlace = @DepartPlace", con))
    {
        cmdRouteID.Parameters.AddWithValue("@DepartPlace", DropDownList1.Text);
        routeID = cmdRouteID.ExecuteScalar();
    }

    using (SqlCommand cmdDate = new SqlCommand("SELECT ScheduleDepartDate From Schedule WHERE RouteID = @Des", con))
    {
        cmdDate.Parameters.AddWithValue("@Des", routeID);

        using (SqlDataReader reader = cmdDate.ExecuteReader())
        {
            DropDownList3.DataSource = reader;
            DropDownList3.DataValueField = "ScheduleDepartDate";
            DropDownList3.DataTextField = "ScheduleDepartDate";
            DropDownList3.DataBind();
        }
    }
}

或者，您可以合并两个查询：

Alternatively, you could merge the two queries:

using (SqlConnection con = new SqlConnection(conStr))
using (SqlCommand cmdDate = new SqlCommand("SELECT S.ScheduleDepartDate From Schedule As S INNER JOIN Route As R ON R.RouteID = S.RouteID WHERE R.RouteDepartPlace = @DepartPlace", con))
{
    cmdDate.Parameters.AddWithValue("@DepartPlace", DropDownList1.Text);

    con.Open();
    using (SqlDataReader reader = cmdDate.ExecuteReader())
    {
        DropDownList3.DataSource = reader;
        DropDownList3.DataValueField = "ScheduleDepartDate";
        DropDownList3.DataTextField = "ScheduleDepartDate";
        DropDownList3.DataBind();
    }
}

There is already an open DataReader associated with this Command which must be closed first

这是非常自我解释的。

你不能在同一个连接对象上打开两个读者。要么完成第一个阅读器并关闭它，要么为第二个阅读器打开第二个连接。



或者改为使用SqlDataAdapters。

Which is pretty self explanatory.
You can't open two readers on the same connection object. Either finish the first reader and close it, or open a second connection for the second reader.

Or use SqlDataAdapters instead.

这篇关于得到错误，有人可以帮我解决吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！