Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

The last record in a DataReader

Posted on 2004-11-29
10
Medium Priority
?
3,326 Views
Last Modified: 2011-09-20
How do I get the last record from a DataReader?
0
Comment
Question by:YZlat
  • 4
  • 4
  • 2
10 Comments
 
LVL 8

Accepted Solution

by:
mani_sai earned 150 total points
ID: 12699515
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
 
LVL 35

Author Comment

by:YZlat
ID: 12700056
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
 
LVL 8

Expert Comment

by:mani_sai
ID: 12700183
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
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 35

Author Comment

by:YZlat
ID: 12700268
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
 
LVL 8

Expert Comment

by:mani_sai
ID: 12700334
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
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 12701336
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
 
LVL 35

Author Comment

by:YZlat
ID: 12705407
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
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 12705455
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
 
LVL 8

Expert Comment

by:mani_sai
ID: 12706038
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
 
LVL 35

Author Comment

by:YZlat
ID: 12708784
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

580 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