Solved

ASP.NET Datareader skips first records

Posted on 2009-04-03
13
436 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
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 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
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…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

773 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