MS SQL Server ODBC - Output parameter doesn't return the correct value

Author

Output parameter doesn't return the correct value

Aynur Demircan

2006-12-01, 5:18 am

Hi,
We are experiencing weird problems when working with the ODBC driver to
access SQL Server 2005.
One of them is about calling a stored procedure with output parameters. (I
learned from what I read that for Sql Server an output parameter is an
Input/Output Parameter by default. But this featuıre isn't provided in ODBC
access? )
I use DbCommand or OdbcCommand with CommandType StoredProcedure. İf I don't
supply a value for the output parameter before calling ExecuteNonReader, an
error like " The stored procedure expects parameter X but it isn't supplied"
is given.
If I supply a value and then run the stored procedure, I get the value
that I gave as input, NOT THE ONE calculated in the SP.
I run the same code using SqlClient Data Provider and I get back correct
values for the output parameters. Does any one have any idea about the
problem?

Thanks..

Arnie Rowland

2006-12-01, 7:13 pm

I think that you have to declare the parameter type as OUTPUT -not the
'default' (Input/Output). Something like:

myDataAdapter.SelectCommand.Parameters.Add("@SerialNum", OdbcType.Int,
Direction.Output).Value = MyIntVariable

(This is from memory, not tested nor guarenteed to be exact.)

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf

"Aynur Demircan" < AynurDemircan@discus
sions.microsoft.com> wrote in message
news:3435338D-EB6A-40D5-82CC- B3AF4413676C@microso
ft.com...
> Hi,
> We are experiencing weird problems when working with the ODBC driver to
> access SQL Server 2005.
> One of them is about calling a stored procedure with output parameters. (I
> learned from what I read that for Sql Server an output parameter is an
> Input/Output Parameter by default. But this featuire isn't provided in
> ODBC
> access? )
> I use DbCommand or OdbcCommand with CommandType StoredProcedure. If I
> don't
> supply a value for the output parameter before calling ExecuteNonReader,
> an
> error like " The stored procedure expects parameter X but it isn't
> supplied"
> is given.
> If I supply a value and then run the stored procedure, I get the value
> that I gave as input, NOT THE ONE calculated in the SP.
> I run the same code using SqlClient Data Provider and I get back correct
> values for the output parameters. Does any one have any idea about the
> problem?
>
> Thanks..

Sponsored Links