c#.net Error: There is already an open DataReader associated with this Command which must be closed first

Question

Help, I am new to asp.net c#. I tried the codes once before but it didn't seems to have this error. So I can't seems to figure where do I make changes to.

Sever error : There is already an open DataReader associated with this Command which must be closed first.

Codes:

string strConnectionString = ConfigurationManager.ConnectionStrings["FYPDB"].ConnectionString;

SqlConnection myConnect = new SqlConnection(strConnectionString);

string strCommandText = "select promoId FROM FYPDB.dbo.Promotions where membershipType LIKE '%' + @membership + '%' AND defaults LIKE '%' + @defaults + '%'";

try
{
    string ddlmembership = ((DropDownList)dvInsertPromotion.FindControl("ddlAddMembershiplist")).SelectedItem.ToString();
    string ddlDefault = ((RadioButtonList)dvInsertPromotion.FindControl("RadioButtonList2")).Text.ToString();

    DataSet da = dal.retrieveMembership(ddlmembership, ddlDefault);
    SiteGridView.DataSource = da;
    SiteGridView.DataBind();


    SqlCommand cmd = new SqlCommand(strCommandText, myConnect);

    cmd.Parameters.Add("@membership", SqlDbType.NVarChar);
    cmd.Parameters.Add("@defaults", SqlDbType.NVarChar);

    cmd.Parameters["@membership"].Value = ddlmembership;
    cmd.Parameters["@defaults"].Value = ddlDefault;

    myConnect.Open();
    SqlDataReader reader = cmd.ExecuteReader();


    if (reader.Read())
    {
        //get number of count
        //int count = da.Tables[0].Rows.Count;
        int count = (int)cmd.ExecuteScalar();

        if (count == 1)
        {
            defaultComfirm.Show();
            promotion = false;
        }

    }
    else
    {
        Label6.Text = "error didnt go through";
        Label6.ForeColor = System.Drawing.Color.Gray;
        Label6.Visible = true;
    }
    reader.Close();
}


finally
{
    myConnect.Close();
}

Possible duplicate of [Error "There is already an open DataReader associated with this Command which must be closed first" when using 2 distinct commands](http://stackoverflow.com/questions/18475195/error-there-is-already-an-open-datareader-associated-with-this-command-which-mu) — MusicLovingIndianGirl, May 09 '16 at 07:38
Here you're not just reusing the connection - you're calling `cmd.ExecuteReader()` and then `cmd.ExecuteScalar()`. That's not the way to do it... if you just want a count, use `SELECT COUNT(1) FROM ...` and then *just* use `ExecuteScalar`... — Jon Skeet, May 09 '16 at 07:41
Perhaps you should explain what do you want to achieve with this code. It seems that you are pretty confused. Are you just trying to check if _promoID_ exists for the conditions? If yes then your code could fail with == 1 because the LIKE operators could return more than one record — Steve, May 09 '16 at 07:46

score 2 · Accepted Answer · answered May 09 '16 at 07:47

you are using the same command object two times:

SqlDataReader reader = cmd.ExecuteReader();
int count = (int)cmd.ExecuteScalar();

This is not possible.

It is not necessary to use a reader if you just want to have the count of rows. In this case you can use the ExecuteScalar.

Like this:

string strCommandText = "select COUNT(promoId) FROM FYPDB.dbo.Promotions where membershipType LIKE '%' + @membership + '%' AND defaults LIKE '%' + @defaults + '%'";
        try
        {
        ...
        int count = (int)cmd.ExecuteScalar();
        ...

Regards Oliver

score 1 · Answer 2 · answered May 09 '16 at 07:56

Looking at your code, you just need count of rows matching the criteria.

So if you just need rows count you could use any of these options.

Option 1:

Find out count by reading all rows

int count =0;
while(reader.Read()) count++;
if (count == 1)
{
    defaultComfirm.Show();
    promotion = false;
}
else
{
    ...
}
reader.Close();

Option 2:

My preferred choice for this case, Modify your query to return the count (SELECT COUNT(*)).

string strCommandText = "select count(promoId) FROM FYPDB.dbo.Promotions where membershipType LIKE '%' + @membership + '%' AND defaults LIKE '%' + @defaults + '%'";

now,

int count = (int)cmd.ExecuteScalar();
if (count == 1)
{
    defaultComfirm.Show();
    promotion = false;
}
else
{
    ...
}
reader.Close();

Option 3:

Other option to convert it to DataTable and get the row count.

using (DataTable dt = new DataTable())
{
     dt.Load(reader);

    if (dt.Rows.Count == 1)
    {
        defaultComfirm.Show();
        promotion = false;
    }
    else
    {
        ...
    }
    reader.Close();
}

score 0 · Answer 3 · answered May 09 '16 at 07:43

try to execute cmd.ExecuteScalar() before cmd.ExecuteReader().

int count = (int)cmd.ExecuteScalar();
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    //get number of count
                    //int count = da.Tables[0].Rows.Count;                 
                    if (count == 1)
                    {
                        defaultComfirm.Show();
                        promotion = false;
                    }

                }

c#.net Error: There is already an open DataReader associated with this Command which must be closed first

3 Answers3