ReturnValue when using ExecuteNonQuery in ADO

[nate axtell]

I'm using ADO in .NET to perform a query that will return a value telling me
the succuss or failure of the procedure. I've been trying to return a
string (some sentence) when different errors occur in the procedure. I am
seeing the following error in VB when a string of characters is returned
from the procedure:
"Syntax error converting the varchar value 'returned error message string'
to a column of data type int."

My code is as follows:
oCMD = New OleDbCommand("{ ? = CALL cmnStartBackup} ", oCnxn)
oCMD.Parameters .Add("@RetVal", OleDbType.VarCh ar, 100).Direction =
ParameterDirect ion.ReturnValue
oCMD.ExecuteNon Query()

So it appears that even though you can set the data type of the return value
it can really only be an integer. Is this an ADO limitation or can I do
something else to allow a string to be returned?

[Guest]

Re: ReturnValue when using ExecuteNonQuery in ADO

"nate axtell" <naxtell at progeny dot net> wrote in message
news:O1wGBQ5rEH A.324@TK2MSFTNG P11.phx.gbl...[color=blue]
> I'm using ADO in .NET to perform a query that will return a value telling[/color]
me[color=blue]
> the succuss or failure of the procedure. I've been trying to return a
> string (some sentence) when different errors occur in the procedure. I am
> seeing the following error in VB when a string of characters is returned
> from the procedure:
> "Syntax error converting the varchar value 'returned error message string'
> to a column of data type int."
>
> My code is as follows:
> oCMD = New OleDbCommand("{ ? = CALL cmnStartBackup} ", oCnxn)
> oCMD.Parameters .Add("@RetVal", OleDbType.VarCh ar, 100).Direction =
> ParameterDirect ion.ReturnValue
> oCMD.ExecuteNon Query()
>
> So it appears that even though you can set the data type of the return[/color]
value[color=blue]
> it can really only be an integer. Is this an ADO limitation or can I do
> something else to allow a string to be returned?
>
>[/color]

To return anything othe than an integer I would change the direction from
returnvalue to output & allow a paameter in the stored procedure with
'@RetVal varchar (20) output' or similar, then set @retval instead of
trying to return it.

--
Jonathan Bailey.

[nate axtell]

Re: ReturnValue when using ExecuteNonQuery in ADO

Using the output parameter works fine, thanks JB. I will go with that, but
if anyone knows why the returned value was trying to be converted to an
integer I would still really like to know.
[color=blue]
> To return anything othe than an integer I would change the direction from
> returnvalue to output & allow a paameter in the stored procedure with
> '@RetVal varchar (20) output' or similar, then set @retval instead of
> trying to return it.
>
> --
> Jonathan Bailey.
>
>[/color]