Solved

ASP.NET Datareader skips first records

Posted on 2009-04-03
13
428 Views
Last Modified: 2012-06-22
Hello,

When I execute the below query my datareader always skips the first record....any thoughts as to why?
'## Sets query string ##

        Dim strQuery As String = "SELECT Vis_Num FROM Data WHERE MDY >= ? AND MDY <= ? ORDER BY Vis_Num ASC"
 

          Dim cmdSearch As New OleDbDataAdapter(strQuery, dbConn)
 

        '## Takes value from search box and uses it to qry against Vis_Num in database ##

        cmdSearch.SelectCommand.Parameters.AddWithValue("MDY", txtStartdate.Text)

        cmdSearch.SelectCommand.Parameters.AddWithValue("MDY", txtEnddate.Text)
 

        Dim reader As OleDbDataReader
 

        Try

            dbConn.Open()

            reader = cmdSearch.SelectCommand.ExecuteReader()
 

            '## execute reader ##

            reader.Read()
 

            If reader.HasRows() Then
 

                '## Binds dropdown box to database fields ##

                ddVisnum.DataSource = reader

                ddVisnum.DataTextField = "Vis_Num"

                ddVisnum.DataValueField = "Vis_Num"
 

                '## Inserts value into the dropdown box ##

                ddVisnum.Items.Insert(0, New ListItem("Open"))

                ddVisnum.DataBind()

                txtError.Visible = False
 

            Else
 

                '## Exits sub, clears values and shows error ##

                txtError.Visible = True

                txtStartdate.Text = ""

                txtEnddate.Text = ""

                ddVisnum.Items.Clear()

                Exit Sub
 

            End If
 

        Catch exdd As Exception

            Response.Write(exdd)
 

        End Try

Open in new window

0
Comment
Question by:ITHelper80
  • 7
  • 4
  • 2
13 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 24063660
Whenever you call the Read() method of a DataReader, you are advancing the position of the reader. So in line 17, you advance the reader one position. Then your setting your DataSource in line 22, after the reader is advanced. Readers are forward only. Try removing line 17. You instantiated the reader in line 14, so there is no need to call line 17.
0
 
LVL 15

Accepted Solution

by:
oobayly earned 500 total points
ID: 24063670
The comment " '## execute reader ##" is incorrect, you've already exectuted it.
By calling Read on DataReader reads the first record. When you bind to the DataReader, the 1st record is discrarded.

Remove the line "reader.Read()" and it should work.
0
 
LVL 6

Author Comment

by:ITHelper80
ID: 24063703
WOW thanks that did it.

So I understand for future reference the ".Databind() "kicks off my reader?
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 24063719
reader = cmdSearch.SelectCommand.ExecuteReader()

populates your reader with data. The .Databind() "reads" the DataSource (in your case a reader) and populates the data.
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 24063726
Clarification:

The .Databind() "reads" the DataSource (in your case a reader) and populates the data into the control.
0
 
LVL 15

Expert Comment

by:oobayly
ID: 24063764
To be quite honest, I've never databound to a DataReader, but yes that is what I'd expect. I always use datareader is if I want to execute a single row.
Damn, just realised kaufmed got in a minute before.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 6

Author Comment

by:ITHelper80
ID: 24063845
Lol well what should be used in place of a datareader when returning multiple records?

In the interest of learning I have another question, same type of issue.

Below I am using a datareader that is databound(almost same code) but if I comment out reader.read() it doesnt work....

Signed,
Confused
        'Sets query string

        Dim strQuery As String = "SELECT Vis_Num,Status,Employee,Present_Method,Proposed_Method,Validator,Notes,ValDate FROM Data WHERE Vis_Num = ?"

        Dim cmdSearch As New OleDbDataAdapter(strQuery, dbConn)
 

        'Takes value from search box and uses it to qry against Vis_Num in database

        cmdSearch.SelectCommand.Parameters.AddWithValue("Vis_Num", CInt(txtSearch.Text))

        Dim reader As OleDbDataReader
 

        Try

            dbConn.Open()

            reader = cmdSearch.SelectCommand.ExecuteReader()
 

            'execute reader

            reader.Read()
 

            If reader.HasRows Then
 

                'grabs values

                txtVisNum.Text = reader("Vis_Num").ToString()

                txtName.Text = reader("Employee").ToString()

                txtPresent.Text = reader("Present_Method").ToString()

                txtProposed.Text = reader("Proposed_Method").ToString()
 

                'removes error message

                txtError.Visible = False
 

            Else
 

                'Exits sub, clears values and shows error

                txtError.Visible = True

                txtVisNum.Text = ""

                txtName.Text = ""

                txtName.Text = ""

                txtPresent.Text = ""

                txtProposed.Text = ""

                ddstatus.Items.Clear()

                ddFacilitator.Items.Clear()

                Exit Sub
 

            End If
 

        Catch ex As Exception
 

            Response.Write(ex)
 
 

        End Try
 

        reader.Close()

Open in new window

0
 
LVL 6

Author Comment

by:ITHelper80
ID: 24063859
Crap nevermind...I was just looking over my code again and I see I dont issue the databind command.....my bad
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 24063884
Because you are accessing individual columns within a row. Take line 19. You call:

txtVisNum.Text = reader("Vis_Num").ToString()

At this point you have advanced the reader, so the first record is accessible. In the first row you are grabbing the data in the column "Vis_Num". If you take out

reader.Read()

then the data has not been made available. Think of it as before you call Read(), your reader is of in Utopia, just chillin'. You have to call Read() to tell the reader to come back to the real world and make your data available.

The reason you didn't have to do this with the previous example, is that the DataBind() call takes care of that for you.
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 24063896
Sorry for the lame example using Utopia. It's Friday :)
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 24063918
Instead of saying "Because you are accessing individual columns within a row," I should say because you are manually accessing the data within the reader rather than the DataBind() handling it for you.
0
 
LVL 6

Author Comment

by:ITHelper80
ID: 24063933
when doing a databind do you need to close it out somehow or does it automatically do that?
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 24063986
I believe it does close the reader, but you can check using DataReader's IsClosed method (after your DataBind() of course!):
If Not reader.IsClosed Then

     reader.Close()

End If

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now