Executing multiple SQL commands INSERT and SELECT using SQL Reader and Response.Write

Question

Response.Write("text"); is rendering on my page load but when I click my button, it does not print.

I am trying to carry out two INSERT commands and one SELECT. I am using the Response.Write to test if the SELECT command is getting the ID I need.

The INSERT commands are working successfully (as the data is been sent to the database) but for some reason I can not access the SprintBacklogID from the Select command. I need the value of the ID to use in a if statement but for the time been, I just need to be able to access the variable in the SELECT statement.

protected void Page_Load(object sender, EventArgs e)
{
    Response.Write("test");
}

protected void addSprintTasks(object sender, EventArgs e)
{
    Response.Write("another test");
    string taskDescription = Desc.Text;
    string taskHours = Hours.Text;
    string hoursLeft = Hours.Text;
    string ProductStoryID = Request.QueryString["ProductStoryID"];
    string SprintBacklogID = Request.QueryString["SprintBacklogID"];

    int TaskId;

    string connectionString = WebConfigurationManager.ConnectionStrings["GiraConnection"].ConnectionString;
    SqlConnection myConnection = new SqlConnection(connectionString);

    myConnection.Open();

    //string query = "INSERT INTO [SprintTasks] (TaskDescription , EstimatedHours, ProductStoryID, RemainingHours, SprintBacklogID) VALUES (@TaskDescription , @EstimatedHours, @ProductStoryID, @RemainingHours, @SprintBacklogID)";
    //string query1 = "INSERT INTO DailyBurndown (TaskID,Date,HoursRemaining) VALUES (@TaskID, @EstimatedHours, @RemainingHours)";

    //create two insert statements under the one connection so the variables can be referenced

    string sql1 = "BEGIN INSERT INTO [SprintTasks] (TaskDescription , EstimatedHours, ProductStoryID, RemainingHours, SprintBacklogID) VALUES (@TaskDescription , @EstimatedHours, @ProductStoryID, @RemainingHours, @SprintBacklogID)SET @ID = SCOPE_IDENTITY();",
           //sqlTest = @"SELECT SprintID FROM SprintTasks WHERE (SprintTasks.SprintBacklogID = @SprintBacklogID);",
           sql2 = "INSERT INTO [DailyBurndown] (TaskID,Date,HoursRemaining) VALUES (@ID, @EstimatedHours, @EstimatedHours); END;";

    string sql = string.Format("{0}{1}", sql1, sql2);


    string query1 = "SELECT SprintBacklogID FROM SprintTasks WHERE (SprintTasks.SprintBacklogID = @SprintBacklogID)";

    //using sql1 and sql2

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand myCommand = new SqlCommand(sql, connection))
        {

            connection.Open();

            myCommand.Parameters.AddWithValue("@TaskDescription", taskDescription);
            myCommand.Parameters.AddWithValue("@EstimatedHours", taskHours);
            myCommand.Parameters.AddWithValue("@RemainingHours", hoursLeft);
            myCommand.Parameters.AddWithValue("@ProductStoryID", ProductStoryID);
            myCommand.Parameters.AddWithValue("@SprintBacklogID", SprintBacklogID);

            //Takes the ID for the task just added to the Sprint Sub Tasks table using Scope Identity
            myCommand.Parameters.Add("@ID", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
            myCommand.ExecuteNonQuery();

            //change over sql statement
            myCommand.CommandText = query1;
            myCommand.ExecuteNonQuery();

            using (SqlDataReader reader = myCommand.ExecuteReader())
            {
                while (reader.Read())
                {
                   Response.Write(reader["SprintBacklogID"].ToString());
                }
            }
            Response.Redirect(Request.RawUrl);
            connection.Close();
        }
    }

    myConnection.Open();

}

Answer 1

You are using Response.Redirect(Request.RawUrl);, so assuming the output is being buffered: anything you wrote to the page is discarded, with the redirect taking precedence.