We help IT Professionals succeed at work.

ASP.NET Datareader skips first records

965 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

Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2015

Commented:
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.
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
WOW thanks that did it.

So I understand for future reference the ".Databind() "kicks off my reader?
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2015

Commented:
reader = cmdSearch.SelectCommand.ExecuteReader()

populates your reader with data. The .Databind() "reads" the DataSource (in your case a reader) and populates the data.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2015

Commented:
Clarification:

The .Databind() "reads" the DataSource (in your case a reader) and populates the data into the control.
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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

Author

Commented:
Crap nevermind...I was just looking over my code again and I see I dont issue the databind command.....my bad
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2015

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2015

Commented:
Sorry for the lame example using Utopia. It's Friday :)
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2015

Commented:
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.

Author

Commented:
when doing a databind do you need to close it out somehow or does it automatically do that?
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2015

Commented:
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.