ASP.NET Multiple Queries not executing within a loop

Question

I am trying to run multiple queries within a loop. The first query runs ok as I can see it when I step through the code.

However the second query (which is within a loop) is supposed to run depending on the value held from the first. When the loop runs based on that value it seems to be ignoring the query. I put a label to display in place of the query and it displayed so I believe how I have opened/closed my connection is not correct.

c# code:

  protected void Page_Load(object sender, EventArgs e)
    {

        // Get the session of the user
        string staffid = Session["StaffId"].ToString();

        //Proxy on page load to check IsActive Status
        string DefaultConnection = WebConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

        SqlConnection myConnection = new SqlConnection(DefaultConnection);
        myConnection.Open();

        //select the userdetail specific to the logged in user using parameterisation 
        string query = "SELECT ProxyStatus.ProxyStatusId, ProxyStatus.FunctionId, ProxyStatus.StartDate, ProxyStatus.EndDate, ProxyStatus.IsActive FROM ProxyStatus INNER JOIN Staff ON Staff.StaffId = ProxyStatus.Proxee WHERE (Staff.StaffId = @StaffId)";

        DateTime thisDay = DateTime.Today;

        SqlCommand myCommand = new SqlCommand(query, myConnection);

        myCommand.Parameters.AddWithValue("@staffid", staffid);
        SqlDataReader rdr = myCommand.ExecuteReader();

        if (rdr.HasRows)
        {

            while (rdr.Read())
            {
                Session["StartDate"] = rdr["StartDate"].ToString();
                Session["EndDate"] = rdr["EndDate"].ToString();
                Session["ProxyStatusId"] = rdr["ProxyStatusId"].ToString();
                Session["FunctionId"] = rdr["FunctionId"].ToString();

                // Get the session of StartDate and endate, use the session value in a query to compare against the current date
                string startdate = Session["StartDate"].ToString();
                string enddate = Session["EndDate"].ToString();
                string proxystatus = Session["ProxyStatusId"].ToString();

                DateTime startdatedata = Convert.ToDateTime(startdate);
                DateTime enddatedata = Convert.ToDateTime(enddate);

                if (startdatedata > thisDay)
                {
                    string DefaultConnection2 = WebConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

                    SqlConnection myConnection2 = new SqlConnection(DefaultConnection2);
                    myConnection2.Open();

                    string query2 = "UPDATE ProxyStatus SET ProxyStatus.IsActive = 'False' WHERE ProxyStatus.ProxyStatusId = @proxystatus";


                    myCommand.Parameters.AddWithValue("@newproxystatus", proxystatusnew); 

                    SqlCommand myCommand2 = new SqlCommand(query2, myConnection2);

                         myCommand2.ExecuteNonQuery();

                }
            } 
        }
       else
         {
            rdr.Close();
         }
    }
}

}

I don't see where you are executing the second query in your code. The command object is constructed but no ExecuteReader(). — Dan Guzman
– Dan Guzman, Commented Apr 22, 2017 at 18:47
When I add in an ExecuteReader i get the following error: "There is already an open DataReader associated with this Command which must be closed first." See changes above — Stephen Crothers
– Stephen Crothers, Commented Apr 22, 2017 at 18:53

gmalenko · Accepted Answer · 2017-04-22 19:01:19Z

2

Shouldn't the lines be

SqlCommand myCommand2 = new SqlCommand(query2, myConnection2);
myCommand.ExecuteNonQuery();

be

SqlCommand myCommand2 = new SqlCommand(query2, myConnection2);
myCommand2.ExecuteNonQuery();

instead? The first "myCommand" will still be in use with "rdr".

answered Apr 22, 2017 at 19:01

gmalenko

1625 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Stephen Crothers Over a year ago

Ok so that got the query running, but I am not getting the error: Must declare the scalar variable "@newproxystatus". Any idea what this could be? When I run it using breakpoints it is picking up the ProxyStatusId as 7 (which is what I want) but then its crashing.

gmalenko Over a year ago

In your query 2 string. You have "@proxystatus". However in the next line you are referencing "@newproxystatus". You have to name them the same.

Collectives™ on Stack Overflow

ASP.NET Multiple Queries not executing within a loop

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related