New record every week?

[Jen]

Is it possible (how) to display a new record every week (or day) from a
recordset?

[Mike Brind]

Jen wrote:

Is it possible (how) to display a new record every week (or day) from a
recordset?

Yes. There are a variety of ways that this can be accomplished. If
you describe your requirement in more detail, it will help us narrow
down the options.

--
Mike Brind

[Jen]

Sorry for giving such bad description.
I have a very simple recordset that fetches data from a similarly very
simple table. The table is named "Medlemsformaner", and has only two
columns: 1: Id (int), 2: Ledare (ntext). The recordset fetches the text in
"Ledare" and presents them like this:

<%sql="select * from Medlemsformaner order by Id desc"%>
<!--#include virtual="/databas/connection.inc" -->
<%do until rst.eof =true%>
<%=rst("Ledare")%><br>
<br>
<%rst.movenext
loop%>

The database is sql server 2003.

Jen.
"Mike Brind" <pa*******@hotmail.comwrote in message
news:11**********************@75g2000cwc.googlegro ups.com...

>
Jen wrote:

>Is it possible (how) to display a new record every week (or day) from a
recordset?

Yes. There are a variety of ways that this can be accomplished. If
you describe your requirement in more detail, it will help us narrow
down the options.

--
Mike Brind

[Jen]

And still for clearance; these would be shown onlu for a couple of lines
length. On top of this I would need to show one whole record (text in
"formaner") every week so that it would automatically show a different
record every week.

Jen

"Jen" <je*@hoganmail.comwrote in message
news:e6**************@TK2MSFTNGP04.phx.gbl...

Sorry for giving such bad description.
I have a very simple recordset that fetches data from a similarly very
simple table. The table is named "Medlemsformaner", and has only two
columns: 1: Id (int), 2: Ledare (ntext). The recordset fetches the text in
"Ledare" and presents them like this:

<%sql="select * from Medlemsformaner order by Id desc"%>
<!--#include virtual="/databas/connection.inc" -->
<%do until rst.eof =true%>
<%=rst("Ledare")%><br>
<br>
<%rst.movenext
loop%>

The database is sql server 2003.

Jen.
"Mike Brind" <pa*******@hotmail.comwrote in message
news:11**********************@75g2000cwc.googlegro ups.com...

>>
Jen wrote:

>>Is it possible (how) to display a new record every week (or day) from a
recordset?

Yes. There are a variety of ways that this can be accomplished. If
you describe your requirement in more detail, it will help us narrow
down the options.

--
Mike Brind

[Bobbo]

Jen wrote:

<stuff>

Looks like you're better off asking in a SQL Server group. From what
you've posted so far, it appears that your problem can be solved by
GROUPing appropriately.

[Jen]

Grouping, -how?

"Bobbo" <ro******@gmail.comwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...

>
Jen wrote:

<stuff>

Looks like you're better off asking in a SQL Server group. From what
you've posted so far, it appears that your problem can be solved by
GROUPing appropriately.

[Mike Brind]

So you only want to select ONE record for display? Why you are using
SELECT * then? If you have 52 (or is it 53?) records (one for each
week) you can add an int column to hold the week number against each
record, then select the one that matches the current week number

<%
weekNo = DatePart("ww", Now)
sql = "SELECT Ledare FROM Medlemsformaner WHERE WeekNoField = " &
WeekNo
Set rs = conn.execute(sql)
Response.Write rs("Ledare")
rs.Close : Set rs = Nothing
%>

This would be the easiest way as you are guaranteed a unique record for
each week of the year.

--
Mike Brind

Jen wrote:

And still for clearance; these would be shown onlu for a couple of lines
length. On top of this I would need to show one whole record (text in
"formaner") every week so that it would automatically show a different
record every week.

Jen

"Jen" <je*@hoganmail.comwrote in message
news:e6**************@TK2MSFTNGP04.phx.gbl...

Sorry for giving such bad description.
I have a very simple recordset that fetches data from a similarly very
simple table. The table is named "Medlemsformaner", and has only two
columns: 1: Id (int), 2: Ledare (ntext). The recordset fetches the text in
"Ledare" and presents them like this:

<%sql="select * from Medlemsformaner order by Id desc"%>
<!--#include virtual="/databas/connection.inc" -->
<%do until rst.eof =true%>
<%=rst("Ledare")%><br>
<br>
<%rst.movenext
loop%>

The database is sql server 2003.

Jen.
"Mike Brind" <pa*******@hotmail.comwrote in message
news:11**********************@75g2000cwc.googlegro ups.com...

>
Jen wrote:
Is it possible (how) to display a new record every week (or day) from a
recordset?

Yes. There are a variety of ways that this can be accomplished. If
you describe your requirement in more detail, it will help us narrow
down the options.

--
Mike Brind

[Jen]

No, I don't have 52 (or 53) records. I don't even know the amount of records
that the table will hold. It should simply cykle through the records,
showing a new record every week. If there are 20 records then the cykle
should start over after 20 weeks.

Jen.

"Mike Brind" <pa*******@hotmail.comwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...

So you only want to select ONE record for display? Why you are using
SELECT * then? If you have 52 (or is it 53?) records (one for each
week) you can add an int column to hold the week number against each
record, then select the one that matches the current week number

<%
weekNo = DatePart("ww", Now)
sql = "SELECT Ledare FROM Medlemsformaner WHERE WeekNoField = " &
WeekNo
Set rs = conn.execute(sql)
Response.Write rs("Ledare")
rs.Close : Set rs = Nothing
%>

This would be the easiest way as you are guaranteed a unique record for
each week of the year.

--
Mike Brind

[Bobbo]

Jen wrote:

If there are 20 records then the cykle
should start over after 20 weeks.

I misunderstood, sorry! I thought your aim was one record per unit
(day/week/month) of data. Perhaps I should take some tips from the
'learning to read' newsgroup?

It sounds more like you're doing a Story of the Week type thing.

Maybe you should consider a scheduled script which moves an indicator
through a table of records? In your case of 20 records, you might have
a scheduled script which runs at, say, midnight on Monday morning, that
changes the 'used' flag for the current row. In week one, no records
would have the 'used' flag set. In week three, the first two records
would have the flag set.

This involves adding a bit column to the table and selecting only rows
which have the flag clear (0), with TOP 1 and ORDER clauses to restrict
the dataset accordingly. At the end of the cycle (i.e. no rows
returned) you'd need to clear all the flags.

[Bob Barrows [MVP]]

As Mike says, you need a way to identify the record that needs to be
displayed. Add a column to your table to store an identifier. Perhaps a week
number, or perhaps the first date the record needs to be displayed. Then use
a WHERE clause in your query to retrieve only the appropriate record.

Jen wrote:

No, I don't have 52 (or 53) records. I don't even know the amount of
records that the table will hold. It should simply cykle through the
records, showing a new record every week. If there are 20 records
then the cykle should start over after 20 weeks.

Jen.

"Mike Brind" <pa*******@hotmail.comwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...

>So you only want to select ONE record for display? Why you are using
SELECT * then? If you have 52 (or is it 53?) records (one for each
week) you can add an int column to hold the week number against each
record, then select the one that matches the current week number

<%
weekNo = DatePart("ww", Now)
sql = "SELECT Ledare FROM Medlemsformaner WHERE WeekNoField = " &
WeekNo
Set rs = conn.execute(sql)
Response.Write rs("Ledare")
rs.Close : Set rs = Nothing
%>

This would be the easiest way as you are guaranteed a unique record
for each week of the year.

--
Mike Brind

--
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"

[Mike Brind]

In that case add a boolean field as a flag. Select the top 1 record
that isn't flagged and display it for the week. Store it's ID number
and the week number in Application Variables. When the week number
changes, update the record so that it's flagged as used, and select the
top 1 record that isn't flagged... etc. You would also need to add
some logic so that if all records have been flagged, you can reset the
flags on all but the one that was used last week.

Others may have alternative suggestions...

--
Mike Brind

Jen wrote:

No, I don't have 52 (or 53) records. I don't even know the amount of records
that the table will hold. It should simply cykle through the records,
showing a new record every week. If there are 20 records then the cykle
should start over after 20 weeks.

Jen.

"Mike Brind" <pa*******@hotmail.comwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...

So you only want to select ONE record for display? Why you are using
SELECT * then? If you have 52 (or is it 53?) records (one for each
week) you can add an int column to hold the week number against each
record, then select the one that matches the current week number

<%
weekNo = DatePart("ww", Now)
sql = "SELECT Ledare FROM Medlemsformaner WHERE WeekNoField = " &
WeekNo
Set rs = conn.execute(sql)
Response.Write rs("Ledare")
rs.Close : Set rs = Nothing
%>

This would be the easiest way as you are guaranteed a unique record for
each week of the year.

--
Mike Brind

[Jen]

Thanks all for the fine suggestions, I really appretiate them (& you).
Darn, this was more complicated than I thought (shouldn't been bragging for
my chief that I can do this..).

I was thinking of some simple if-then clause that kind of checked that if
its sunday and if this is the first request of this site today then select
yada yada where id is = some new counterfield in the table (or even some
textfile) +1 and at the same time increment this counterfield (or textfile)
with 1, and hey, if this is not possible (came to the end of the forinstance
20 records) then start over and select top 1 yada yada..

It isn't fun beeing dumb in coding :(
"Mike Brind" <pa*******@hotmail.comwrote in message
news:11**********************@m79g2000cwm.googlegr oups.com...

In that case add a boolean field as a flag. Select the top 1 record
that isn't flagged and display it for the week. Store it's ID number
and the week number in Application Variables. When the week number
changes, update the record so that it's flagged as used, and select the
top 1 record that isn't flagged... etc. You would also need to add
some logic so that if all records have been flagged, you can reset the
flags on all but the one that was used last week.

Others may have alternative suggestions...

--
Mike Brind

[Monkey Pi]

Jen wrote:

Thanks all for the fine suggestions, I really appretiate them (& you).
Darn, this was more complicated than I thought (shouldn't been bragging for
my chief that I can do this..).

I was thinking of some simple if-then clause that kind of checked that if
its sunday and if this is the first request of this site today then select
yada yada where id is = some new counterfield in the table (or even some
textfile) +1 and at the same time increment this counterfield (or textfile)
with 1, and hey, if this is not possible (came to the end of the forinstance
20 records) then start over and select top 1 yada yada..

It isn't fun beeing dumb in coding :(

But what if no user visits on Sunday?
I think this would be best handled server-side. You set up a date
field. You populate the field with dates 1 week apart starting with the
previous Sunday. You set the page to display whichever record has the
most recent date. Every Sunday, you fire off an automated (or manual)
event that updates the oldest record to the current date. Whenever you
add a new record, you just date it 1 week previous to the oldest record.

Workable?

Monkey Pi

[Jen]

This way it could be done, yes, but I'm not the one that's gonna be updating
these in the future. That's why I'm looking for a completely automated
solution, preferrably based on the id-value. The end user shouldn't have to
worry about weeknumbers or similar.

Jen.

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcomwrote in message
news:Om*************@TK2MSFTNGP06.phx.gbl...

As Mike says, you need a way to identify the record that needs to be
displayed. Add a column to your table to store an identifier. Perhaps a
week number, or perhaps the first date the record needs to be displayed.
Then use a WHERE clause in your query to retrieve only the appropriate
record.

[Monkey Pi]

Jen wrote:

This way it could be done, yes, but I'm not the one that's gonna be updating
these in the future. That's why I'm looking for a completely automated
solution, preferrably based on the id-value. The end user shouldn't have to
worry about weeknumbers or similar.

Jen.

The solution I proposed could have the "record of the week" updated by a
process run from windows task scheduler, SQL Server (if you're using
it), or an other automated schedule utility. If you don't want the user
to be involved, you're not going to be the one updating, and you don't
want to automate an update, then you are indeed out of answers.

Monkey Pi

[Bob Barrows [MVP]]

See Monkey Pi's response.

Jen wrote:

This way it could be done, yes, but I'm not the one that's gonna be
updating these in the future. That's why I'm looking for a completely
automated solution, preferrably based on the id-value. The end user
shouldn't have to worry about weeknumbers or similar.

Jen.

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcomwrote in message
news:Om*************@TK2MSFTNGP06.phx.gbl...

>As Mike says, you need a way to identify the record that needs to be
displayed. Add a column to your table to store an identifier.
Perhaps a week number, or perhaps the first date the record needs to
be displayed. Then use a WHERE clause in your query to retrieve only
the appropriate record.

--
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"

[Mike Brind]

I haven't tested this, but it works with 3 application variables.
Application("WeekNo") holds the week number. Application("ID") holds
the current story ID. Application("Ledare") holds the current story.
The logic is fairly straightforward:

If the current week number differs from the app variable (becuase the
app variable is empty or the week number has changed) the database is
checked for any stories that haven't been flagged as used. If there
are any, it takes the first one, and stores the ID, story and week
number in app variables. It then updates the boolean field to true for
the record.

If there are none that haven't been used, it resets the boolean field
for all the records to false, and selects the top one.

Since the text of the story is stored in an application variable, the
database will only ever be queried once a week.

<%
If DatePart("w",Now) <Application("WeekNo") Then
sql = "SELECT TOP 1 ID, Ledare FROM Medlemsformaner " & _
" Where boolField = False"
Set rs = Conn.Execute(sql)
If rs.EOF Then
Conn.Execute("UPDATE MedlamsFormaner SET boolField = False")
sql = "SELECT TOP 1 ID, Ledare FROM Medlemsformaner " & _
" Where boolField = False"
Set rs = Conn.Execute(sql)
End If
Application("ID") = rs("ID")
Application("Ledare") = rs("Ledare")
Application("WeekNo") = DatePart("w",Now)
rs.Close : Set rs = Nothing
sql = "Update Medlemsformaner Set boolField = True WHERE " & _
" ID =" & Application("ID")
Conn.execute(sql)
End If
Response.Write Application("Ledare")
%>

--
Mike Brind
Jen wrote:

Thanks all for the fine suggestions, I really appretiate them (& you).
Darn, this was more complicated than I thought (shouldn't been bragging for
my chief that I can do this..).

I was thinking of some simple if-then clause that kind of checked that if
its sunday and if this is the first request of this site today then select
yada yada where id is = some new counterfield in the table (or even some
textfile) +1 and at the same time increment this counterfield (or textfile)
with 1, and hey, if this is not possible (came to the end of the forinstance
20 records) then start over and select top 1 yada yada..

It isn't fun beeing dumb in coding :(
"Mike Brind" <pa*******@hotmail.comwrote in message
news:11**********************@m79g2000cwm.googlegr oups.com...

In that case add a boolean field as a flag. Select the top 1 record
that isn't flagged and display it for the week. Store it's ID number
and the week number in Application Variables. When the week number
changes, update the record so that it's flagged as used, and select the
top 1 record that isn't flagged... etc. You would also need to add
some logic so that if all records have been flagged, you can reset the
flags on all but the one that was used last week.

Others may have alternative suggestions...

--
Mike Brind

[Jen]

Hi, and thanks. Do I need to make another column ("WeekNo" or "boolField")
in the table?

Now I get:
Microsoft VBScript runtime error '800a01a8'
Object required: ''

/ny_pa_kommande/test.asp, line 16
...where line 16 is the line "Set rs = Conn.Execute(sql)"

I have made those fields too but still get the same "Object required.."
error

Jen

"Mike Brind" <pa*******@hotmail.comwrote in message
news:11**********************@i3g2000cwc.googlegro ups.com...

>I haven't tested this, but it works with 3 application variables.
Application("WeekNo") holds the week number. Application("ID") holds
the current story ID. Application("Ledare") holds the current story.
The logic is fairly straightforward:

If the current week number differs from the app variable (becuase the
app variable is empty or the week number has changed) the database is
checked for any stories that haven't been flagged as used. If there
are any, it takes the first one, and stores the ID, story and week
number in app variables. It then updates the boolean field to true for
the record.

If there are none that haven't been used, it resets the boolean field
for all the records to false, and selects the top one.

Since the text of the story is stored in an application variable, the
database will only ever be queried once a week.

<%
If DatePart("w",Now) <Application("WeekNo") Then
sql = "SELECT TOP 1 ID, Ledare FROM Medlemsformaner " & _
" Where boolField = False"
Set rs = Conn.Execute(sql)
If rs.EOF Then
Conn.Execute("UPDATE MedlamsFormaner SET boolField = False")
sql = "SELECT TOP 1 ID, Ledare FROM Medlemsformaner " & _
" Where boolField = False"
Set rs = Conn.Execute(sql)
End If
Application("ID") = rs("ID")
Application("Ledare") = rs("Ledare")
Application("WeekNo") = DatePart("w",Now)
rs.Close : Set rs = Nothing
sql = "Update Medlemsformaner Set boolField = True WHERE " & _
" ID =" & Application("ID")
Conn.execute(sql)
End If
Response.Write Application("Ledare")
%>

--
Mike Brind
Jen wrote:

>Thanks all for the fine suggestions, I really appretiate them (& you).
Darn, this was more complicated than I thought (shouldn't been bragging
for
my chief that I can do this..).

I was thinking of some simple if-then clause that kind of checked that if
its sunday and if this is the first request of this site today then
select
yada yada where id is = some new counterfield in the table (or even some
textfile) +1 and at the same time increment this counterfield (or
textfile)
with 1, and hey, if this is not possible (came to the end of the
forinstance
20 records) then start over and select top 1 yada yada..

It isn't fun beeing dumb in coding :(
"Mike Brind" <pa*******@hotmail.comwrote in message
news:11**********************@m79g2000cwm.googleg roups.com...

In that case add a boolean field as a flag. Select the top 1 record
that isn't flagged and display it for the week. Store it's ID number
and the week number in Application Variables. When the week number
changes, update the record so that it's flagged as used, and select the
top 1 record that isn't flagged... etc. You would also need to add
some logic so that if all records have been flagged, you can reset the
flags on all but the one that was used last week.

Others may have alternative suggestions...

--
Mike Brind

[Mike Brind]

Conn is a generic term for a valid connection object. Replace it with
whatever valid connection object you create and open. If you use your
include file, you will need to change the name of the recordset object
in either the include file or the code so they match, and then you can
leave Conn.Execute(sql) out of the code, as I guess that is already in
your include file..

You don't need WeekNo - that was a suggestion if you had one record for
every week. boolField can be called anything you like, but must be
created as a boolean/YesNo field in the table containing the records.

--
Mike Brind

Jen wrote:

Hi, and thanks. Do I need to make another column ("WeekNo" or "boolField")
in the table?

Now I get:
Microsoft VBScript runtime error '800a01a8'
Object required: ''

/ny_pa_kommande/test.asp, line 16
..where line 16 is the line "Set rs = Conn.Execute(sql)"

I have made those fields too but still get the same "Object required.."
error

Jen

"Mike Brind" <pa*******@hotmail.comwrote in message
news:11**********************@i3g2000cwc.googlegro ups.com...

I haven't tested this, but it works with 3 application variables.
Application("WeekNo") holds the week number. Application("ID") holds
the current story ID. Application("Ledare") holds the current story.
The logic is fairly straightforward:

If the current week number differs from the app variable (becuase the
app variable is empty or the week number has changed) the database is
checked for any stories that haven't been flagged as used. If there
are any, it takes the first one, and stores the ID, story and week
number in app variables. It then updates the boolean field to true for
the record.

If there are none that haven't been used, it resets the boolean field
for all the records to false, and selects the top one.

Since the text of the story is stored in an application variable, the
database will only ever be queried once a week.

<%
If DatePart("w",Now) <Application("WeekNo") Then
sql = "SELECT TOP 1 ID, Ledare FROM Medlemsformaner " & _
" Where boolField = False"
Set rs = Conn.Execute(sql)
If rs.EOF Then
Conn.Execute("UPDATE MedlamsFormaner SET boolField = False")
sql = "SELECT TOP 1 ID, Ledare FROM Medlemsformaner " & _
" Where boolField = False"
Set rs = Conn.Execute(sql)
End If
Application("ID") = rs("ID")
Application("Ledare") = rs("Ledare")
Application("WeekNo") = DatePart("w",Now)
rs.Close : Set rs = Nothing
sql = "Update Medlemsformaner Set boolField = True WHERE " & _
" ID =" & Application("ID")
Conn.execute(sql)
End If
Response.Write Application("Ledare")
%>

--
Mike Brind
Jen wrote:

Thanks all for the fine suggestions, I really appretiate them (& you).
Darn, this was more complicated than I thought (shouldn't been bragging
for
my chief that I can do this..).

I was thinking of some simple if-then clause that kind of checked that if
its sunday and if this is the first request of this site today then
select
yada yada where id is = some new counterfield in the table (or even some
textfile) +1 and at the same time increment this counterfield (or
textfile)
with 1, and hey, if this is not possible (came to the end of the
forinstance
20 records) then start over and select top 1 yada yada..

It isn't fun beeing dumb in coding :(
"Mike Brind" <pa*******@hotmail.comwrote in message
news:11**********************@m79g2000cwm.googlegr oups.com...
In that case add a boolean field as a flag. Select the top 1 record
that isn't flagged and display it for the week. Store it's ID number
and the week number in Application Variables. When the week number
changes, update the record so that it's flagged as used, and select the
top 1 record that isn't flagged... etc. You would also need to add
some logic so that if all records have been flagged, you can reset the
flags on all but the one that was used last week.

Others may have alternative suggestions...

--
Mike Brind