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.