The last record in a DataReader

How do I get the last record from a DataReader?
LVL 35
YZlatAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mani_saiCommented:
You can try this:

1)Modifiy your SQL Query something like this:

Select Top 1 * from Employee Order By lname Desc

This will pull last record from your query.

2) Then Use this query in VB.Net Code

Dim sqlString As String
Dim strEmployeeID as String

sqlString  = "Select Top 1 * from Employee Order By lname Desc"

 Dim cmd As New SqlCommand(sqlString, objConnection)
  objConnection.Open()

  Dim dr As SqlDataReader
  dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

If dr.Read Then

strEmployeeID  = dr("Emp_ID")

End If


Hope it helps.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
YZlatAuthor Commented:
That didn't work. In my sql statement I do ORDER BY three different fields and your sql statement returns the very first record
0
mani_saiCommented:
can you show your sql statement?

With sample output you get when you exceute sql query

and also what you expect from query to be displayed in front end ?

Basically what i meant was if you use top1 with order by desc clause will pull the last record.



0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

YZlatAuthor Commented:
SELECT  TOP 1 *
               FROM (locations INNER JOIN customer ON locations.location_id = customer.location_id)
               INNER JOIN orders ON customer.cust_id = orders.cust_id
               ORDER BY  locations.locationZone, locations.locationName, customer.lastname DESC
0
mani_saiCommented:
I think you should get the first record if you remove DESC from the query.

If you have DESC you should get the last record as first record.

I am pretty sure Top 1 with ORDER BY DESC should pull last record from any SQL Query.
0
Jeff CertainCommented:
YZlat,
You're using SELECT TOP 1 to return only 1 record... so what do you mean by getting the "last" record from the datareader?
0
YZlatAuthor Commented:
if I remove DESC I will get over 100 records and if I put DESC back in, the query will retrieve the first record of those 100 records.

Chaosian, let's say a query returns records 1,2,3,4,5 SELECT TOP 1 will return record 1, but SELECT TOP 1 ORDER BY DESC will return records in descending order, it will retrieve record 5
0
Jeff CertainCommented:
Actually, if you remove DESC, you'll still get only one record (TOP 1).

So, in your case, you want the customer with the last alphabetical last name? Right now, you'll get the customer with the alphabetically last surname (last name) from the first location zone and first location name... or is this what you want?
0
mani_saiCommented:
Another solution(not very effective but works)

Step1:

'open the command object with your query returning only count of records
Dim cmd As New SqlCommand("SELECT COUNT(*) FROM Table_A", sqlCon)

'Get the total count of records
Dim totalRows As Int16 = CInt(cmd.ExecuteScalar())


Step2:

Dim rowCount As Int16
Dim dr As SqlDataReader
Dim qry as String

qry = "SELECT * FROM TABLE_A"   'replace with your SQL query
Dim cmd1 as New SqlCommand(qry, sqlCon)
dr = cmd1.ExecuteReader()

While dr.Read()
rowCount += 1
     If rowCount  = totalRows  then   'It is your last record

        'capture your last record here

   End If
End While


I did not test the code, but the logic will work i believe.

Good Luck



0
YZlatAuthor Commented:
OK, I figured it out: I ORDER BY three fields, all I needed to do is add DESC after each one:

SELECT  TOP 1 *
               FROM (locations INNER JOIN customer ON locations.location_id = customer.location_id)
               INNER JOIN orders ON customer.cust_id = orders.cust_id
               ORDER BY  locations.locationZone DESC, locations.locationName DESC,  customer.lastname DESC

mani_sai, thanks for your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.