Getting output parameter from SqlCommand returns null

Question

I have this piece of code:

using (SqlCommand cmd = new SqlCommand())
{
   cmd.Connection = connection;
   cmd.CommandText = "UPDATE S " +
                     "SET S.WebId = S.WebId + 1 " +
                     "OUTPUT DELETED.WebId " +
                     "FROM jcdSetting S";
   SqlParameter parameter = cmd.Parameters.Add("@id", SqlDbType.Int);
   parameter.Direction = ParameterDirection.Output;
   int i = cmd.ExecuteNonQuery();
   webId = Convert.ToInt32(cmd.Parameters["@id"].Value);
}

For some reason, the last line fails, the parameter I'm trying to access is always DbNull. I've tried the query in Management Studio and it returns the value just fine.

I've checked the return value of ExecureNonQuery and that as well returns 1 as expected. I'm totally lost here.

Any help would be appreciated.

check this:http://stackoverflow.com/questions/18373461/execute-insert-command-and-return-inserted-id-in-sql — Ehsan Sajjad, Feb 19 '15 at 14:18
As others have pointed out, `OUTPUT` does not define an output parameter (those are only for stored procedures); examine the result set instead. Incidentally, your `UPDATE` has no `WHERE`. This can work correctly only if `jcdSetting` has only a single row, otherwise you'll get back a table of updated values. — Jeroen Mostert, Feb 19 '15 at 14:23

score 2 · Accepted Answer · answered Feb 19 '15 at 14:19

Using the output within a update statement, is equivalent to a select, whereas from the code you've posted you're expecting it to come out in an output parameter named @id.

Where does @id come from? you haven't defined it anywhere.

output in the method that you've used it would return a rowset, not a scalar value.

Getting output parameter from SqlCommand returns null

1 Answers1