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.

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
– Jeroen Mostert, Commented Feb 19, 2015 at 14:23

Kritner · Accepted Answer · 2015-02-19 14:19:11Z

2

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.

answered Feb 19, 2015 at 14:19

Kritner

13.8k11 gold badges53 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting output parameter from SqlCommand returns null

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related