Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Select last record in DB

Posted on 2001-08-30
17
Medium Priority
?
654 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 80 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
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 tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

596 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