Click here to Skip to main content
12,952,186 members (58,047 online)
Rate this:
 
Please Sign up or sign in to vote.
==============
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:
========================================

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 4-Mar-13 20:26pm
virilio817

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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

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

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

    Print Answers RSS
Top Experts
Last 24hrsThis month
OriginalGriff 6,239
CHill60 3,490
Maciej Los 3,083
Jochen Arndt 1,975
ppolymorphe 1,880


Advertise | Privacy | Mobile
Web02 | 2.8.170525.1 | Last Updated 5 Mar 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100