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

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

Hello everyone.

I'e got a same problem with a bit different in coding.

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.

I couldn't understand why is wrong?

Following code snippet shows you the way that I used:

SqlParameter[] Params = new SqlParameter[2];

Params[0] = new SqlParameter("@RoleId", RoleId);

Params[1] = SqlParameter("@return, "");

Params[0] = SqlParameter.Direction.InputOutput;

Params[1] = SqlParameter.Direction.Output;

// Execute

SqlHelper.ExecuteNonQuery(Connection, CommandType.StoredProcedure, "ManageRoles", Params);

// Retrieve Value

int Role = Convert.ToInt32(Params[0]. Value.ToString());

string result = Params[1].Value.ToString();

Any suggestion to solve this problem would be truly appreciated.

- Thanks.

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

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.