How to set ConnectionTimeout to 0 in Sql Server 2008 ?

Question

I set timeout to 0 but the connection close prematuraly, what is wrong with this statement ?

        Using odbcconn As New OdbcConnection(DataShared.gstrCNN)

        odbcconn.ConnectionTimeout = 0
        odbcconn.Open()
        Dim OdbcCmd As New OdbcCommand( _
            "{ ? = CALL [proc_Cp_GenEstadoCta](" & _
            PCOD_EMPR & ", " & _
            PPER_ANUAL & "," & _
            DataShared.gintCODUSER & " ) }", odbcconn)

        OdbcCmd.Parameters.Add("@return", OdbcType.Int)
        OdbcCmd.Parameters("@return").Direction = ParameterDirection.ReturnValue

        OdbcCmd.ExecuteNonQuery()
        If CInt(OdbcCmd.Parameters("@return").Value) = 0 Then
            GenEstadoMovsSaldos = True
        Else
            GenEstadoMovsSaldos = False
        End If

    End Using

The corrected code

        Using odbcconn As New OdbcConnection(DataShared.gstrCNN)

        --odbcconn.ConnectionTimeout = 0

        odbcconn.Open()
        Dim OdbcCmd As New OdbcCommand( _
            "{ ? = CALL [proc_Cp_GenEstadoCta](" & _
            PCOD_EMPR & ", " & _
            PPER_ANUAL & "," & _
            DataShared.gintCODUSER & " ) }", odbcconn)

        OdbcCmd.CommandTimeout = 60

        OdbcCmd.Parameters.Add("@return", OdbcType.Int)
        OdbcCmd.Parameters("@return").Direction = ParameterDirection.ReturnValue

        OdbcCmd.ExecuteNonQuery()
        If CInt(OdbcCmd.Parameters("@return").Value) = 0 Then
            GenEstadoMovsSaldos = True
        Else
            GenEstadoMovsSaldos = False
        End If

    End Using

It's working well !

Answer 1

What are you trying to accomplish by setting the connection timeout to zero?

The connection timeout is the time to wait while attempting to open the database connection. It has nothing to do with when the connection closes.

Perhaps you are looking for the CommandTimeout property in the OdbcCommand class? Setting the CommandTimeout to zero will remove the time limit when waiting for the query to run.

However, if the database goes offline your program will wait indefinitely for something that will not happen, so you should consider setting a long time instead, so that the command will timeout eventually instead of never.

(By the way, why are you using the ODBC database driver instead of the faster and more specialised SqlClient driver?)