"Max number of processes" exceed from VB

[nathb]

Hi,
I am writing a program in VB 6.0. It connects to oracle sucessefully . It is a simple insert statement into a single table which has 70 columns. First time it inserts a records and subsequently it updates the record. I am using ADO. Somehow I get the "Max number of processes" oracle error after inserting about 80 records into the table.

I am pasting the code I am using. could some one pls suggest me I am doing something wrong?

code to connect to oracle:
gsConnection = "Provider=oraOLEDB.oracle;" & _
"Data Source=test.world;" & _
"User Id=myoracledb;" & _
"Password=okay"
Set rstrecordset = lclsDataRet.GetResult(gsConnection, ssql, 0)
'lclsDataRet is a class.
ssqledit = "UPDATE tbl_SwapVolatilities SET " & sFieldName & " = " & sDatapoint & " WHERE tbl_SwapVolatilities.HISTORICALDATE = to_date('" & Format(CDate(sDate), "DD-MMM-YYYY&quot & "', 'DD-Mon-YYYY')"
Set lclsCustomers = New clsDataRet
lclsCustomers.ExecSQL gsConnection, ssqledit

Public Function GetResult(ByVal gsConnection As String, ByVal ssql As String, ByRef lrows As Long) As ADODB.Recordset

Dim objConn As ADODB.Connection
Dim rsGetResult As ADODB.Recordset
Dim lErrNo As Long
Dim sErrDesc As String
Dim sErrorMsg As String


On Error GoTo GetResult_Err

Set objConn = New ADODB.Connection

objConn.Open gsConnection

Set rsGetResult = New ADODB.Recordset
With rsGetResult
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.ActiveConnection = objConn
.Open ssql


lrows = .RecordCount
End With


'return the recordset
Set GetResult = rsGetResult
Set objConn = Nothing
Exit Function

Public Function ExecSQL(gsConnection, ssql)
Dim ssqledit As String
Dim objConn As ADODB.Connection
Dim objCommand As ADODB.Command
Dim blnRtn As Boolean

On Error GoTo err_clsDataRet_ExecSQL

Set oEH = New CDBErrorProcessor
oEH.SetClass "clsDataRet", "ExecSQL method", "ExecSQL method"


Set objConn = New ADODB.Connection
objConn.Open gsConnection

Set objCommand = New ADODB.Command
objCommand.ActiveConnection = objConn
objCommand.CommandText = ssql
objCommand.CommandType = adCmdText
objCommand.Execute

Set objCommand = Nothing
Set objConn = Nothing

Exit Function

err_clsDataRet_ExecSQL:
blnRtn = oEH.ProcessError(Err)

End Function

 

[jebenson]

In the ExecSQL function, try putting objConn.Close before you do the Set objConn = Nothing

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[chiph]

Like jebenson says - if you don't explicitly close a connection object, it will stay open until the server times it out, or your program ends.

I use this block of code to do that:

    If Not adoConn Is Nothing Then
        If adoConn.State = adStateOpen Then
            adoConn.Close
        End If
       Set adoConn = Nothing
    End If

Chip H.

 

[nathb]

thank you so much guys.