Home » SQL & PL/SQL » SQL & PL/SQL » Callling oracle func with ADO
Callling oracle func with ADO [message #33869] Mon, 08 November 2004 08:55 Go to next message
Lee
Messages: 56
Registered: May 1999
Member
i have a VB6 program querying an oracle db. I have this query that works fine in SQL navigator but when run from my program gives the error "invalid column name". Is there something special about calling a compiled function from ADO?
szQuery = "select sman, duedte, originvdte, discdte, applyto, customer, co, " _
        & "substr(ardetail_ftotalinvoice(customer,applyto,co),1,10) remaining, " _
        & "substr(ardetail_netapplied(customer,applyto,co),1,10) applied " _
        & "From ardetail " _
        & " where customer = '" & custNum & "' " _
        & "   and source in ('1012','1013') " _
        & "   and co = '" & gsCompany & "' " _
        & "   and applyto in " _
        & "    (select applyto from ardetail where customer = '" & custNum & "' " _
        & "        and source in ('1012','1013','1014','1018') " _
        & "        and co = '" & gsCompany & "' " _
        & "        group by applyto, discdte " _
        & "      Having Sum(ftotalinvoice) <> 0 ) " _
        & "order by duedte "

    If custRst.State = adStateOpen Then
        custRst.Close     
    End If
    On Error Goto GetInvoiceError
    custRst.Open szQuery, gConn, adOpenKeyset, adLockOptimistic, adCmdText
    DoEvents
    With custRst
        If .EOF Then
            MsgBox "No invoices found for this customer"
            sbStatusBar.Panels(1).Text = "No invoices found for this customer"
   ...


Can anyone shed any light on why this would work fine in SQL Navigator but return the invalid column error from my program? If i remove the 2 functions from the select, the query runs fine so the error must be related to them. But i dont understand why and i really need to use them!

thanks
Lee
Re: Callling oracle func with ADO [message #199964 is a reply to message #33869] Fri, 27 October 2006 03:49 Go to previous messageGo to next message
SemperOnit
Messages: 2
Registered: October 2006
Location: UK
Junior Member
Have you tried putting packagename. in front of your function names?
Re: Callling oracle func with ADO [message #200258 is a reply to message #33869] Mon, 30 October 2006 02:13 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Are you using the same user_id in both cases?
Previous Topic: Bulk Binding -FORALL
Next Topic: Help needed in a procedure.
Goto Forum:
  


Current Time: Fri Dec 09 19:33:52 CST 2016

Total time taken to generate the page: 0.09999 seconds