How to add timeout to connection string

kgittinger
kgittinger used Ask the Experts™
on
Where should the timeout seconds be entered in the connection string for exequeting a query on the below connection?  I need 120 seconds...

    Dim cn As OleDbConnection
    Const strCN As String = "Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;User ID='';Initial Catalog=MyTable;Data Source=BS-120471\SQLEXPRESS;Initial File Name='';Server SPN=''"
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I would first suggest using OleDbConnectionStringBuilder to build the string (http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbconnectionstringbuilder.aspx)

OleDB does not have a built in time-out in the string as far as I know. Since you are using SQLEXPRESS you might consider using a SqlConnection object instead. it has the property for the timeout as well as the ability to add the following to the connection string:  "Connection Timeout=30"

Author

Commented:
Is there any chance you could provide an example of this connection builder at work?  I am unsure about adding the "connections timeout"

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Sample.mdb;User ID="Admin;NewValue=Bad"

THanks!
You can simply change your connection string to:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Sample.mdb;User ID=Admin;NewValue=Bad;Connect Timeout=600
The default is 15 seconds (the example sets it to 10 mins). You can set it to zero to be unlimited but of course this is not not recommended.

What I meant by using the builder is for a SQL Server connection string, you can load your string in and then change it via easy to see properties. EG:
 
Dim builder As New SqlConnectionStringBuilder("Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;User ID='';Initial Catalog=MyTable;Data Source=BS-120471\SQLEXPRESS;Initial File Name='';Server SPN=''")
builder.ConnectTimeout = 600
' Set more properties here.

Using conn As New SqlConnection(builder.ToString())
	conn.Open()
...
End Using

Open in new window

.ToString() it when you're done to get the connection string it represents. Unfortunately the OleDbConnectionStringBuilder does not have the properties as nicely defined.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial