asp.net c# SqlDataSource timout problems

Question

I'm trying to extend the timeout of an SqlDataSource beyond 30 second (Seems to be the default). I'm trying to run a stored procedure that has to run through 100,000s of records. During busy periods it times out. I'm using ASP.NET 4.0 and IIS 6.0 on 2003 server.

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

I've tried to no avail to extend the timeout:

< asp:SqlDataSource ID="dsTest" EnableCaching="true" CacheDuration="604800" runat="server" ConnectionString="<%$ ConnectionStrings:SuperNARIC %>" SelectCommand="selectStatus" SelectCommandType="StoredProcedure" onselecting="dsTest_Selecting" >
    <SelectParameters>
        < asp:ControlParameter ControlID="ddlCar" Name="CountryID" PropertyName="SelectedValue" Type="Int32" />
    < /SelectParameters>
< /asp:SqlDataSource>



protected void dsTest_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
    {
        e.Command.CommandTimeout = 300;
    }

Any help would be greatly appreciated.

Thanks

Community · Accepted Answer · 2017-05-23 11:47:07Z

17

Like mentioned here, this worked for me.

You can increase the Timeout property like this

protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
        {
            e.Command.CommandTimeout = 0;
        }

Setting timeout to 0 means no timeout

edited May 23, 2017 at 11:47

CommunityBot

11 silver badge

answered Feb 21, 2014 at 14:59

Harry

1,3433 gold badges22 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Anthony Horne · Accepted Answer · 2018-07-06 09:37:30Z

11

There are two types of timeout: ConnectionTimeout and CommandTimeout:

ConnectionTimeout Determines the maximum time your app will wait to establish a connection to your server.

CommandTimeout: Max time allowed for a command to execute.

Make sure you set both. In the Comand:

 command.CommandTimeout = 300;

Note: This can be implemented in the Selecting event should your command be part of a DataSource. e.Command.CommandTimeout = 0; A value of 0 means to wait indefinitely.

And connection string:

SqlConnectionStringBuilder cs = new SqlConnectionStringBuilder(connectionString);
cs.ConnectTimeout = 300;

Or:

<add name="MyConnectionString" connectionString="Data Source=.\SQLEXPRESS; Database=MyDB; Integrated Security=True;Pooling=True;connection timeout=30" providerName="System.Data.SqlClient" />

Note: Try setting the connection string timeout globally, perhaps in your config file.

edited Jul 6, 2018 at 9:37

Anthony Horne

2,5202 gold badges31 silver badges54 bronze badges

answered Apr 23, 2012 at 19:26

Ulises

13.4k5 gold badges36 silver badges52 bronze badges

2 Comments

user1024416 Over a year ago

Hi, I have the following in my web.config: Connection Timeout=180

Ulises Over a year ago

Cool, pump it up to 500 just for testing purposes and see.

Schenz · Accepted Answer · 2012-04-23 19:09:07Z

3

Timeout is generally set in your connection string. See http://www.connectionstrings.com/ for complete examples.

answered Apr 23, 2012 at 19:09

Schenz

1,1138 silver badges14 bronze badges

1 Comment

user1024416 Over a year ago

Hi, I have the following in my web.config: Connection Timeout=180

Bonfix Ngetich · Accepted Answer · 2019-02-12 10:35:10Z

2

This is what worked for me:

  <script runat="server">
    protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
    {
    e.Command.CommandTimeout = 0;
    }
    </script>


<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
ConnectionString="Your Connection String" 
ProviderName="Teradata.Client.Provider"
SelectCommand="A SELECT COMMAND THAT TAKES A LONG TIME"
DataSourceMode="DataSet"
onselecting="SqlDataSource1_Selecting">

answered Feb 12, 2019 at 10:35

Bonfix Ngetich

1,26713 silver badges17 bronze badges

Comments

saluce · Accepted Answer · 2012-04-23 19:11:52Z

1

You need to ensure both your CommandTimeout and your ConnectionString's Connect Timeout are set to prevent timing out on long-running stored procedures. If you don't set the connection timeout, you'll timeout before the stored procedure is finished, even if the stored procedure command itself hasn't timed out.

answered Apr 23, 2012 at 19:11

saluce

13.4k3 gold badges52 silver badges68 bronze badges

1 Comment

user1024416 Over a year ago

Thank you for the quick responses. I have the following in my web.config: Connection Timeout=180

Nikhil Agrawal · Accepted Answer · 2012-04-23 19:09:20Z

0

Connect Timeout/Connection Timeout/Timeout Default Value - 15 sec The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. Valid values are greater than or equal to 0 and less than or equal to 2147483647.

string myconstr = "Data Source=(local);Initial Catalog=AdventureWorks;"
    + "Integrated Security=SSPI;Connection Timeout=30"

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(v=vs.100).aspx

answered Apr 23, 2012 at 19:09

Nikhil Agrawal

48.8k25 gold badges134 silver badges212 bronze badges

1 Comment

user1024416 Over a year ago

Thank you for the quick responses. I have the following in my web.config: Connection Timeout=180

Akshay Khandelwal · Accepted Answer · 2012-04-23 20:21:36Z

0

The Maximum Connection Time Out Value can be 2147483647. Try setting the Connection Timeout value into your Connection string in your Web Config as below

<connectionStrings> <add name="ConnectionString" connectionString="Data Source=144681;Initial Catalog=Customer;Persist Security Info=True;User ID=xxxxx;Password=yyyyy" providerName="System.Data.SqlClient" /> </connectionStrings>

answered Apr 23, 2012 at 20:21

Akshay Khandelwal

1,56811 silver badges19 bronze badges

1 Comment

Akshay Khandelwal Over a year ago

And Add

<add name="MyConnectionString" connectionString="Data Source=.\SQLEXPRESS; Database=MyDB; Integrated Security=True;Pooling=True;connection timeout=30" providerName="System.Data.SqlClient" />

into the <connectionStrings></connectionStrings>

anon · Accepted Answer · 2013-04-23 15:19:37Z

-1

There's a timeout for both the SQL and the page response time.

Server.ScriptTimeout = 120; 
e.Command.CommandTimeout = 120;

answered Apr 23, 2013 at 15:19

anon

1

Collectives™ on Stack Overflow

asp.net c# SqlDataSource timout problems

8 Answers 8

Comments

2 Comments

1 Comment

Comments

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

Comments

2 Comments

1 Comment

Comments

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related