Why does IDataReader lose a row?

Question

I am facing this problem. I have a stored procedure which returns 6 rows when I execute it.

But when I am retrieving the rows in my app by using ExecuteReader, it only returns only 5 rows. Why is it losing a row??

My stored procedure consists of 5 union statements which are getting filled from a single table:

 dbase.AddInParameter(cmd, "@LoginUser", DbType.String, UserID);

   try
   {
      using (IDataReader  dr = dbase.ExecuteReader(cmd))
      if (dr.Read())
      {
         dt = new DataTable("DashBoard");
         dt.Load(dr);
      }
   }

dbase is my database object. And cmd is the SqlCommand used to call the stored procedure.

UserID is parameter is passing

Stored procedure code is:

ALTER PROCEDURE [dbo].[USP_ViewAdminDashBoard](@LoginUser varchar(75)) 
    -- Add the parameters for the stored procedure here
AS
BEGIN

    SET NOCOUNT ON;
    SET DATEFORMAT DMY;
    DECLARE @LastLoginDate  as DateTime

        Select @LastLoginDate = dbo.UDF_GetLastLoginByUser(@LoginUser)
    Select 'Last Login Date', convert(varchar(12),@LastLoginDate,105)

    Union 
    Select  'Nos. Records pending for Upload' as Title, convert(varchar(5),COUNT(s.BatchID)) Total from  dbo.BREGISTRATIONENTRY s, Dbo.TBL_iBATCH B
    where  B.BatchID = s.BatchID And b.Forwarded = 0 and b.isBatchClosed = 1
END

Please show us the stored procedure too. – Fischermaen Dec 06 '11 at 07:46 — Fischermaen, Dec 06 '11 at 07:46

gbn · Accepted Answer · 2011-12-06T08:38:04.177

11

Your first dr.Read is advancing to the first row. The DataTable.Load is reading the remaining rows but not the first row

Use HasRows to test for existence and don't use Read before the DataTable.Load

Edit:

Just load the DataTable without testing first: then test on the DataTable. There is no HasRows in the IDataReader interface.

edited Dec 06 '11 at 08:38

answered Dec 06 '11 at 07:50

gbn

422,506
82
585
676

Thanks Pal i miss the basic's – joshua Dec 06 '11 at 07:58
1

Unfortunately, `IDataReader` does not have `HasRows`, so I guess you can just create the `DataTable` and check if the result has rows instead. – C.Evenhuis Dec 06 '11 at 08:32
1

Yes i did the same.but suppose if reader fails to get data.then at the time of checking Datatable it gives the error that DR is null but why dont know – joshua Dec 07 '11 at 06:10
1

@Madhav: you have to work with what they give you: and you can't test the DR first... – gbn Dec 07 '11 at 07:31

score 0 · Answer 2 · answered May 03 '13 at 11:30

joshua i also has this problem while using enterprise library more than two time even i use same code written down but both time i found a problem in my store procedure there must be any wrong selection in query or any think else which db server does not detect and i solve it and my IDataReader select all rows and also use same way as suggest by 'gbn' using (IDataReader dr = oDb.ExecuteReader(p_oDbCommand)) {

                if (dr != null)
                {

                   ds.Tables[0].Load(dr);
                }

            }
            return dt;

score 0 · Answer 3 · answered Dec 06 '11 at 07:57

0

IDataReader is a forward reader,it means when you read a row,that row will be deleted from the reader and you can't get it from the reader any more.

answered Dec 06 '11 at 07:57

I need an IPhone.

11
2

Why does IDataReader lose a row?

3 Answers3