ReturnValue when using ExecuteNonQuery in ADO

  • Thread starter Thread starter nate axtell
  • Start date Start date
N

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.VarChar, 100).Direction =
ParameterDirection.ReturnValue
oCMD.ExecuteNonQuery()

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?
 
nate axtell said:
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.VarChar, 100).Direction =
ParameterDirection.ReturnValue
oCMD.ExecuteNonQuery()

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?
Click to expand...

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.
 
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.
 
Back
Top