Click here to Skip to main content
14,969,782 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
==============
My main code:
==============

Private Sub Chk_RaCosignStatus()
Dim sSQL As String
Dim RaNoArray() As String
Dim res As String
Dim i As Integer
On Error GoTo ErrorHandle

'Get data which status is "ACTIVE form table of "ra_cosignstatus" and keep "racosignno"
sSQL = "SELECT * FROM RA_COSIGNSTATUS WHERE STATUS = 'ACTIVE' AND UPDATETIME > SYSDATE - 30 order by updatetime"

Call ODBC_QuerySQL(sSQL, myDBconn, myDBrs)

While Not myDBrs.EOF And Not IsNull(myDBrs)
If myDBrs.Fields("status") = "ACTIVE" Then
ReDim Preserve RaNoArray(i)
RaNoArray(i) = Trim(IIf(IsNull(myDBrs.Fields("RANO")) = True, "", myDBrs.Fields("RANO")))
i = i + 1
End If
NextRule:
myDBrs.MoveNext
Wend

If myDBrs.State = adStateOpen Then myDBrs.Close

If i > 0 Then
For i = 0 To UBound(RaNoArray)

'Mapping all RANO
res = ODBC_Execute_RaCosignActive(RaNoArray(i), GlobalData.RaCosignActive_Name, myDBconn, myDBcomd)
If res = 0 Then
'Record mapping resule, return 0 resule is true
logDatatoFile "Main", 0, "Excute RACosignActive Procedure Success."
Else
'Record mapping resule, return 1 resule is false
logDatatoFile "Main", 0, "Excute RACosignActive Procedure Error."
End If

Next
End If

Exit Sub
ErrorHandle:
logDatatoFile "Main", 0, "Chk_RaCosignStatus ErrMsg: " & Err.Description & ""
End Sub


========================================
Function of call oracle storeprocedure:
========================================

VB
Public Function ODBC_Execute_RaCosignActive(ByVal inputval As String, ByVal SPName As String, ByRef iRS_Conn As ADODB.Connection, Optional ByRef iRS_Command As ADODB.Command) As Integer
On Error GoTo ErrorHandle
    Dim res As String
    Dim adoP As ADODB.Parameter
    Set iRS_Command.ActiveConnection = iRS_Conn
    iRS_Command.CommandType = adCmdStoredProc
    iRS_Command.CommandText = SPName

    Set adoP = iRS_Command.CreateParameter("@s_RANO", adVarChar, adParamInput, 10, inputval)
    iRS_Command.Parameters.Append adoP

    Set adoP = iRS_Command.CreateParameter("@o_RTNSTR", adVarChar, adParamOutput, 10)
    iRS_Command.Parameters.Append adoP

    iRS_Command.Execute

    Set iRS_Command.ActiveConnection = Nothing

    ODBC_Execute_RaCosignActive = 0

    Exit Function
ErrorHandle:
    ODBC_Execute_RaCosignActive = 1
    logDatatoFile "modSpcDB", 0, "ODBC_Execute_RaCosignActive ErrMsg: " & Err.Description & ""
End Function


============
My problem is:
============

My main code first call call ODBC_Execute_RaCosignActive in loop(For i = 0 To UBound(RaNoArray)) will work.

But second call ODBC_Execute_RaCosignActive,

it will occor error when it run iRS_Command.Execute

error description is ORA-01036: illegal variable name/number

Someone can help me finding what;s problem is it ???!!!
Posted

1 solution

adodb.command.parameters need to clear up for call storeprocedure again

so reset iRS_Commandaf after iRS_Command.Execute will fix the problem.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900