Display last recordset

[Dan Matthews]

Hey everybody,

I have an 2000 Access database that stores job listings from potential
employers for a school. I would like to be able to display the date
on a webpage the last time the database was updated (inputed through
asp natch'). The database is not updated everyday so that won't work
but something like the database was updated on Monday 11/24/2003, I
would want it to say "New job listings as of Monday, 11/24/2003" if it
were checked on Tuesday or whenever.

The database uses an "Entry Date" column for every job entered so I
figure that would be the key for this function (unless there is
something better to use).

Currently I have:

<%
Dim Connect, Jobs
Set Connect=Server.CreateObject("ADODB.Connection")
Connect.Open "careers1"
Set Jobs = Connect.Execute (_
"SELECT * FROM [JOB LIST] WHERE (([JOB LIST].[Entry Date])=Date())")

do until Jobs.EOF
%>

<p class="text"><font color="red">New job listings as of
<%=Jobs("Entry Date")%></font>


<% Jobs.MoveNext
loop %>

but that only displays new jobs if they have been entered on the
current day. I need to have the date of the last job entered to be
viewed. Any help would be appreciated.

If you have any questions, please ask.

Dan Matthews, MCP
Technology Manager
Green Hall, University of Kansas
(785) 864-9267
(e-mail address removed)
http://www.people.ku.edu/~dmatthew/

 

[Rob Meade]

...

[..snip..]

I read that a couple of times but still couldn't quite work out what you
wanted to do...so here goes at me guessing...

If you want to return just the lastest date your database was updated - why
not something like this :

SELECT MAX(EntryDate) AS LastDate FROM JobsList

That should return the most recent date of the items added.

Hope this helps,

Regards

Rob

 

[Randy Rahbar]

Set Jobs = Connect.Execute (_

"SELECT * FROM [JOB LIST] WHERE (([JOB LIST].[Entry Date])=Date())")

What about sorting it by date desc?

SELECT TOP 1 [Entry Date] FROM [JOB LIST] ORDER BY [Entry Date] DESC

This should only return the last record

PS... I'm not sure it's a good idea to include spaces in your column and
table names.

Hey everybody,

I have an 2000 Access database that stores job listings from potential
employers for a school. I would like to be able to display the date
on a webpage the last time the database was updated (inputed through
asp natch'). The database is not updated everyday so that won't work
but something like the database was updated on Monday 11/24/2003, I
would want it to say "New job listings as of Monday, 11/24/2003" if it
were checked on Tuesday or whenever.

The database uses an "Entry Date" column for every job entered so I
figure that would be the key for this function (unless there is
something better to use).

Currently I have:

<%
Dim Connect, Jobs
Set Connect=Server.CreateObject("ADODB.Connection")
Connect.Open "careers1"
Set Jobs = Connect.Execute (_
"SELECT * FROM [JOB LIST] WHERE (([JOB LIST].[Entry Date])=Date())")

do until Jobs.EOF
%>

<p class="text"><font color="red">New job listings as of
<%=Jobs("Entry Date")%></font>


<% Jobs.MoveNext
loop %>

but that only displays new jobs if they have been entered on the
current day. I need to have the date of the last job entered to be
viewed. Any help would be appreciated.

If you have any questions, please ask.

Dan Matthews, MCP
Technology Manager
Green Hall, University of Kansas
(785) 864-9267
(e-mail address removed)
http://www.people.ku.edu/~dmatthew/

 

[Foo Man Chew]

How about using an aggregate function???

SELECT MAX([Entry Date]) FROM [Job List]

Adjust the query accordingly when you fix your poorly-named objects.

 

[Dan Matthews]

How about using an aggregate function???

SELECT MAX([Entry Date]) FROM [Job List]

Adjust the query accordingly when you fix your poorly-named objects.

"Poorly-named objects"?!? No reason to get personal! Their names are
arbitrary, what difference does it make?!?

Well I tried eveyone's suggestion and no joy. I'm thinking I did not
explain the problem correctly.

I wish to display the last record from a database on a web page. It
would display just the last record and none other. Say Joe Blow
enters a record on Monday, December 1, 2003 and the webpage would say
"Database updated as of Monday, December 1, 2003". No other updates
are made to the database afterwards. Tuesday, December 2, 2003, Joe
checks the web page and it still says "Database updated as of Monday,
December 1, 2003".

That's what I'm trying to do.

 

[Aaron Bertrand - MVP]

"Poorly-named objects"?!? No reason to get personal!

Why do you think the comment was personal? Object names should not have
spaces in them. It can be problematic for certain database operations, and
requiring those square braces sure makes code harder to read. I can't see
the entire conversation, not sure if it's because it happened too long ago,
or if you purposely started a new thread and snipped the rest of the
conversation.

I wish to display the last record from a database on a web page. It
would display just the last record and none other. Say Joe Blow
enters a record on Monday, December 1, 2003 and the webpage would say
"Database updated as of Monday, December 1, 2003". No other updates
are made to the database afterwards. Tuesday, December 2, 2003, Joe
checks the web page and it still says "Database updated as of Monday,
December 1, 2003".

I think the concept you're having difficulty with here is "last." There is
no such thing as the "last row entered" in a database (the database doesn't
track this for you). You can do so using a column with a default value
(assuming SQL Server; I have no idea what database you are using because,
again, the rest of the conversation is not here). And if you want to know
when the last update was, you can use a separate column and keep it updated
with a trigger (or in the stored procedure, if you control data manipulation
that way and don't allow ad hoc updates/inserts).

 

[Bob Barrows]

How about using an aggregate function???

SELECT MAX([Entry Date]) FROM [Job List]

Adjust the query accordingly when you fix your poorly-named objects.

"Poorly-named objects"?!? No reason to get personal! Their names are
arbitrary, what difference does it make?!?

He's referring to the fact that your names include spaces. Just because the
database tool allows this (the use of non-standard characters in object
names) does not mean this should be done. Using non-standard names means you
need to adjust your code to handle those names (note the use of brackets []
in the above query. The brackets would not be needed if the names only
included the standard characters).

Well I tried eveyone's suggestion and no joy. I'm thinking I did not
explain the problem correctly.

I wish to display the last record from a database on a web page. It
would display just the last record and none other. Say Joe Blow
enters a record on Monday, December 1, 2003 and the webpage would say
"Database updated as of Monday, December 1, 2003". No other updates
are made to the database afterwards. Tuesday, December 2, 2003, Joe
checks the web page and it still says "Database updated as of Monday,
December 1, 2003".

That's what I'm trying to do.

I think this was pretty obvious, based on the prior responses, all of which
are valid solutions.

I think you need to tell us what "no joy" means. Error messages? Wrong
results? No results?

Give us a better idea of what your table structure is: datatypes, column
names etc. For example, is there a column identifying who entered the
record?

It's always helpful to show us some sample data in tabular format, along
with the results you wish your query to return - also in tabular format.

Bob Barrows

 

[Dan Matthews]

He's referring to the fact that your names include spaces. Just because the

database tool allows this (the use of non-standard characters in object
names) does not mean this should be done. Using non-standard names means you
need to adjust your code to handle those names (note the use of brackets []
in the above query. The brackets would not be needed if the names only
included the standard characters).

Yeah, I know it contains spaces. My excuse is that I didn't write
it... ;-)

I think you need to tell us what "no joy" means. Error messages? Wrong
results? No results?

Yes, yes and yes! The select(max) seems to work the best but instead
of giving me the date for the last entry, it gives me all of the dates
for all of the last entries. I.e, if on Monday three entries were
made, the web page gives me all three entries. I just want the last
entry made, period.

Give us a better idea of what your table structure is: datatypes, column
names etc. For example, is there a column identifying who entered the
record?

MS Access on a Windows 2000 server running IIS with .asp pages. I'm
using a column titled "Entered Date" for the date that job info was
entered by an employer. I'm trying to get the last entry made by the
"Entered Date".

It's always helpful to show us some sample data in tabular format, along
with the results you wish your query to return - also in tabular format.

Bob Barrows

Sorry, I thought I was being detailed enough...