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.

.

Relevant Pages