Solved

ASP.NET Datareader skips first records

Posted on 2009-04-03
13
465 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

734 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