nathb
Jul 28th, 2003, 01:55 PM
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
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