Executing Oracle SP in ADO...

[GDX]

Hello Guys I have a problem im am trying to execute the following oracle SP but im getting the error:

"Snytax Error Or Access Violation"

When i run the same line in Worksheet it executes it sucesfully..any ideas?

Dim oCommand As New ADODB.Command
oCommand.ActiveConnection = "Driver={Microsoft ODBC for Oracle};Server=myserver;Uid=u;Pwd=pw;"
Linerun = "execute INVENTORY_POST('Xiacon','R','CTL006','003','02-44-845','HL-25-06Z1.35%','+','240','EA','VISHAY','','','','01092002','1','240','BOX');"
oCommand.CommandText = Linerun
oCommand.CommandType = adCmdStoredProc
oCommand.Execute , , adOptionUnspecified

Thanks a lot! Gordon R. Durgha
gd@vslink.net

http://www.vslink.net

http://www.brainbench.com/transcript.jsp?pid=1217371

 

[SLAbel]

If you are going to run it like that, then the command type would be SQLtext and I think you need to surround the execute with a Begin and End;. I have used the following code to execute a stored procedure:

    Dim cn                      As ADODB.Connection
    Dim cm                      As ADODB.Command
    Dim prm                     As ADODB.Parameter
    Dim objCon                  As ADOWrapper.CConnection

'--------------------

    Set cm = CreateObject("ADODB.Command")
    
    Set objCon = New ADOWrapper.CConnection
    Set cn = objCon.OpenConnection(dbtNormalDatabase, _
                                   cntTypeRegularConnect)
    Set objCon = Nothing
    
    '
    '       Now let's call the procedure 
    '   that will do the delete.
    '
    With cm
    
        Set .ActiveConnection = cn
        .CommandType = adCmdStoredProc
        .CommandText = "PackageName.usp_DeleteSomethingByID"
        
        '
        '   Parameters to the procedure
        '
        Set prm = .CreateParameter("SomethingID", _
                                   adInteger, _
                                   adParamInput, _
                                   , _
                                   vntFolderNamePK)
        .Parameters.Append prm
        
        '
        '   Add any more parameters that you need, then
        ' Execute it
        '

        .Execute
        
    End With
    
    cn.Close
    
    Set prm = Nothing
    Set cm = Nothing
    Set cn = Nothing

[\code]

Hope that helps
scott