How do i show last database record entered?

[bellend]

Hi guys, Just a quick question. I have a bit of code that loops through all the recordsets in the database and prints them onto a page. See code below. How do i modify the code to just print the last recordset entered? Thanks in advance!!!

'Looping through the records until the end of the records
Do while Not recordset.eof
Response.Write "Ticket : " & recordset("id") & "<br>"
Response.Write "Name : " & recordset("name") & "<br>"
Response.Write "Email : " & recordset("email") & "<br>"
Response.Write "mobile : " & recordset("mobile") & "<br><br>"
Response.Write "Pixels : " & recordset("pixel") & "<br><br>"
'move on to the next record
recordset.MoveNext
loop

 

[MoLaker]

Not a good idea - at least not something you should make a habit of. Yes, with MS Access, you can usually get the last row of data if you do not have a order by in your SQL. But, there are things that could change that. If the last record entered is important to you, you would be much better off to date/time stamp the record when it is created then order by the date/time stamp.

That said, you can go to the last record in the recordset (NOT necessarily the last record entered) by following your do/loop with recordset.movelast.

'Looping through the records until the end of the records
Do while Not recordset.eof
Response.Write "Ticket : " & recordset("id") & "<br>"
Response.Write "Name : " & recordset("name") & "<br>"
Response.Write "Email : " & recordset("email") & "<br>"
Response.Write "mobile : " & recordset("mobile") & "<br><br>"
Response.Write "Pixels : " & recordset("pixel") & "<br><br>"
'move on to the next record
recordset.MoveNext
loop
recordset.movelast

Then print

 

[ChrisHirst]

recordset.movelast

however that will be the last record according to the sort order of record retrieval, NOT necessarily the last one entered.



Chris.

Indifference will be the downfall of mankind, but who cares?

http://www.candsdesign.co.uk

A website that proves the cobblers kids adage.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[bellend]

I have put the recordset. movelast in and i get the error Error Type:
Microsoft JET Database Engine (0x80040E24)
Rowset does not support fetching backward.
/form/add_to_database.asp, line 77


Here is the rest of my code if it will make any sense! cheers guys

<%
'declare your variables
Dim name, email, mobile, card, pixel
Dim sConnString, connection, sSQL, recordset

' Receiving values from Form, assign the values entered to variables
name = Request.Form("name")
email = Request.Form("email")
mobile =Request.Form("mobile")
card =Request.Form("card")
pixel =Request.Form("pixel")


'declare SQL statement that will query the database
sSQL = "INSERT into users_tbl (name, email, mobile, card, pixel) values ('" & _
name & "', '" & email & "', '" & mobile & "', '" & card & "', '" & pixel & "')"

'define the connection string, specify database
'driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("Users.mdb")


'create an ADO connection object
Set connection = Server.CreateObject("ADODB.Connection")

'Open the connection to the database
connection.Open(sConnString)

'execute the SQL
connection.execute(sSQL)

response.write ""

' Done. Close the connection object
connection.Close
Set connection = Nothing
%>
<%


'declare SQL statement that will query the database
sSQL="SELECT * FROM Users_tbl"

'create an ADO connection and recordset object
Set connection = Server.CreateObject("ADODB.connection")
Set recordset = Server.CreateObject("ADODB.Recordset")

'define the connection string, specify database
'driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("Users.mdb")

'Open the connection to the database
Connection.Open sConnString

'Open the recordset object, executing the SQL
Recordset.Open sSQL, Connection

'Looping through the records until the end of the records
Do while Not recordset.eof
Response.Write "Ticket : " & recordset("id") & "<br>"
Response.Write "Name : " & recordset("name") & "<br>"
Response.Write "Email : " & recordset("email") & "<br>"
Response.Write "mobile : " & recordset("mobile") & "<br><br>"
Response.Write "Pixels : " & recordset("pixel") & "<br><br>"
'move on to the next record
recordset.MoveNext
loop
recordset.movelast

'Now close the recordset and the connection object
recordset.Close
Set recordset = Nothing
connection.Close
Set connection = Nothing
%>

 

[ChrisHirst]

as it is Access you will probably need to use a client-side cursor, which certainly won't improve performance.

recordset.cursorlocation = adUseClient (value = 3 if you are not using the constants file.)


you will need to set this before recordset.open.





Chris.

Indifference will be the downfall of mankind, but who cares?

http://www.candsdesign.co.uk

A website that proves the cobblers kids adage.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[MoLaker]

The default cursor type is adOpenForwardOnly. It can't back up. Take a look here...

http://www.w3schools.com/ado/met_rs_open.asp

 

[bellend]

Sorry to be a thicko, but how would i set the value of the aduseclient if the value is 3.
What would the code be?

 

[ChrisHirst]

if you have the adovbs.inc file as an include, the code will be

recordset.cursorlocation = adUseClient

if not

recordset.cursorlocation = 3



Chris.

Indifference will be the downfall of mankind, but who cares?

http://www.candsdesign.co.uk

A website that proves the cobblers kids adage.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[snowboardr]

Well what db are you using?

If mysql you can do this:

Select column_nameID FROM table_name ORDER by column_nameID DESC LIMIT 1

www.sitesd.com
ASP WEB DEVELOPMENT

 

[ChrisHirst]

snowboardr,

this might be a bit of a give away

sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("Users.mdb")

Chris.

Indifference will be the downfall of mankind, but who cares?

http://www.candsdesign.co.uk

A website that proves the cobblers kids adage.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[snowboardr]

Yes, well in a perfect world i would have time to read every post and people would upgrade there database from Access to MySQL, and save the world and them alot of trouble.

Access sucks.

www.sitesd.com
ASP WEB DEVELOPMENT

 

[ChrisHirst]

Yes in a perfect world every host would offer MySQL on their servers or at least install the ODBC drivers to allow use of remote servers.
However this is far from that and many people have to work with what they have.

Access is a viable option for small Db solutions on low traffic sites and something that many programmers/web developers have started on.



Chris.

Indifference will be the downfall of mankind, but who cares?

http://www.candsdesign.co.uk

A website that proves the cobblers kids adage.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[snowboardr]

Access can be corrupt though, why would you want to risk your business' data on that chance?

www.sitesd.com
ASP WEB DEVELOPMENT

 

[ChrisHirst]

I do agree btw,
Access is not something to have mission critical data on, but a business probably wouldn't (shouldn't) be using the same level of hosting as someone with a hobby/testing site.



Chris.

Indifference will be the downfall of mankind, but who cares?

http://www.candsdesign.co.uk

A website that proves the cobblers kids adage.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[bellend]

Have implemented that but it still prints all the recordsets out rather than the last one. Nay other ideas? appreciate your help guys

recordset.cursorlocation = 3


'Open the recordset object, executing the SQL
Recordset.Open sSQL, Connection

'Looping through the records until the end of the records
Do while Not recordset.eof
Response.Write "Ticket : " & recordset("id") & "<br>"
Response.Write "Name : " & recordset("name") & "<br>"
Response.Write "Email : " & recordset("email") & "<br>"
Response.Write "mobile : " & recordset("mobile") & "<br><br>"
Response.Write "Pixels : " & recordset("pixel") & "<br><br>"
'move on to the next record
recordset.MoveNext
loop
recordset.movelast

 

[ChrisHirst]

well, maybe if you took out the code that loops around your recordset and put the movelast before the response.writes it would stop doing that.



Chris.

Indifference will be the downfall of mankind, but who cares?

http://www.candsdesign.co.uk

A website that proves the cobblers kids adage.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[spod]

I'm assuming the ticket field you are displaying in

Response.Write "Ticket : " & recordset("id") & "<br>"

is an access autonumber column - it's not part of the insert statement, so must automatically be filled in. Therefore the following sql should work in access to bring back one row, which will be the last entered row to the table

SELECT TOP 1 id, name, email, mobile, card, pixel FROM Users_tbl order by ID desc

 

[Tarwn]

I would agree with spod, thre is no point in bringing down all the records from the database if you only need the last one. If the column is not an autoid you could always add a date/time column and set the default value to Now(). That way it would timestamp every record that as entered (provided you didn't manually add a date). Then to get the last one you would pull them out similar to spods method:

SELECT TOP 1 id, name, email, mobile, card, pixel FROM Users_tbl ORDER BY YourDateField DESC

Though you may also want to put some squarebrackets around those field names, think some of them may be reserved words.


Another option, if your looking for more options, would be to simply build a select based on the fields you just inserted.

-T

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
Help, the rampaging, spear-waving, rabid network gnomes are after me!