locked
SqlHelper.ExecuteReader not populating ReturnValue RRS feed

  • Question

  • User-209105085 posted

    I have StoredProc,which returns 0 if there is no error. My StoredProc look like this

    CREATE PROCEDURE dbo.usp_GetData
       @intProviderID BIGINT
       
       
    AS 
    SET NOCOUNT ON

      DECLARE @ERROR    INT,
              @ROWCOUNT INT
     
     
     SELECT  

      FieldLenght,
      FieldPosition
     FROM dbo.FieldData WITH (READUNCOMMITTED)
     WHERE ProviderID = @intProviderID
     ORDER BY FieldPosition ASC
     
     
        SELECT
                @ERROR = @@ERROR, @ROWCOUNT = @@ROWCOUNT

      IF @ERROR<>0
                RETURN 1

      IF @ROWCOUNT=0
                RETURN 2     

    RETURN 0
    GO

    This is wroking fine With SqlHelper.ExecuteDataset . I Get 0 in SQLParam[0].Value,Which i wanted.

    SqlParameter[] SQLParam = new SqlParameter[2];   
    SQLParam[0] = new SqlParameter("@ReturnValue", SqlDbType.Int);
    SQLParam[0].Direction = ParameterDirection.ReturnValue; 
      
    SQLParam[1] = new SqlParameter("@intProviderID", SqlDbType.BigInt);
    SQLParam[1].Value = lProviderID;
        
    dataset= SqlHelper.ExecuteDataset(ConnectionString, CommandType.StoredProcedure,
         "dbo.usp_GetData", SQLParam)

    but if i use DataReader as

    datareader= SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "dbo.usp_GetData", SQLParam);

    I dont get any value in SQLParam[0].Value . It says "undefined value"

    any idea

    Friday, December 16, 2005 12:25 PM

All replies

  • User1424575140 posted
    I'm pulling from memory: I think you may need to read through all the rows of data before you will have access to the return value.
    Friday, December 16, 2005 3:10 PM
  • User-85510428 posted

    Hello everyone.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

    <o:p> </o:p>

    I'e got a same problem with a bit different in coding. <o:p></o:p>

    I should receive some values which are sent by Sql stored procedures as output parameters. In fact, I've done this in Windows application by SqlHelper class and thought both of them (in win & web) have to be same. However after running Sqlhelper methods (executenonequery, executescaler) nothing can be returned into Web Form.<o:p></o:p>

    I couldn't understand why is wrong? <o:p></o:p>

    Following code snippet shows you the way that I used: <o:p></o:p>

    <o:p> </o:p>

    SqlParameter[] Params = new SqlParameter[2];<o:p></o:p>

    <o:p> </o:p>

    Params[0] = new SqlParameter("@RoleId", RoleId);<o:p></o:p>

    Params[1] = SqlParameter("@return, "");<o:p></o:p>

    <o:p> </o:p>

    Params[0] = SqlParameter.Direction.InputOutput;<o:p></o:p>

    Params[1] = SqlParameter.Direction.Output;<o:p></o:p>

    <o:p> </o:p>

    // Execute<o:p></o:p>

    SqlHelper.ExecuteNonQuery(Connection, CommandType.StoredProcedure, "ManageRoles", Params);<o:p></o:p>

    <o:p> </o:p>

    // Retrieve Value<o:p></o:p>

    int Role = Convert.ToInt32(Params[0]. Value.ToString());<o:p></o:p>

    string result = Params[1].Value.ToString();<o:p></o:p>

    <o:p> </o:p>

    <o:p> </o:p>

    <o:p> </o:p>

    Any suggestion to solve this problem would be truly appreciated.<o:p></o:p>

    - Thanks.<o:p></o:p>

    Saturday, December 17, 2005 12:38 AM
  • User-209105085 posted
    ok now i got the value...if i close the reader i get the value in SQLParam[0]. and then .depend on that value i set my return status object. but when value is 0 which is SUCCESS i need the reader again in BO layer to iterate thru records.but now the reader is closed. so i cant use it..I know i can use Dataset but i'm not doing any update its will be just simple retrieve operation.any other solution
    Monday, December 19, 2005 6:01 PM
  • User1424575140 posted

    If you are passing the data between layers and the the data is fairly small, you should consider a dataset.

     

    Tuesday, December 20, 2005 6:16 PM
  • User1293054447 posted

    I prefer pass custom object between layers, I assum you follow three tier arachitecture:

    UI-BL-DAL

    So your DAL fetch data from database and return DataReader to BL, then BL build strong type custom object, close DataReader and connection, return custom object back to your UI.

    Here is rule: fetch your data as late as possible and close connection as soon as possible. 

    Wednesday, December 21, 2005 10:26 AM