CODEWITHSUNDEEP
c#asp.netsql-server
Stephen Crothers
Stephen Crothers

Reputation: 3

ASP.NET Multiple Queries not executing within a loop

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

}

Upvotes: 0

Views: 150

Answers (1)

gmalenko
gmalenko

Reputation: 162

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

Upvotes: 2

Related Questions