Errors accessing SQL Server (2000)

[olisemalis]

Hello,

I´m getting some random errors while accessing SQL server in my application (VB.net):

1)
ExecuteScalar requires an open and available Connection. The connection's current state is closed.

2)
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

I have the default pool which I think is 100.

When running this query against the database, I´m getting between 480-500 rows (which I believe are connections in the pool). If the pool is the default, I don´t know how can I get 500?

SELECT spid, uid=rtrim(loginame), Program_name=rtrim(Program_name),
dbname=db_name(dbid), status=rtrim(status) FROM master.dbo.sysprocesses
WHERE loginame = 'my_app'

I already sow if I was closing all the connections, and I think I am.

Any ideas on this?

Thank you

 

[kss444]

All the errors you are getting is because of your first error. Your not opening your connection, you need to open your connection before using executescalar.

Ordinary Programmer

 

[kss444]

SqlConnection connectionString = new SqlConnection(ConfigurationManager.ConnectionStrings["MembershipConnectionString"].ConnectionString);
SqlCommand comm = new SqlCommand("select * from table", connectionString);
comm.Connection.Open();

string x; //your returned values
x = comm.ExecuteScalar(CommandBehavior.CloseConnection);

Ordinary Programmer

 

[olisemalis]

Hello,

It can´t be so simple... if so I would always get an error in the same functions. And I´m actually opening and closing my connections. Please, look my code.

thank you.

//////////////////////////////////////////////
Client side:

try
sConnStr = "something"
objDB= CLS_ADONET.GetADONETWrapper(CLS_ADONET.PROVIDER_TYPE.PROVIDER_SQLCLIENT)
objDB.ConnectionString = sConnStr

GetUserID = objDB.ExecuteScalar("GetUser", CommandType.StoredProcedure)
catch
finally
objDB.Disconnect()
objDB.Dispose()
objDB = Nothing

end try
/////////////////////////////


/////////////////////////////////////
Class (the called functions):

Public Shared Function GetADONETWrapper(ByVal sProviderName As String) As CLS_ADONET

Dim oDB As CLS_ADONET

oDB = Nothing

Select Case Trim(UCase(sProviderName))
Case PROVIDER_TYPE.PROVIDER_OLEDB '"OLEDB"
oDB = GetOleDbWrapper()

Case PROVIDER_TYPE.PROVIDER_SQLCLIENT '"SQLCLIENT"
oDB = GetSqlClientWrapper()

Case PROVIDER_TYPE.PROVIDER_ODBC '"ODBC"
oDB = GetOdbcWrapper()

Case PROVIDER_TYPE.PROVIDER_ORACLE '"ORACLECLIENT"
oDB = GetOracleWrapper()

Case Else
oDB = GetADONETWrapper()

End Select

Return oDB
End Function



Public Shared Function GetSqlClientWrapper() As CLS_ADONET
Dim oDB As CLS_ADONET

oDB = New CLS_ADONET
oDB.m_eProvider = PROVIDER_TYPE.PROVIDER_SQLCLIENT

Return oDB
End Function


Public Function ExecuteScalar(ByVal sSQL As String, ByVal oType As CommandType) As Object

ValidateConnection()
m_oCommand.CommandText = sSQL
m_oCommand.CommandType = oType

Return m_oCommand.ExecuteScalar()

End Function

Public Function ValidateConnection() As Boolean

If m_bConnected Then
ValidateConnection = True
Else
ValidateConnection = Connect()
End If

End Function


Public Function Connect() As Boolean
Dim i As Integer

' Check for valid connection string
If IsNothing(m_sConnectionString) Or m_sConnectionString.Length = 0 Then
Throw New Exception("Invalid database connection string")
End If

' Disconnect if already connected
Disconnect()

' Get ADONET connection object
m_oConnection = GetConnection()
m_oConnection.ConnectionString = Me.ConnectionString

If m_nRetryConnect = 0 Then
m_nRetryConnect = 1
End If

' Implement connection retries
For i = 0 To m_nRetryConnect - 1

Try
m_oConnection.Open()

If m_oConnection.State = ConnectionState.Open Then
m_bConnected = True
Exit For
End If

Catch

If i = m_nRetryConnect Then
Throw
End If

Thread.Sleep(1000)

End Try
Next

' Get command object
m_oCommand = m_oConnection.CreateCommand()
m_oCommand.CommandTimeout = m_nCommandTimeout

Return m_bConnected

End Function



Public Function Disconnect()

' Disconnect can be called from Dispose and should guarantee no errors
If m_bConnected Then
Exit Function 'Return
End If

If Not IsNothing(m_oTransaction) Then
RollbackTransaction(False)
End If

If Not IsNothing(m_oCommand) Then

m_oCommand.Dispose()
m_oCommand = Nothing
End If

If Not IsNothing(m_oConnection) Then
Try
m_oConnection.Close()
Catch
End Try
m_oConnection.Dispose()
m_oConnection = Nothing
End If

m_bConnected = False

End Function
/////////////////////////////////////////////////////