By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,144 Members | 972 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,144 IT Pros & Developers. It's quick & easy.

Display last recordset

P: n/a
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
dm*******@ku.edu
http://www.people.ku.edu/~dmatthew/
Jul 19 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Just make another query that returns it:

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

"Dan Matthews" <dm*******@ku.edu> wrote in message
news:ee************************@posting.google.com ...
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
dm*******@ku.edu
http://www.people.ku.edu/~dmatthew/

Jul 19 '05 #2

P: n/a
"Dan Matthews" wrote ...

[..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
Jul 19 '05 #3

P: n/a
> 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.

"Dan Matthews" <dm*******@ku.edu> wrote in message
news:ee************************@posting.google.com ... 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
dm*******@ku.edu
http://www.people.ku.edu/~dmatthew/

Jul 19 '05 #4

P: n/a
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" <dm*******@ku.edu> wrote in message
news:ee************************@posting.google.com ...
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
dm*******@ku.edu
http://www.people.ku.edu/~dmatthew/

Jul 19 '05 #5

P: n/a
"Foo Man Chew" <fo*@man.chew> wrote in message news:<us**************@TK2MSFTNGP09.phx.gbl>...
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.
Jul 19 '05 #6

P: n/a
> "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).

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
Jul 19 '05 #7

P: n/a
Dan Matthews wrote:
"Foo Man Chew" <fo*@man.chew> wrote in message
news:<us**************@TK2MSFTNGP09.phx.gbl>...
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

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 19 '05 #8

P: n/a
> 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...
Jul 19 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.