Solved

SQL Select last record in DB

Posted on 2001-08-30
17
636 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
  • 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
 
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

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 information …
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

707 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now