Return_Value not being passed back

[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."

 

[nate axtell]

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

 

[J. Clay]

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

 

[nate axtell]

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?