Solved

SQL Select last record in DB

Posted on 2001-08-30
17
644 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
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 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

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!

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 informatio…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

729 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