Missing Rows in a DataBind()

[Andrew]

Hey all,

Have a strange one here, and being still fairly new to .NET isn't helping me
understand it.

I am having a problem where a DataReader doesn't return all the rows when I
try to use a method from a separate class file that returns a DataReader,
where when I code the DataReader in the .aspx file it does. Below are the
details and code of what I am runnign into. I appreciate any help/insight
you can provide.

I am working on a fairly simple app that allow a user to add/update/delete
meeting minutes and agendas. I have a database (SQL Server 2000) storing
some simple info, and a directory the Mins/Agnd PDF file is uploaded to. I
created a little web user control that uses a DataGrid and DropDown listbox.
I bind each one separately, and use stored procedures to query the DB.

Originally I did the following function, called in the page's OnLoad method
only if Page.IsPostback was false:

Private Sub BindYearDropDow n()
Dim con As New
SqlConnection(C onfigurationSet tings.AppSettin gs("SQLServer_B OS"))
Dim cmd As SqlCommand = New SqlCommand("spA gendas_GetAllAg endaYears", con)
lstAgendas.Data Source = cmd.ExecuteRead er(CommandBehav ior.CloseConnec tion)
lstAgendas.Data TextField = "Year"
lstAgendas.Data ValueField = "Year"
lstAgendas.Data Bind()
End Sub

(My S-Proc is a basic "SELECT DISTINCT vYear FROM MyTable ORDER BY vYear
DESC")

All this does is populate a DropDown list with the distinct years in the DB
(2000 through 2004 in my case). I decided to use a DataReader beacuase I am
not doing two-way communication, and also the DR runs much faster due to
lesser overhead requirements. Well, as I was building this app, I started
to have more and more methods needing to talk to the DB. Well, I wanted to
create a single function that I could just call and it would return a set of
records that I could rummage through or bind to data sources. This way I
could just reuse the same method over and over, and if anything in my DB
connection changed, I didn't have to worry about finding all the location
throughout the app and missing some change.

So, I created the following function in a "business objects" class file:

Public Function GetADataReader( ByVal sSQL As String) As SqlDataReader
Dim con As New
SqlConnection(C onfigurationSet tings.AppSettin gs("SQLServer_B OS"))
Dim cmd As SqlCommand = New SqlCommand(sSQL , con)
Dim dr As SqlDataReader
Try
con.Open()
dr = cmd.ExecuteRead er(CommandBehav ior.CloseConnec tion)
dr.Read()
Return dr
Catch ex As Exception
Throw
End Try
End Function

And then I altered my binding method back in my control to the following
(BoardBO is my "business objects" class file):

Private Sub BindYearDropDow n()

lstAgendas.Data Source =
BoardBO.GetADat aReader("spAgen das_GetAllAgend aYears")
lstAgendas.Data TextField = "Year"
lstAgendas.Data ValueField = "Year"
lstAgendas.Data Bind()

End Sub

And now we get to my question/problem. When I did it the first way, I got
all five years (2004 through 2000). Yet when I do it the second way, I only
get 4 years (2003 through 2000). I debugged the code by adding a breakpoint
on the GetADataReader method and stepping through it one line at a time.
After the dr.Read() call, in the command window I typed "?dr(0)" and it gave
me "2004", yet once the DataBind() method of the BindYearDropDow n method is
called, "2004" is nowhere to be seen. Huh? I don't get this.

Wondering if it was something about a "remote" DataReader method, I also
created a "remote" DataSet method to do the exact same thing, but using a
DataSet object vice a SQLDataReader. That function is :

Public Function GetADataSet(ByV al sSQL As String) As DataSet
Dim con As String =
ConfigurationSe ttings.AppSetti ngs("SQLServer_ BOS")
Dim da As New SqlDataAdapter( sSQL, con)
Dim ds As New DataSet
Try
da.Fill(ds)
Return ds
Catch ex As Exception
Throw
End Try
End Function

And then I changed my dropdown list data bind method to:

Private Sub BindYearDropDow n()

lstAgendas.Data Source =
BoardBO.GetADat aSet("spAgendas _GetAllAgendaYe ars").Tables(0 )
lstAgendas.Data TextField = "Year"
lstAgendas.Data ValueField = "Year"
lstAgendas.Data Bind()

End Sub

Low and behold, I got all five years once again (2004 - 2000)!

What is it about doing this with a DataSet instead of a DataReader that
gives me all my data? Why is the DataReader method loosing the first row in
the returned records? I don't understand this.

If you can help me to understand what is happening, I sure would appreciate
it. Thanks!!

-- Andrew