VB calling SP with Money parameter fails 
Author Message
 VB calling SP with Money parameter fails

Hi all,

I have a VB6 program using ADO to access the database using the MS OLEDB
provider for ODBC with Informix ODBC driver in the 2.50 SDK. The program
used ADO Command objects to call stored procedures in the database to
perform inserts, updates, etc. When trying to pass a parameter to the stored
procedure using a MONEY datatype, I get the error "Multi-step OLE-DB
operation generated errors. Check each OLE-DB status value, if available. No
work was done." The error does not occur if the value being passed is zero,
but does occur with any non-zero value. Thanks in advance for any
suggestions. Here's an example of the problem:

The ADO Command object setup:
---
Private qryITesttable As ADODB.Command
Set qryITesttable = New ADODB.Command
qryITesttable.CommandType = adCmdText
qryITesttable.CommandText = "{ call I_testtable(?,?) }"

Set prm = qryITesttable.CreateParameter("Vt_char", adChar, adParamInput, 10)
qryITesttable.Parameters.Append prm
Set prm = qryITesttable.CreateParameter("Vt_money", adNumeric, adParamInput)
qryITesttable.Parameters.Append prm

The stored procedure call:
' Set the parameters for the INSERT command object.
qryITesttable.Parameters("Vt_char").Value = "TEST1"
qryITesttable.Parameters("Vt_money").Value = 1             ' 0 works here.
Any non-0 value fails.

' Set the ActiveConnection of the Command object to the Connection
parameter.
qryITesttable.ActiveConnection = gadoConnMain

' Execute the Command object into a RecordSet, and retrieve the return code.
Set RSExecuteSP = qryITesttable.Execute

The table definition:
---
create table testtable
  ( t_char char(10) not null ,
    t_money money(10,2));

create unique index t_char_idx on testtable (t_char);

The Stored Procedure:
---
create procedure I_testtable (Vt_char   char(10),
                              Vt_money  money(10,2)) returning smallint;

define return_code smallint;
define sql_err int;
define isam_err int;
define sql_text char(20);

on exception set sql_err, isam_err, sql_text
let return_code=sql_err;
return return_code;
end exception;

insert into testtable (t_char,t_money) values (Vt_char,Vt_money);
let return_code=0;
return return_code;

end procedure;



Wed, 18 Jun 1902 08:00:00 GMT
 VB calling SP with Money parameter fails

Ok, I figured this out. The ADO datatype needs to be adCurrency vs adNumeric
for the Informix ODBC driver. Per the documentation for the Intersolv
driver, THAT uses adNumeric, but we are using the Informix driver.


Quote:
> Hi all,

> I have a VB6 program using ADO to access the database using the MS OLEDB
> provider for ODBC with Informix ODBC driver in the 2.50 SDK. The program
> used ADO Command objects to call stored procedures in the database to
> perform inserts, updates, etc. When trying to pass a parameter to the
stored
> procedure using a MONEY datatype, I get the error "Multi-step OLE-DB
> operation generated errors. Check each OLE-DB status value, if available.
No
> work was done." The error does not occur if the value being passed is
zero,
> but does occur with any non-zero value. Thanks in advance for any
> suggestions. Here's an example of the problem:

> The ADO Command object setup:
> ---
> Private qryITesttable As ADODB.Command
> Set qryITesttable = New ADODB.Command
> qryITesttable.CommandType = adCmdText
> qryITesttable.CommandText = "{ call I_testtable(?,?) }"

> Set prm = qryITesttable.CreateParameter("Vt_char", adChar, adParamInput,
10)
> qryITesttable.Parameters.Append prm
> Set prm = qryITesttable.CreateParameter("Vt_money", adNumeric,
adParamInput)
> qryITesttable.Parameters.Append prm

> The stored procedure call:
> ' Set the parameters for the INSERT command object.
> qryITesttable.Parameters("Vt_char").Value = "TEST1"
> qryITesttable.Parameters("Vt_money").Value = 1             ' 0 works here.
> Any non-0 value fails.

> ' Set the ActiveConnection of the Command object to the Connection
> parameter.
> qryITesttable.ActiveConnection = gadoConnMain

> ' Execute the Command object into a RecordSet, and retrieve the return
code.
> Set RSExecuteSP = qryITesttable.Execute

> The table definition:
> ---
> create table testtable
>   ( t_char char(10) not null ,
>     t_money money(10,2));

> create unique index t_char_idx on testtable (t_char);

> The Stored Procedure:
> ---
> create procedure I_testtable (Vt_char   char(10),
>                               Vt_money  money(10,2)) returning smallint;

> define return_code smallint;
> define sql_err int;
> define isam_err int;
> define sql_text char(20);

> on exception set sql_err, isam_err, sql_text
> let return_code=sql_err;
> return return_code;
> end exception;

> insert into testtable (t_char,t_money) values (Vt_char,Vt_money);
> let return_code=0;
> return return_code;

> end procedure;



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Problem with results and SP calling SP in VB

2. Calling sp from VB and VB.net

3. sql70 and vb 60 and stored procedures with money output parameters

4. Profiler repeats 1st SP call only with ADO parameter objects

5. Syntax problem with ADO Parameter call to SP

6. calling an SP w/ output parameter

7. I can't call Oracle SP from VB5 with over 10 parameters

8. Profiler repeats 1st SP call only with ADO parameter objects

9. Call Oracle SP That Has IN OUT Parameter

10. output parameter in SP call

11. ADO storedprocedure call failing (parameter hell?)

12. Sqlxml call fails when text parameter passed between 4k and 8k


 
Powered by phpBB® Forum Software