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

Answer 1

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

Answer 2

you are using the same command object two times:

1. SqlDataReader reader = cmd.ExecuteReader();
2. 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

Answer 3

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

                }