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;
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.
"Ed Brown" <ebrown@computer-systems.com> wrote in message
news:8ubpul$5vs$1@news.chatlink.com...
> 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;
>
>