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]]

"michael sorens" <m_********@community.nospam> wrote in message
news:op***************@spo-cont-2-dt.itron.com...
|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?

On Mon, 08 May 2006 11:30:08 -0700, Willy Denoyette [MVP]
<wi*************@telenet.be> wrote:

"michael sorens" <m_********@community.nospam> wrote in message
news:op***************@spo-cont-2-dt.itron.com...
|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.

[John B]

michael sorens wrote:

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...?

On Mon, 08 May 2006 19:02:42 -0700, Kevin Yu [MSFT]
<v-****@online.microsoft.com> wrote:

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]

I just found a chunk of code to do precisely this
(http://www.codeproject.com/csharp/Ta...ndTimeout.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!