Hi Alexander Suprum and RBarry Young,

I thank you both for your replies.

In the code behind for the Update report file in the overloaded constructor, there is the following line of code:

mySqlDataReader = OvenProgramDataByProgramID_returningSqlDataReader(ovenProgramID);

This function OvenProgramDataByProgramID_returningSqlDataReader is in the same code behind of the Update report and its code is as follows:

public static SqlDataReader OvenProgramDataByProgramID_returnSqlDataReader(int ovenProgramID)

{

SqlDataReader sqlRead = null;

try

{

SqlParameter[] theSqlParams = new SqlParameter[1];

theSqlParams[0] = new SqlParameter("@OvenProgramID", SqlDbType.Int);

theSqlParams[0].Value = ovenProgramID;

mySqlConnection = new SqlConnection(connString1);

sqlRead = SqlHelper.ExecuteReader(mySqlConnection, CommandType.StoredProcedure, "ap_OvenProgramDataByOvenProgramID", theSqlParams);

}

catch (Exception theErrorThatHappened)

{

if (sqlRead != null)

{

sqlRead.Close();

}

string stringError;

stringError = theErrorThatHappened.ToString();

}

return sqlRead;

}

The ReportEnd event of the Update report has the following code and has had the following code before I created this thread:

private void UpdateReport_PC6000_ReportEnd(object sender, EventArgs e)

{

if (BatchDataSubReport != null)

{

BatchDataSubReport.Dispose();

BatchDataSubReport = null;

}

if (mySqlDataReader != null)

{

mySqlDataReader.Close();

mySqlDataReader = null;

}

if (theSqlConnection != null)

theSqlConnection.Close();

if (mySqlConnection != null)

mySqlConnection.Close();

}

So the ReportEnd event happens before the Update report is loaded into the WebViewer of the ReportViewer.aspx file and when debugging I see that the code in the ReportEnd event runs.

Reading your replies though, I made some changes.

I added the following line at the top of the Update report’s code behind file:

private static SqlDataReader sqlReaderForFunction;

I then changed the code so that now the Update report’s code behind has the following function:

public static SqlDataReader OvenProgramDataByProgramID_returnSqlDataReader(int ovenProgramID)

{

sqlReaderForFunction = null;

try

{

SqlParameter[] theSqlParams = new SqlParameter[1];

theSqlParams[0] = new SqlParameter("@OvenProgramID", SqlDbType.Int);

theSqlParams[0].Value = ovenProgramID;

mySqlConnection = new SqlConnection(connString1);

sqlReaderForFunction = SqlHelper.ExecuteReader(mySqlConnection, CommandType.StoredProcedure, "ap_OvenProgramDataByOvenProgramID", theSqlParams);

}

catch (Exception theErrorThatHappened)

{

if (sqlReaderForFunction != null)

{

sqlReaderForFunction.Close();

}

string stringError;

stringError = theErrorThatHappened.ToString();

}

return sqlReaderForFunction;

}

I changed the code in the ReportEnd event of the Update report’s code behind to the following by adding an IF for this new sqlReaderForFunction:

if (BatchDataSubReport != null)

{

BatchDataSubReport.Dispose();

BatchDataSubReport = null;

}

if (mySqlDataReader != null)

{

mySqlDataReader.Close();

mySqlDataReader = null;

}

if (theSqlConnection != null)

theSqlConnection.Close();

if (mySqlConnection != null)

mySqlConnection.Close();

if (sqlReaderForFunction != null)

{

sqlReaderForFunction.Close();

sqlReaderForFunction = null;

}

So after I made the aforementioned changes, and when I am debugging and loading the report into the ReportViewer’s WebViewer control before the user has the ability to update the comment from the ReportViewer after the Update report has loaded, I put breakpoints in the ReportEnd event and the following happens:

#1 The code goes inside each IF

#2 The code goes inside the IF for mySqlDataReader and the code then closes it and then the code makes it null

#3 The code goes inside the IF about theSqlConnection and then the code closes it

#4 The code goes inside the IF for mySqlConnection and then the code closes it

#5 The code goes inside the IF for sqlReaderForFunction and then the code closes it and then the code makes it null

This past Friday when I posted this thread, the ReportEnd had this same code (except for the IF about the new sqlReaderForFunction that I added today) and that ReportEnd event fired when the ReportViewer file for the Update report loads that Update report. So the IF statements for the SqlDataReader do close and then make null the SqlDataReader and then the IF statements for the SqlConnections do close those connections.

Still though with my changes today I get the Timeout Expired error and that query that selects from db_os_waiting_tasks and joins with dm_exec_connections shows that the stored procedure that fills the mySqlDataReader in the Update report’s overloaded constructor is waiting with ASYNC_NETWORK_IO and that the stored procedure to update the Comments column is being blocked by the stored proc that fills the mySqlDataReader in the Update report’s constructor.

In the ReportViewer’s code behind, after the user clicks to update the Comment column in the table with his or her comment that he or she just entered, I tried when I select the existing string in the Comment column using WITH (NOLOCK) after the “from dbo.Ovenprogram” but this does not cause the error to not happen always. Yet sometimes I do not get the Timeout Expired error with or without the WITH (NOLOCK) in that Select. So using WITH (NOLOCK) in that Select does not solve always the problem. Sometimes though the Timeout Expired error does not happen and the update stored procedure in ReportViewer’s code behind runs fine and is not blocked by the stored proc that fills the mySqlDataReader in the Update report’s constructor. Late Friday afternoon, this past Friday as today is Monday 10-SEP-2012, I successfully saved a comment to the Update report after adding again the WITH (NOLOCK) yet today WITH or WITHOUT the WITH (NOLOCK) in that Select it is not possible for me now to save a comment for that same Update report. So sometimes the update stored proc is not blocked by that other stored proc and the comment is saved/updated while other times the Timeout Expired error happens and that Update report’s stored proc blocks the update stored proc of the Update report’s ReportViewer file.

Wait a second. I continued debugging after the ReportEnd event of the Update report’s code behind and noticed something.

Okay, the user is on a web page named Options_TwoListboxesOnlyOneDate.aspx. Here the user checks or unchecks checkboxes concerning what probes he or she wants to graph, whether or not he wants to see the probe calibration comments, et cetera. On this Options_TwoListboxesOnlyOneDate.aspx, the user must choose a bolded date from the calendar to see what oven controls had cook programs run that day, then when the user clicks on whichever oven control he wants a report a different listbox shows all of the cook programs that were run on that day for that selected oven control, and then the user clicks to view the report to redirect to the ReportViewer.aspx file and its code behind loads the Update report into its WebViewer control. The program then goes into the Update report’s code behind at this point. The programs goes through the Update report’s code and goes through the ReportEnd event of the Update report’s code behind.

Now the Update report has loaded into the ReportViewer web page.

When the user clicks to save his or her comment to the existing string of comments in the Comments column of a table, I noticed that the program goes back into the constructor of the Update report and goes through the line of code “mySqlDataReader = OvenProgramDataByProgramID_returnSqlDataReader(ovenProgramID);” but the program does NOT go through the Update report’s ReportEnd event again. I guess after the user clicks on ReportViewer.aspx to save the comment when the Update report is already loaded into the ReportViewer file, the post back causes the program to go back into the constructor of the Update report file through that line of code “mySqlDataReader = OvenProgramDataByProgramID_returnSqlDataReader(ovenProgramID);” but then the program does not go through the Update report’s other events and methods like FetchData and ReportEnd events but rather leaves the Update report’s constructor having gone through it, then goes into the Dispose of the Update report’s Designer.cs file, then the program goes back to the ReportViewer’s code behind after it has done its code “WebViewer.Report = new UpdateReport_PC6000(OvenProgramID, options, probes, GraphSizeFromDropDown), and then the program goes into the Master page’s code behind, and then while debugging the program goes into a new tab in Visual Studio 2010 that is named Disassembly.

Yet sometimes the update comment is saved successfully with no problem even though after clicking on the Update report’s ReportViewer file to save a comment it seems that the postback in its ReportViewer file causes the program to go again into just the constructor of the Update report.

A former employee created this web app. I created in it these ActiveReports 6 code-based reports and I was told to create the reports in the web app just like they exist and work in a big Windows application.

So in this web app, there is a Complete report that has exactly the same code as the Update report but the Complete report is loaded into a DIFFERENT ReportViewer file that is named differently because the Complete report does not allow the user to add a comment. So the web app’s Complete report itself is exactly the same as the Update report (in their code-behind files) but the Complete report has a different ReportViewer file that is named differently because the user only uses the Update report when he or she wants to add a comment. The complete report loads without any problem. Well heck, the Update report loads without any problem too.

Anyway, it seems that when the program is in the ReportViewer file for the Update report and instantiates a new Update report to load it into the ReportViewer file for the Update report and the program goes into the Update report for the first time, the ReportEnd event of the Update report closes the SqlDataReader and then makes it null AND ALSO closes the SqlConnections.

I am confused about this problem, especially because sometimes the update of the comments works with no problem while other times this Timeout Expired exception occurs because it seems the stored proc that fills the SqlDataReader in the constructor of the Update report file is blocking the update stored procedure that is used from the Update report’s ReportViewer file.

I thank you both though very much for your replies. It seems that the SqlDataReaders and SqlConnections are indeed closed in the Update report’s ReportEnd event when the Update report is loaded into its ReportViewer file.

Hviezdoslav

Monday

10-SEP-2012