Re: Error while calling an Oracle Stored Procedure from VB using ADO

macca wrote:
> Hi all, hope you can help me out. I'm getting the following error when
> trying to call an oracle stored proc from vb using ado
>
> ORA-01060 array binds or executes not allowed
>
> Does nayone know what needs to be done to correct this? the definition
> of the sp and vb code is shown below
>
> PROCEDURE allocate_account_number_ref (i_number_to_allocate IN NUMBER
> ,i_sort_code IN VARCHAR2
> ,i_account_type IN VARCHAR2
> ,i_issued_to IN VARCHAR2
> ,i_issued_by IN VARCHAR2
> ,o_account_numbers OUT reftype);
>
> Private Sub UserForm_Initialize()
>
> Dim Conn As String
>
> Set cn = New ADODB.Connection
>
> userId = "xxx"
>
> password = "xxx"
>
> server = "xxx"
>
> On Error GoTo 0
>
> Set cn = New ADODB.Connection
>
> cn.ConnectionString = "Provider=MSDAORA.1;" & _
> "Data Source=" & server & ";" & _
> "User ID=" & userId & ";" & _
> "Password=" & password & ";" & _
> "PLSQLRSet=1;"
>
> cn.CursorLocation = adUseClient
> cn.Open
>
> qSql = "{call cap_pkg.allocate_account_number({resultset 1,
> o_account_numbers},?,?,?,?,?)}"
>
>
> Set cpw1 = New ADODB.Command
>
>
> With cpw1
> .ActiveConnection = cn
> .CommandType = adCmdText
> .CommandText = qSql
>
>
> .Parameters.Append .CreateParameter("i_number_to_allocate",
> adNumeric, adParamInput, , 1)
>
> .Parameters.Append .CreateParameter("i_sort_code", adVarChar,
> adParamInput, 8, "309713")
>
> .Parameters.Append .CreateParameter("i_account_type", adVarChar,
> adParamInput, 3, "001")
>
> .Parameters.Append .CreateParameter("i_issued_to", adVarChar,
> adParamInput, 35, "visualbasic")
>
> .Parameters.Append .CreateParameter("i_issued_by", adVarChar,
> adParamInput, 35, "visualbasic")
>
> End With
>
> Set rs = New ADODB.Recordset
> Set rs = cpw1.Execute
> MsgBox "Complete"
> rs.Close
> End Sub

Try placing:

    cpw1.Prepared = True

Before the .Parameters.Append lines. This may not be the only error in your code, but it should help.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Jul 20 2006 - 06:25:25 CDT