<EagleRedHighFlyingBirdscom@discussions.microsoft.com> wrote:

>I found the source of the problem. The MSDN documentation states that
>retrieval of output value should be done after the DataReader is closed:
>
>"While the SqlDataReader is being used, the associated SqlConnection is busy
>serving the SqlDataReader, and no other operations can be performed on the
>SqlConnection other than closing it. This is the case until the Close method
>of the SqlDataReader is called. For example, you cannot retrieve output
>parameters until after you call Close."
>
>We have using an int output parameter (@result) to return error codes and I
>had been checking that value before processing any result set. A error might
>indicate that the data in a returned result set might not be valid and should
>either not be processed or discarded. Up to this point there had never been
>a problem with this scheme until we needed to return a bit type in an output
>parameter. This failed until we closed the DataReader on the client.
>
>"Mary Chipman [MSFT]" wrote:
>
>> The optimal solution is to craft your stored procedures so that they
>> always return values that the client application can easily handle.
>>
>> -mary
>>
>> On Fri, 12 Oct 2007 12:24:02 -0700, EagleRed@HighFlyingBirds.com
>> <EagleRedHighFlyingBirdscom@discussions.microsoft.com> wrote:
>>
>> >No, it does not. It always returns null, but the underlying table column
>> >cannot be null. Something is happening in the SqlParameter/SqlCommand
>> >objects that cause a null value to appear in the calling application.
>> >
>> >Thanks,
>> >Eagle.
>> >
>> >"Mary Chipman [MSFT]" wrote:
>> >
>> >> Does the stored procedure always return the correct value in terms of
>> >> what the client application is expecting? One way to handle the
>> >> situation is to explicitly set the output parameter to either 0 or 1.
>> >> One option is set a default of NULL when you declare it:
>> >>
>> >> @flag1 bit = NULL OUTPUT
>> >>
>> >> You can then put logic in to test if the flag is null before sending
>> >> the output back to the client. The alternative is to create a working
>> >> variable, do the SELECT, and then set the flag to either 0 or 1 based
>> >> on whether the working variable is null. This is the kind of logic
>> >> that is best handled on the server, not in your client code.
>> >>
>> >> -mary
>> >>
>> >> On Thu, 11 Oct 2007 10:31:01 -0700, EagleRed@HighFlyingBirds.com
>> >> <EagleRedHighFlyingBirdscom@discussions.microsoft.com> wrote:
>> >>
>> >> >By the way I have tried changing
>> >> >
>> >> >Set @flag1 = (SELECT flag From MyTable
>> >> >Where id = @id2)
>> >> >
>> >> >to
>> >> >
>> >> >SELECT @flag1 = flag From MyTable
>> >> >Where id = @id2
>> >> >
>> >> >I got the same result.
>> >> >
>> >> >Thanks,
>> >> >Eagle
>> >> >
>> >> >
>> >> >"EagleRed@HighFlyingBirds.com" wrote:
>> >> >
>> >> >> I am encountering the following problem with a call to a stored procedure
>> >> >> from C# code.
>> >> >>
>> >> >> The stored procedure looks like this:
>> >> >>
>> >> >> ALTER PROCEDURE sproc
>> >> >> @id1 int,
>> >> >> @id2 int,
>> >> >> @trans_date datetime,
>> >> >> @flag1 bit output,
>> >> >> @result int Output
>> >> >> AS
>> >> >> .
>> >> >> .
>> >> >> .
>> >> >> Set @flag1 = (SELECT flag From MyTable
>> >> >> Where id = @id2)
>> >> >> -- A record is found and debugger shows @flag1 has a value
>> >> >> .
>> >> >> .
>> >> >> .
>> >> >> Return 0
>> >> >>
>> >> >> The C# code:
>> >> >> sqlCmdData.CommandText = "sproc";
>> >> >> sqlCmdData.Parameters.Clear();
>> >> >> sqlCmdData.Parameters.AddWithValue("@id1", _id1);
>> >> >> sqlCmdData.Parameters.AddWithValue("@id2", _id2);
>> >> >> sqlCmdData.Parameters.AddWithValue("@trans_date", DateTime.Now);
>> >> >> SqlParameter sqlParmFlag = new SqlParameter("@flag1", SqlDbType.Bit);
>> >> >> sqlParmFlag.Direction = ParameterDirection.Output;
>> >> >> sqlCmdData.Parameters.Add(sqlParmFlag);
>> >> >> SqlParameter sqlParmResult = new SqlParameter("@result", SqlDbType.Int);
>> >> >> sqlParmResult.Direction = ParameterDirection.Output;
>> >> >> sqlCmdData.Parameters.Add(sqlParmResult);
>> >> >> SqlDataReader dr = sqlCmdData.ExecuteReader();
>> >> >> int _status = Convert.ToInt32(sqlParmResult.Value); // This works OK
>> >> >> bool flag = (bool)sqlParmFlag.Value; // sqlParmFlag.Value is always NULL
>> >> >>
>> >> >> This throws a NULL exception.
>> >> >>
>> >> >> Any ideas on what may be happening here?
>> >> >>
>> >> >> I would appreciate any help.
>> >> >>
>> >> >> Thanks,
>> >> >> Eagle
>> >> >>
>> >> >>
>> >> >>
>> >>