Pages

Friday 30 September 2011

SQL Output Parameter

I don't why but any SQL Parameters that are defined as outputs are not available until the SqlDataReader has been closed.
So this works:
SqlParameter reservationNumberParameter = new SqlParameter("@ReservationNumber", SqlDbType.NVarChar, 15);
reservationNumberParameter.Direction = ParameterDirection.Output;
command.Parameters.Add(reservationNumberParameter);

reader = command.ExecuteReader();

reader.Close();

reservationNumber = Convert.ToString(reservationNumberParameter.Value);

Whereas this does not work:
SqlParameter reservationNumberParameter = new SqlParameter("@ReservationNumber", SqlDbType.NVarChar, 15);
reservationNumberParameter.Direction = ParameterDirection.Output;
command.Parameters.Add(reservationNumberParameter);

reader = command.ExecuteReader();

reservationNumber = Convert.ToString(reservationNumberParameter.Value);

reader.Close();