Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1309
  • Last Modified:

Run-time error '-2147217900(80040e14)'

I am getting following error while running my VB application:

Run-time error '-2147217900(80040e14)':

ORA-06550: line 1, column 60:
PLS-00103: Encountered the symbol "1" when expecting one of the following:
.(),*@%|=-+</> at in is mod remainder not range rem+>..<an exponent(**)> <> or!+ or ~=>
>= <= <> and or like LIKE2 LIKE4 LIKEC between}}


Here's the package and stored proc:

CREATE OR REPLACE PACKAGE ARB_Facsimile_Signature
is
Procedure Invoices_BU( NumYear in Number, NumMon in Number, VarBuUnit in Varchar, p_cursor  out types.cursorType );

Procedure Invoices_ALL ( NumYear in Number, NumMon in Number, p_cursor  out types.cursorType );

END ARB_Facsimile_Signature;
/


CREATE OR REPLACE PACKAGE BODY ARB_Facsimile_Signature
is
Procedure Invoices_ALL( NumYear in Number, NumMon in Number, p_cursor out types.cursorType )is
begin
      open p_cursor for Select IM_INVOICE_NO AS Invoice_No,UM_USER_NAME Approver_Name,BM_NAME as Business_Unit,IM_APPR_DATe as Approved_Date,
ID_COUNTRY_CODE as Country,(select sum(ID_AMOUNT) as Amount
 from bill_invoice_dtl where
 id_invoice_no =im_invoice_no and  ID_INVOICE_VER = IM_INVOICE_VER) as Invoice_Amount,
 CASE ID_Country_code
  WHEN 'IND' THEN 'INR'
  WHEN 'NEP' THEN 'INR'
  ELSE 'USD'
END as Currency from Bill_Invoice_Mst
INNER JOIN BILL_INVOICE_DTL ON IM_INVOICE_NO=ID_INVOICE_NO
INNER JOIN BILL_BU_MST ON IM_BU_CODE=BM_CODE
INNER JOIN BILL_USER_MST ON IM_APPR_BY = UM_LOGIN_ID
where IM_MONTH= NumMon and IM_YEAR= NumYear;
end Invoices_ALL;
Procedure Invoices_BU ( NumYear in Number, NumMon in Number, VarBuUnit in Varchar, p_cursor out types.cursorType ) AS
begin
      open p_cursor for Select IM_INVOICE_NO AS Invoice_No,UM_USER_NAME Approver_Name,BM_NAME as Business_Unit,IM_APPR_DATe as Approved_Date,
ID_COUNTRY_CODE as Country,(select sum(ID_AMOUNT) as Amount
 from bill_invoice_dtl where
 id_invoice_no =im_invoice_no and  ID_INVOICE_VER = IM_INVOICE_VER) as Invoice_Amount,
 CASE ID_Country_code
  WHEN 'IND' THEN 'INR'
  WHEN 'NEP' THEN 'INR'
  ELSE 'USD'
END as Currency from Bill_Invoice_Mst
INNER JOIN BILL_INVOICE_DTL ON IM_INVOICE_NO=ID_INVOICE_NO
INNER JOIN BILL_BU_MST ON IM_BU_CODE=BM_CODE
INNER JOIN BILL_USER_MST ON IM_APPR_BY = UM_LOGIN_ID
where IM_BU_CODE = VarBuUnit and
IM_MONTH= NumMon and IM_YEAR= NumYear;
end Invoices_BU;
END ARB_Facsimile_Signature;
/

Here's the vb code:

Private Sub imgGO_Click()
Dim CmdStr As ADODB.Command
Dim adrsBuDtl As New ADODB.Recordset ' Recordset for storing business unit details.
Dim SqlStr As String
Dim ParYear As New ADODB.Parameter
Dim ParMonth As New ADODB.Parameter
Dim ParBu As New ADODB.Parameter
Provider  
    If Check_values Then
        If Not ConnTest Then
            MsgBox "Database connection error", vbOKOnly, "Go Image Click"
        Else
      OraConn.Open "Provider=ORAOLEDB.Oracle;User ID=arbilling1;password=biju1;Data Source=hpdev;Persist Security Info=False"
            OraConn.CommandTimeout = 10
            OraConn.CursorLocation = adUseClient
            If CboBusinessUnit.ListIndex = 1 Then ' Query for all Business unit details
                'SqlStr = "ARB_Facsimile_Signature.Invoices_ALL"
                SqlStr = "{Call ARB_Facsimile_Signature.Invoices_ALL(?,?,resultset 1)}"
                Set CmdStr = New ADODB.Command
                With CmdStr
                        .ActiveConnection = OraConn
                        .CommandText = SqlStr
                        .CommandType = adCmdText
                End With
                Set ParYear = CmdStr.CreateParameter("In_Year", adInteger, adParamInput, 1, TxtYear.Text)
                CmdStr.Parameters.Append ParYear
                ParYear = TxtYear.Text
                Set ParMonth = CmdStr.CreateParameter("In_Month", adInteger, adParamInput, 1, CboMonth.ListIndex)
                CmdStr.Parameters.Append ParMonth
                ParMonth = CboMonth.ListIndex
            ElseIf CboBusinessUnit.ListIndex > 1 Then
                SqlStr = "ARB_Facsimile_Signature.Invoices_BU"
                'SqlStr = "{Call ARB_Facsimile_Signature.Invoices_BU(?,?,?,{resultset 1000, fname})}"
                Set CmdStr = New ADODB.Command
                With CmdStr
                    Set .ActiveConnection = OraConn
                        .CommandText = SqlStr
                        .CommandType = adCmdStoredProc
                End With
                Set ParYear = CmdStr.CreateParameter("In_Year", adInteger, adParamInput, 1)
                CmdStr.Parameters.Append ParYear
                Set ParMonth = CmdStr.CreateParameter("In_Month", adInteger, adParamInput, 1)
                CmdStr.Parameters.Append ParMonth
                Set ParBu = CmdStr.CreateParameter("In_Bu", adVarChar, adParamInput, 3)
                CmdStr.Parameters.Append ParBu
                ParYear.value = CInt(TxtYear.Text)
                ParMonth.value = CInt(CboMonth.ListIndex)
                ParBu.value = maBUCode(CboBusinessUnit.ListIndex)
            End If
        End If
           
        Set adrsBuDtl = New ADODB.Recordset
        'If Not adrsBuDtl.State Then
            Set adrsBuDtl = CmdStr.Execute
            MsgBox adrsBuDtl.RecordCount
        'End If
    End If
End Sub
0
Biju1149
Asked:
Biju1149
  • 2
  • 2
  • 2
1 Solution
 
sujith80Commented:
Are you sure this call is correct.
>> SqlStr = "{Call ARB_Facsimile_Signature.Invoices_ALL(?,?,resultset 1)}"

"resultset 1" - looks not right
0
 
Biju1149Author Commented:
Sujith,

I am not sure. Can you help me to resolve the issue? Thanks in advance.

0
 
sujith80Commented:
I am not familiar with VB.

To me it looks like that call is not proper. Probably you have to declare a resultset variable and use it in the call. Or bind the parameter to the variable.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Robberbaron (robr)Commented:
"{Call ARB_Facsimile_Signature.Invoices_ALL(?,?,resultset 1)}"  is a call to the oracle stored procedure.

Thats where the problem is.  I agree that   resultset 1 doesnt look look like a parameter to send to the stored procedure.
0
 
Biju1149Author Commented:
SQL>variable c refcursor;
SQL>exec ARB_Facsimile_Signature. Invoices_BU (2006,01,HRC,:C);
SQL>print c;

In oracle above command is used to execute and retrive data.
I need to execute this procedure using VB code.

Please help me to resolve the issue. thanks in advance
0
 
Robberbaron (robr)Commented:
http://support.microsoft.com/kb/176086    '<<<very much on target

http://support.microsoft.com/kb/174679/EN-US/

 
I know little of stored proceedures but following M$ example....

SqlStr = "{Call ARB_Facsimile_Signature.Invoices_ALL(?,?,{resultset 1, p_cursor})}"
'returns max 1 record !

SqlStr = "{Call ARB_Facsimile_Signature.Invoices_BU(?,?,?,{resultset 1000, p_cursor})}"
'returns max 1000 records

ive changed the out variable name to match what is in the SP.

ms example attached for others to search by.




  ''QSQL = "{call packperson.oneperson(?,{resultset 2, ssn, fname, lname})}"
  'return 2 records max
 
  Set CPw2 = New ADODB.Command
  With CPw2
      Set .ActiveConnection = Cn
      .CommandText = QSQL
      .CommandType = adCmdText
      .Parameters.Append .CreateParameter(, adInteger, adParamInput)
  End With
 
  Set Rs = New ADODB.Recordset
  With Rs
      .CursorType = adOpenStatic
      .LockType = adLockReadOnly
  End With
 
  CPw2(0) = inputssn  '<<< user input data
 
  Rs.Open
 
  MsgBox "Person data: " & Rs(0) & ", " & Rs(1) & ", " & Rs(2)
 
  Rs.Close

Open in new window

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now