得到错误，有人可以帮我解决吗？ [英] Got error, anyone can help me to fix it?

问题描述

我有两个表是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();
        }

推荐答案

您正尝试同时在同一连接上执行两个命令。



查看查询，我怀疑第一个查询只返回一个值。 （如果没有，您的第二个查询将不起作用。）尝试用 ExecuteScalar 替换第一个 ExecuteReader ：

You're trying to execute two commands on the same connection at the same time.

Looking at the queries, I suspect that the first query is only returning a single value. (If not, your second query isn't going to work.) Try replacing the first ExecuteReader with ExecuteScalar:

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

没问题 - 我可以看到问题。

您将会遇到错误得到的是：

It's ok - I can see the problem.
The error you will be getting is along the lines of:

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.

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