Pls suggest...VB to oracle .Max number of processes error from oracle.

[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 closing the connection object after every insert or edit statement. No clue why this is happening.

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

code to connect to oracle:

gsConnection = "Provider=oraOLEDB.oracle;" & _
"Data Source=test;" & _
"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") & "', '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

[csar]

I suggest u would try to loop execution on one connection..

oConn.Open
 
Do While 
  oConn.Execute SQL
Loop
 
oConn.Close

cause of every connection need process (in Oracle)