Why is my SQL Connection Timing out?

Question

In my standalone Windows application I have a connection string:

Private Const _sqlDB As String = "Data Source=(localdb)\v11.0;Initial Catalog=localACETest;Integrated Security=True;AttachDbFileName=C:\ACE DB\localACETest.mdf"

To which I am adding a connect timeout (nevermind that this is an absurdly long timeout):

";Connect Timeout=1000"

Can anyone explain why I have procedures now timing out that were NOT prior to adding the connect timeout?

Here's how I create the connections:

        _conn = New SqlConnection(_sqlDB)
        _conn.Open()

        Dim _dr As SqlDataReader
        Dim _cmd As New SqlCommand("Random Stored Procedure", _conn)
        _cmd.Parameters.Add("@BusGrp", System.Data.SqlDbType.NVarChar).Value = bg
        _cmd.CommandType = System.Data.CommandType.StoredProcedure

This doesn't seem to happen when I remove the connect timeout and add a command timeout, however:

        _cmd.CommandTimeout = 1000

While I understand that a command & connection timeout are two different things, it doesn't make sense to me that adding a connection timeout to extend the default would cause this problem.

Answer 1

You are using the incorrect property for a Connection Timeout:

Connect Timeout=1000

should be

Connection Timeout=1000

The correct property is outlined in the MSDN documentation for Connection Timeout. Here's the correct way of using a SQL database (taken from the same MSDN documentation):

As discussed via the comments, you're actually connecting to an SQL server, there's a better way here:

Sub connectToDb()
    myConn = New SqlConnection("Server=(local);Database=[.database name here.];User Id=[.username here.];Password=[.password here.];")
    Dim returnValue As Object
    cmd.CommandType = CommandType.Text
    cmd.Connection = myConn

    Try
        myConn.Open()
        MsgBox("Connected to SQL")
    Catch excep As Exception
        MsgBox("An error occurred: " + excep.Message)
    End Try
End Sub

To query the database for something, you can use:

    cmd.CommandText = "USE [.database name.]"
    cmd.ExecuteScalar()
    cmd.CommandText = "[.SQL query.]"
    returnValue = cmd.ExecuteScalar() 'You can check the returnValue to see if the query returned anything.

If you have an error that multiple queries cannot be executed at the same time, you must enable MARS by appending MultipleActiveResultSets=True; to your connection string.