Washcare
asked on
SELECT Last Record from an online Access Database
Good Morning
I would like the following code to only return the last record in the database file. It currently displays each record to the last record. If I remove the loop it just returns the first record. My understanding is limited, but I think I need to change the SELECT to something like:
strSQL = "SELECT tblComments.S1, tblComments.S2, tblComments.S3, tblComments.Date, tblComments.EN, tblComments.CW, tblComments.Status, tblComments.SSO FROM tblComments WHERE ID_no = SELECT MAX lngRecordNo FROM tblComments"
Many thanks
I would like the following code to only return the last record in the database file. It currently displays each record to the last record. If I remove the loop it just returns the first record. My understanding is limited, but I think I need to change the SELECT to something like:
strSQL = "SELECT tblComments.S1, tblComments.S2, tblComments.S3, tblComments.Date, tblComments.EN, tblComments.CW, tblComments.Status, tblComments.SSO FROM tblComments WHERE ID_no = SELECT MAX lngRecordNo FROM tblComments"
Many thanks
<%
%>
<html>
<head>
<title>Database</title>
</head>
<body bgcolor="white" text="black">
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsdatabase 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query for the database
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("Comments.mdb")
'Set an active connection to the Connection object using DSN connection
'adoCon.Open "DSN=database"
'Create an ADO recordset object
Set rsdatabase = Server.CreateObject("ADODB.Recordset")
'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT tblComments.S1, tblComments.S2, tblComments.S3, tblComments.Date, tblComments.EN, tblComments.CW, tblComments.Status, tblComments.SSO FROM tblComments;"
'Open the recordset with the SQL query
rsdatabase.Open strSQL, adoCon
'Loop through the recordset
Do While not rsdatabase.EOF
'Write the HTML to display the current record in the recordset
Response.Write ("<br>")
Response.Write (rsdatabase("S1"))
Response.Write ("<br>")
Response.Write (rsdatabase("S2"))
Response.Write ("<br>")
Response.Write (rsdatabase("S3"))
Response.Write ("<br>")
Response.Write (rsdatabase("Date"))
Response.Write ("<br>")
Response.Write (rsdatabase("EN"))
Response.Write ("<br>")
Response.Write (rsdatabase("CW"))
Response.Write ("<br>")
Response.Write (rsdatabase("Status"))
Response.Write ("<br>")
Response.Write (rsdatabase("SSO"))
Response.Write ("<br>")
'Move to the next record in the recordset
rsdatabase.MoveNext
Loop
'Reset server objects
rsdatabase.Close
Set rsdatabase = Nothing
Set adoCon = Nothing
%>
</body>
</html>
"SELECT LAST tblComments.S1, tblComments.S2, tblComments.S3, tblComments.Date, tblComments.EN, tblComments.CW, tblComments.Status, tblComments.SSO FROM tblComments
ASKER
Mayankagarwal
I have just tried the above and I get a 500 - Internal server error, message?
Many thanks
I have just tried the above and I get a 500 - Internal server error, message?
Many thanks
If you turn off "Show Friendly HTTP Error Messages" in your browser advanced settings then it should show you the underlying error. Most likely it is a simple syntax error somewhere.
ASKER
Carl
I have just tried the above and get the same error message. I am sure its linked to changing the SELECT to include LAST.
Regards
I have just tried the above and get the same error message. I am sure its linked to changing the SELECT to include LAST.
Regards
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great Many thanks