Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

ASP.NET Datareader skips first records

Posted on 2009-04-03
13
Medium Priority
?
559 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 2000 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
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…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

618 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