Solved

SQL Select last record in DB

Posted on 2001-08-30
17
643 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
[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
  • 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
Independent Software Vendors: 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 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

Technology Partners: 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!

Question has a verified solution.

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

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…

738 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