Hi all,

I have the following problem:

I have a private method that returns a SqlDataReader. For this to work I
have not to close the DB connection in the above method. I do this only to
have the possibility to iterate through the entire rows set in a while loop,
located in the calling method.

I have included a few lines of code to get the number of rows fetched from
the DB. I do this with SqlParameter("RETURN_VALUE", SqlDbType.Int) [ I am
using a stored procedure that returns @@rowcount].

I have found out that I am getting the appropriate value for the stored
procedure parameter ("RETURN_VALUE") ONLY when I have explicitly close the
DB connection. Unfortunately when the control is returned to the calling
method the usual error message:

Invalid attempt to Read when reader is closed

is being received as the connection is closed and there's no such DataReader
already.

Does anyone know a workaround for this? I need the report to the user the
number of fetched rows.

Thanks,

Martin

--------------------------

Code:

private SqlDataReader GetReader(string parameter, string date)

{

     DateTime MyDateTime;

.....//. date parsing

     SqlConnection myConn = new SqlConnection(ConnectionString());

     SqlCommand myCmd = new SqlCommand();

     SqlDataReader myReader=null;

     myCmd.CommandType = CommandType.StoredProcedure;

     myCmd.Connection = myConn;

     myCmd.CommandText = "GetData";

     myCmd.CommandTimeout = 250;

     SqlParameter Param1 = new SqlParameter();

     Param1 = myCmd.Parameters.Add("@parameter", SqlDbType.VarChar, 12);

     Param1.Direction = ParameterDirection.Input;

     Param1.Value = parameter;

     SqlParameter Param2 = new SqlParameter();

     Param2 = myCmd.Parameters.Add("@date", SqlDbType.VarChar, 20);

     Param2.Direction = ParameterDirection.Input;

     Param2.Value =  MyDateTime.Date.ToShortDateString();

     SqlParameter outValue = new SqlParameter();

     outValue = myCmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int);

outValue.Direction = ParameterDirection.ReturnValue;

     myConn.Open();

     myReader = myCmd.ExecuteReader();

// this works only is myConn.Close() is executed but then

// we cannot return a READER to the calling method???

intRowsReturned = Convert.ToInt32(myCmd.Parameters["RETURN_VALUE"].Value);

                 return myReader;

           }

// the calling method

private void btnSQLGet_Click(object sender, System.EventArgs e)

{

                 .

     // get the data in a reader

     SqlDataReader myReader = GetReader(cboParameter.Text, txtDate.Text);

     if (myReader==null)

           return;

.

}