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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 659
  • Last Modified:

SQL Select last record in DB

How do I select the last record in a DB using SQL in ASP?
0
donovan_bray
Asked:
donovan_bray
  • 7
  • 4
  • 3
  • +2
1 Solution
 
tim_ferrisCommented:
After you have formed your recordset, just

rsYourRecordset.movelast

Is that all you needed or is there something else???
0
 
AlfaNoMoreCommented:
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
 
donovan_brayAuthor Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
John844Commented:
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
 
John844Commented:
change
<%=rSelect(QuoteID)%>
to
<%=rSelect("QuoteID")%>
0
 
John844Commented:
and change
         sqlSelect = "select MAX(QuoteID) from QuoteLines"
to
         sqlSelect = "select MAX(QuoteID) as QuoteID from QuoteLines"
0
 
jitgangulyCommented:
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
 
AlfaNoMoreCommented:
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
 
jitgangulyCommented:
Right alfa. John pointed out first
0
 
AlfaNoMoreCommented:
:-)

Tough scrpa between us all for these points then...
0
 
AlfaNoMoreCommented:
:-)

Tough scrap between us all for these points then...
0
 
donovan_brayAuthor Commented:
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
 
AlfaNoMoreCommented:
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
 
John844Commented:
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
 
AlfaNoMoreCommented:
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
 
AlfaNoMoreCommented:
Ah, no need for the MoveLast if you're only returning one record!!! Ignore my previous comment.
0
 
donovan_brayAuthor Commented:
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

  • 7
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now