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 BindYearDropDown()
Dim con As New
SqlConnection(ConfigurationSettings.AppSettings("SQLServer_BOS"))
Dim cmd As SqlCommand = New SqlCommand("spAgendas_GetAllAgendaYears", con)
lstAgendas.DataSource = cmd.ExecuteReader(CommandBehavior.CloseConnection)
lstAgendas.DataTextField = "Year"
lstAgendas.DataValueField = "Year"
lstAgendas.DataBind()
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(ConfigurationSettings.AppSettings("SQLServer_BOS"))
Dim cmd As SqlCommand = New SqlCommand(sSQL, con)
Dim dr As SqlDataReader
Try
con.Open()
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
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 BindYearDropDown()

lstAgendas.DataSource =
BoardBO.GetADataReader("spAgendas_GetAllAgendaYears")
lstAgendas.DataTextField = "Year"
lstAgendas.DataValueField = "Year"
lstAgendas.DataBind()

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 BindYearDropDown 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(ByVal sSQL As String) As DataSet
Dim con As String =
ConfigurationSettings.AppSettings("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 BindYearDropDown()

lstAgendas.DataSource =
BoardBO.GetADataSet("spAgendas_GetAllAgendaYears").Tables(0)
lstAgendas.DataTextField = "Year"
lstAgendas.DataValueField = "Year"
lstAgendas.DataBind()

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

 

[Marina]

You call .Read before you return the erader - which advances the reader to
the first row. I assume your problem is always missing the first row from
the result set? Well, this is why.

 

[Andrew]

Damn, I took all that time and effort to write up the message and you go an
solve it in three lines. I guess .Net is more productive. Thanks for
the help, it was right on the money!

-- Andrew