• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 662
  • 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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

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