how to change a SQL connection timeout

[michael sorens]

I have used the visual designer to create a series of DataGridViews
populated from SQLServer data.
A typical call is:

myCountsTableAdapter.Fill(myDataSet.myCounts);

My application actually connects to different systems during a run; on
some of these the default timeout is adequate, but on one system I get an
SQL exception that simply reports "Timeout expired". The default timeout
shows as "15" using this line of code.

Console.WriteLine("timeout = "
+ myCountsTableAdapter.Connection.ConnectionTimeout);

But this property is read-only; how can I change it?

 

[Willy Denoyette [MVP]]

|I have used the visual designer to create a series of DataGridViews
| populated from SQLServer data.
| A typical call is:
|
| myCountsTableAdapter.Fill(myDataSet.myCounts);
|
| My application actually connects to different systems during a run; on
| some of these the default timeout is adequate, but on one system I get an
| SQL exception that simply reports "Timeout expired". The default timeout
| shows as "15" using this line of code.
|
| Console.WriteLine("timeout = "
| + myCountsTableAdapter.Connection.ConnectionTimeout);
|
| But this property is read-only; how can I change it?

Set the timeout in your connection string....

...., Connection Timeout=30,...

Check msdn for other ConnectionString keywords.

Willy.

 

[michael sorens]

I was able to change the Connection Timeout as you suggested--confirmed
with a WriteLine--yet my application still threw a SQLException with a
timeout, even going up to 120 seconds. Running the query in SqlServer
Express takes only 30 to 35 seconds every time. Is there a second timeout
value that might be causing my problem?

 

[John B]

I was able to change the Connection Timeout as you suggested--confirmed
with a WriteLine--yet my application still threw a SQLException with a
timeout, even going up to 120 seconds. Running the query in SqlServer
Express takes only 30 to 35 seconds every time. Is there a second
timeout value that might be causing my problem?

There is a connection timeout and a command timeout.
Connection Timeout: the time it takes before a connection attempt will
timeout.
Command Timeout: the time it takes before command processing will timeout.

JB
<...>

 

[Kevin Yu [MSFT]]

I agree with JB, that you have to set the CommandTimeout of the
DataAdapter.SelectCommand.CommandTimeout property. But since you're using a
TableAdapter generated by the designer, I think you have to modify the
designer generated code.

Kevin Yu
Microsoft Online Community Support

============================================================================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
============================================================================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[michael sorens]

Aha, the evidence pointed me to a separate timeout for connection vs.
command; thanks for the confirmation! I took a look through the generated
code to try to identify where to access this CommandTimeout but it is not
terribly obvious. Any further hints...?

 

[michael sorens]

I just found a chunk of code to do precisely this
(http://www.codeproject.com/csharp/TableAdaptrCommandTimeout.asp). It
almost worked "as is" -- I had to change "_commandCollection" to
"CommandCollection" and then it worked flawlessly.

Thanks again for guiding me in the right direction!

 

[Kevin Yu [MSFT]]

You're welcome!

Kevin Yu
Microsoft Online Community Support

============================================================================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
============================================================================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)