none
Output parameters in application block RRS feed

  • Question

  • Thanks in advance

     sqlParams[0] = new SqlParameter("@TotalRecords", SqlDbType.Int);
     sqlParams[0].Direction = ParameterDirection.Output;

    dreader = Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(sqlconnection, CommandType.StoredProcedure, "BIS_DisplayEvents", sqlParams);

    totRecords = Convert.ToInt16(sqlParams[0].Value);

    This is the code written by me.
    But iam always getting totRecords=0.

    Please tell me the mistake that was done by me.


    Wednesday, November 21, 2007 7:27 AM

Answers

  •  

    Output variables are not updated before the reader is closed. You have to close it first.

     

    Just add dreader.Close() before reading the value:

    dreader = Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(...);
    dreader.Close();
    totRecords = Convert.ToInt16(sqlParams[0].Value);

     

    Also, you can use 'using' statement of .Net to ensure it is always closed, even if an exception is raised:

    using (SqlDataReader dreader = Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(...))

    {

     

    // use the reader here

     

    } // the reader will be closed here, OK to read the output variables

    totRecords = Convert.ToInt16(sqlParams[0].Value);

     

    Let me know if you have more questions,

     Nissim

    Monday, November 26, 2007 9:17 PM
    Moderator
  •  

    You will get this message when you try to use the SqlDataReader after closing it. Note that if you use 'using' statement, the reader is auto-closed in the end of the 'using' block - attempting to use the reader after it will cause the above exception. And only after the call to Close you can get the value of OUTPUT parameters.

     

    If you still see this exception, please post the code and point to the line this exception is raised on (you can find the line numbers in exception stack list).

    Wednesday, November 28, 2007 1:54 AM
    Moderator

All replies

  •  

    Output variables are not updated before the reader is closed. You have to close it first.

     

    Just add dreader.Close() before reading the value:

    dreader = Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(...);
    dreader.Close();
    totRecords = Convert.ToInt16(sqlParams[0].Value);

     

    Also, you can use 'using' statement of .Net to ensure it is always closed, even if an exception is raised:

    using (SqlDataReader dreader = Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(...))

    {

     

    // use the reader here

     

    } // the reader will be closed here, OK to read the output variables

    totRecords = Convert.ToInt16(sqlParams[0].Value);

     

    Let me know if you have more questions,

     Nissim

    Monday, November 26, 2007 9:17 PM
    Moderator

  • thanks for yuor response,

       Even after trying according to you. I am getting following error. Please send me solution for this. Thanks in advance.


    System.InvalidOperationException: Invalid attempt to Read when reader is closed. at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) at System.Data.SqlClient.SqlDataReader.Read() at BLBio.DisplayEvents(Int32 bisId, String cat) in c:\Inetpub\wwwroot\BioInfoSiteP\App_Code\BLBio.cs:line 601
    Tuesday, November 27, 2007 1:31 PM
  •  

    You will get this message when you try to use the SqlDataReader after closing it. Note that if you use 'using' statement, the reader is auto-closed in the end of the 'using' block - attempting to use the reader after it will cause the above exception. And only after the call to Close you can get the value of OUTPUT parameters.

     

    If you still see this exception, please post the code and point to the line this exception is raised on (you can find the line numbers in exception stack list).

    Wednesday, November 28, 2007 1:54 AM
    Moderator