Search code examples
c#asp.nett-sqlconnection-timeout

Asp.net web form "The wait operation timed out"

I have a web form built to retrieve data from a reporting server. I don't have rights to create an index on the table I'm querying in that server, and the query is very, very slow on the text lookup. I've tried optimizing what I can in the query, but it's just going to be slow. So I need to find a way to avoid getting the timeout in the webpage itself. I have reviewed several similar questions, I set a crazy high connection timeout, but I'm still getting the wait operation timeout on the button click. The timeout happens after 30 seconds, even though I have set such a high connection timeout. I don't know if it's the web config, but I'm not using the connection string defined in my webconfig, just this one here in the codebehind. The query does not time out in SSMS, only when accessing the data from the webform (which technically is using my login credentials in the connection string, so there is no sort of permissions issues, other portions run fine). Is there any where else I need to increase the timeout for the page?

Connection string (un/pw x'ed out for privacy):

server=lxxxxxx.resources.xxx; Initial Catalog=pxxxxxxxx;User ID=mfeXXXXX;Password=XXXXXX;Trusted_Connection=False; Connection Timeout=10000"

query that times out, if that helps:

 protected void btnSearchFeedback_Click(object sender, EventArgs e)
    {
        con.Open();
        SqlDataAdapter sdaFeedBack = new SqlDataAdapter("SELECT CTIR.portion_ID, IB.Item_ID as [Item ID], CTIR.Item_DBkey as [Item DB Key], CTIR.Feedback as [Feedback], CTA.date as [Date],CTA.Candidate_ID as [Candidate ID] FROM Canxxxxxxx CTIR WITH (NOLOCK) left join Ixxxxx IB WITH (NOLOCK) ON CTIR.xxxxx = IB.xxxxx left join Cxxxxxx CTA WITH (NOLOCK) ON CTxxxxxx = CTxxxxxxx where (ctir.portion_id in (" + portionIDTextBox.Text + ") ) AND (len(CTIR.Feedback) > 0) and CTA.Date  > '2015-02-01 00:00:00.000' order by [Date]", con);
        DataSet dtFeedBack = new DataSet();
        sdaFeedBack.Fill(dtFeedBack);
        ViewState["dtFeedback"] = dtFeedBack;
        rptFeedBack.DataSource = dtFeedBack;
        rptFeedBack.DataBind();
        con.Close();
    }

Again, x'ed out table names for security. So I am trying to see if there is another location I need to set the connection timeout to a higher number to avoid the "Server Error in '/' Application. The wait operation timed out", or is this something that needs to be handled on the server side?

This is the exact error:

Server Error in '/' Application.

The wait operation timed out Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ComponentModel.Win32Exception: The wait operation timed out

stack trace: [Win32Exception (0x80004005): The wait operation timed out]

[SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) +2434922 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) +5736592 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +285 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +3731 System.Data.SqlClient.SqlDataReader.TrySetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo) +198 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +2852 System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +58 System.Data.SqlClient.SqlDataReader.get_MetaData() +89 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +379 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest) +2026 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +375 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +53 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +240 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +41 System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +12 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +139 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +136 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +88 FeedBackForm.btnSearchFeedback_Click(Object sender, EventArgs e) in C:\Users\mfelchlin\Documents\Visual Studio 2015\WebSites\devFormsSite\FeedBackForm.aspx.cs:27 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +9696694 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +204 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +12 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +15 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +35 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1639

Solution 
  • sdaFeedBack.SelectCommand.CommandTimeout=120; //or some other number of seconds.

    Setting the timeout on the connection determines how long to wait for the connection to open, not for the command to execute.