Return_Value not being passed back

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

nate axtell

In VB .NET 1.1 I have created an oledbcommand object that queries the
xp_cmdshell in the Master database. This part works fine. But when I try
to access the Return_Value parameter's value there is nothing there.
IsNothing() returns True; IsDBNull() returns False. Shouldn't the
xp_cmdshell procedure return an integer value? Here's my code:

Dim conn as New OleDbConnection(connstr)
Dim cmd as New OleDbCommand("xp_cmdshell", conn)
cmd.CommandTimeout = 300 '** 6 min timeout
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("", OleDbType.VarChar, 4000).Value = valid_osql_string
Try
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
Catch ex as Exception
'** show error
End Try
cmd.Parameters.Add("RETURN_VALUE", OleDbType.Integer).Direction =
ParameterDirection.ReturnValue
IsDbNull(cmd.Parameters("RETURN_VALUE").Value) '** returns False
IsNothing(cmd.Parameters("RETURN_VALUE").Value) '** returns True

I tried adding the parameter before the cmd.ExecuteNonQuery call but
received the following error:
"Error executing extended stored procedure: Invalid Parameter Type."
 
I fixed my below problem by adding the Return_Value paramter before the
command_string parameter. Does anyone know why this fix makes it work?
What is going on with the parameter collection?
thanks,
Nate
 
You must define all parameters, even return parameters, before executing any
command. I have always considered it good form to define the Return
parameter first and then the remaining parameters.

HTH,
Jim
 
But do you know why if I reverse the two parameter adds the query fails?
Could it possible be because of the NO_OUTPUT optional flag that xp_cmdshell
accepts as a second parameter?
 
Back
Top