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!!
-- AndrewYou 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" <AndrewR2k1@.hotmail.com> wrote in message
news:eKv185lHEHA.828@.TK2MSFTNGP12.phx.gbl...
> 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
>
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
"Marina" <someone@.nospam.com> wrote in message
news:uxq6d$lHEHA.1220@.tk2msftngp13.phx.gbl...
> 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" <AndrewR2k1@.hotmail.com> wrote in message
> news:eKv185lHEHA.828@.TK2MSFTNGP12.phx.gbl...
helping
> me
when
> I
DataReader,
the
help/insight
add/update/delete
storing
> I
> listbox.
> method
> con)
> cmd.ExecuteReader(CommandBehavior.CloseConnection)
> DB
I
> am
started
> to
set
> of
I
location
SqlDataReader
got
> only
> breakpoint
> gave
> is
a
row
> in
> appreciate
>
