Got error, anyone can help me to fix it?

Question

0.00/5 (No votes)

See more:

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

C#

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

Posted 31-Jul-14 4:13am

AlexanderYap

Updated 31-Jul-14 5:39am

RaisKazi

v3

Add a Solution

Comments

OriginalGriff 31-Jul-14 10:14am

What's the error?
Any message?
Any particular line?

2 solutions

Solution 1

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

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.

Posted 31-Jul-14 4:23am

OriginalGriff

Comments

Rob Philpott 31-Jul-14 11:58am

Yes, you can. Connection string option thingy.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2014-07-31T04:25:00

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:

C#

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:

C#

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