System.Data.SqlClient.SqlException: Timeout expired.

[Sam]

I had large database (transaction record > 1 million) at SQL Server and I
trying to load data from SQL Server into Datagrid via ASP.Net 1.1 and
ADO.Netx.

However, it give error message as below during processing.

While I checked at Query Analyser, it take 2 minutes + for search this
record and it is working fine.

Questions?
- What wrong at my coding? How about web.config?
- How to resolve this issue?
- Why ADO.Net / ASP.Net 1.1 does not support large connection pooling?

Please advise.

Many thanks.


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

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.Data.SqlClient.SqlException: Timeout expired. The
timeout period elapsed prior to completion of the operation or the server is
not responding.

Source Error:
Line 90: Try
Line 91: rptcon.Open()
Line 92: Dim okrd As SqlDataReader = rptcmd.ExecuteReader()
Line 93:
Line 94: ReportDataGrid.DataSource = okrd


ASPX Coding
--------------
Dim rptcon As New
SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim rptcmd As New SqlCommand()

rptcmd.CommandText = "Select GLDOC, GLDCT, gldate, GLEXA,
GLEXR, account_num, amount from f0911"
rptcmd.Connection = rptcon

Try
rptcon.Open()
Dim okrd As SqlDataReader = rptcmd.ExecuteReader()

ReportDataGrid.DataSource = okrd
ReportDataGrid.DataBind()

okrd.Close()

Finally
rptcon.Close()
End Try

End If

Web.Config Coding
---------------------
<!-- Web.Config Configuration File -->
<configuration>
<appSettings>
<add key="ConnectionString"
value="server=SQLSVR;database=ERP;uid=User;pwd=User;" />
</appSettings>
<system.web>
<compilation debug="true" />
<authentication mode="Forms">
<forms name="ERP" loginUrl="authentication.aspx"
protection="All" path="/" timeout="20"/>
</authentication>
<authorization>
<allow users="user1,user2"/>
<deny users="*"/>
</authorization>
<customErrors mode="Off" />
</system.web>
</configuration>

 

[Guest]

Hi Sam,

You can add "Connect Timeout=150" in the connection string as given below
and try.
"server=SQLSVR;database=ERP;uid=User;pwd=User;Connect Timeout=150"

This should solve the time out issue in fetching the data.

 

[Sam]

Even I changed the connection timeout at web.config. it is still same. Any
advise, please?

Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding.
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.Data.SqlClient.SqlException: Timeout expired. The
timeout period elapsed prior to completion of the operation or the server is
not responding.

Source Error:

Line 113: Try
Line 114: rptcon.Open()
Line 115: Dim okrd As SqlDataReader = rptcmd.ExecuteReader()
Line 116:
Line 117: ReportDataGrid.DataSource = okrd


Source File:
D:\SQLDB\Intranet\WebApp\Finance\subsystem\gl\report_general_journal.aspx
Line: 115

Stack Trace:

[SqlException: Timeout expired. The timeout period elapsed prior to
completion of the operation or the server is not responding.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +742
System.Data.SqlClient.SqlCommand.ExecuteReader() +42
ASP.report_general_journal_aspx.OK(Object Sender, EventArgs e) in
D:\SQLDB\Intranet\WebApp\Finance\subsystem\gl\report_general_journal.aspx:115
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1292

Version Information: Microsoft .NET Framework Version:1.1.4322.2032; ASP.NET
Version:1.1.4322.2032

 

[Guest]

Hi,

You can try this:

rptcon.Open()
rptcmd.CommandTimeout = 150
Dim okrd As SqlDataReader = rptcmd.ExecuteReader()

Saravanan K V

Even I changed the connection timeout at web.config. it is still same. Any
advise, please?

Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding.
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.Data.SqlClient.SqlException: Timeout expired. The
timeout period elapsed prior to completion of the operation or the server is
not responding.

Source Error:

Line 113: Try
Line 114: rptcon.Open()
Line 115: Dim okrd As SqlDataReader = rptcmd.ExecuteReader()
Line 116:
Line 117: ReportDataGrid.DataSource = okrd


Source File:
D:\SQLDB\Intranet\WebApp\Finance\subsystem\gl\report_general_journal.aspx
Line: 115

Stack Trace:

[SqlException: Timeout expired. The timeout period elapsed prior to
completion of the operation or the server is not responding.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +742
System.Data.SqlClient.SqlCommand.ExecuteReader() +42
ASP.report_general_journal_aspx.OK(Object Sender, EventArgs e) in
D:\SQLDB\Intranet\WebApp\Finance\subsystem\gl\report_general_journal.aspx:115
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1292

Version Information: Microsoft .NET Framework Version:1.1.4322.2032; ASP.NET
Version:1.1.4322.2032

Hi Sam,

You can add "Connect Timeout=150" in the connection string as given below
and try.
"server=SQLSVR;database=ERP;uid=User;pwd=User;Connect Timeout=150"

This should solve the time out issue in fetching the data.