Solved

SQL Select last record in DB

Posted on 2001-08-30
17
640 Views
Last Modified: 2010-05-18
How do I select the last record in a DB using SQL in ASP?
0
Comment
Question by:donovan_bray
  • 7
  • 4
  • 3
  • +2
17 Comments
 
LVL 2

Expert Comment

by:tim_ferris
ID: 6440987
After you have formed your recordset, just

rsYourRecordset.movelast

Is that all you needed or is there something else???
0
 
LVL 9

Expert Comment

by:AlfaNoMore
ID: 6440988
SELECT TOP 1 * FROM table ORDER BY id_field DESC

and that'll bring you the last record added to this table, assuming you have an autonumbering column as your id_field.
0
 

Author Comment

by:donovan_bray
ID: 6440995
This is my code:

          sqlSelect = "select MAX(QuoteID) from QuoteLines"
          set rSelect = govtDB.Execute(sqlSelect)

<%=rSelect(QuoteID)%>


This is my error:
ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/admin/Admin_insert_action.asp, line 72
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 7

Expert Comment

by:John844
ID: 6440997
if you have a identity column, order by it descending and grab one record like.
select TOP 1 * from tablename order by recID desc

if you have a datestamp column, order by it descending and grab one record like.
select TOP 1 * from tablename order by dateStamp desc
0
 
LVL 7

Expert Comment

by:John844
ID: 6441005
change
<%=rSelect(QuoteID)%>
to
<%=rSelect("QuoteID")%>
0
 
LVL 7

Accepted Solution

by:
John844 earned 20 total points
ID: 6441007
and change
         sqlSelect = "select MAX(QuoteID) from QuoteLines"
to
         sqlSelect = "select MAX(QuoteID) as QuoteID from QuoteLines"
0
 
LVL 20

Expert Comment

by:jitganguly
ID: 6441013
Try this

sqlSelect = "select MAX(QuoteID) as quoteID from QuoteLines"
set rSelect = govtDB.Execute(sqlSelect)
if not rSelect.eof Then
 rSelect.movelast
<%=rSelect(QuoteID)%>
End if




0
 
LVL 9

Expert Comment

by:AlfaNoMore
ID: 6441025
Still gotta get that field name in quotes though

sqlSelect = "select MAX(QuoteID) as quoteID from QuoteLines"
set rSelect = govtDB.Execute(sqlSelect)
if not rSelect.eof Then
rSelect.movelast
Response.Write rSelect("QuoteID")
End if
0
 
LVL 20

Expert Comment

by:jitganguly
ID: 6441030
Right alfa. John pointed out first
0
 
LVL 9

Expert Comment

by:AlfaNoMore
ID: 6441032
:-)

Tough scrpa between us all for these points then...
0
 
LVL 9

Expert Comment

by:AlfaNoMore
ID: 6441033
:-)

Tough scrap between us all for these points then...
0
 

Author Comment

by:donovan_bray
ID: 6441036
Hi~ I used Johns solution and now I am getting:


Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] Circular reference caused by alias 'Lineid' in query definition's SELECT list.

/admin/Admin_insert_action.asp, line 44
0
 
LVL 9

Expert Comment

by:AlfaNoMore
ID: 6441040
Well in John's example there is no lineid. Can we see you whole SQL statement. Not your ASP code to produce, bu the string itself?
0
 
LVL 7

Expert Comment

by:John844
ID: 6441044
it must have a problem with the same name, try this instead

         sqlSelect = "select MAX(QuoteID) as MaxQuoteID from QuoteLines"
         set rSelect = govtDB.Execute(sqlSelect)

<%=rSelect("MaxQuoteID")%>
0
 
LVL 9

Expert Comment

by:AlfaNoMore
ID: 6441056
You might also find it better to create a Recordset object, rather than returning a recordset via the connection object's execute method. You'll have more options as to what type of Cursor and Lock you want
0
 
LVL 9

Expert Comment

by:AlfaNoMore
ID: 6441064
Ah, no need for the MoveLast if you're only returning one record!!! Ignore my previous comment.
0
 

Author Comment

by:donovan_bray
ID: 6441081
Thanks for all your help guys. I am only for days new to ASP and you all have been alot of help. Esspecially since my boss is too cheap to buy any books on ASP :(


Thanks again :)

0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
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.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

808 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