It can be really frustrating in ASP.NET when you get the  timeout expired.  By default SQL server allows a 30sec timeout for queries.  The problem is that sometimes we have queries that pull large amounts of data on an old server and it is just going to take  >30 seconds to execute.  For instance, I have a few reports that I created for the end of the year that have to crunch a years worth of data that can take a long time.   Other thing is if you’re using a Sqldatasource there is no great way to increase the timeout.  This is a simple way to use codebehind to populate your gridview while giving you the flexibility to increase the timeout to whatever you want.

The error you usually get is the following:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

To accomplish this you may need to put Imports statements up at the very top of the page like so:

Imports System.Data.SqlClient
Imports System.Data.Sql
Imports System.Data.SqlTypes

Then create your SQL String like you normally would.

Strsql1 = “SELECT dbo.[ridiculous_amt_of_data].[moundofdata] FROM dbo.[ridiculous_amt_of_data]”

Then add the following obviously substituting your information.

Dim MyConnection As System.Data.SqlClient.SqlConnection
Dim MyCommand As System.Data.SqlClient.SqlCommand
Dim MyDataReader As System.Data.SqlClient.SqlDataReader
MyConnection = New System.Data.SqlClient.SqlConnection("server=YourIPAddressOrHostname; initial catalog=YourDatabaseNameHere;uid=YourUsername;pwd=YourPassword")
MyConnection.Open()
MyCommand = New System.Data.SqlClient.SqlCommand
MyCommand.Connection = MyConnection

Set your sql string here

MyCommand.CommandText = strsql1

Adjust your timeout here

MyCommand.CommandTimeout = "120"
MyDataReader = MyCommand.ExecuteReader

Change These To Your Gridview Name  And Then Close Everything Out  

GridView1.DataSource = MyDataReader
GridView1.DataBind()
MyDataReader.Close()
MyDataReader = Nothing
MyCommand = Nothing
MyConnection.Close()
MyConnection = Nothing

Another reason that this error may happen, according to MS, is that there are uncommitted transactions in the database. Here’s some more information on the topic.

http://blogs.msdn.com/b/spike/archive/2008/07/31/timeout-expired-the-timeout-period-elapsed-prior-to-completion-of-the-operation-or-the-server-is-not-responding.aspx

Advertisements