Solved

ASP.NET Datareader skips first records

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

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 74

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 74

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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 74

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 74

Expert Comment

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

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 74

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

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…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

760 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

23 Experts available now in Live!

Get 1:1 Help Now