New record every week?

[Jen]

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

 

[Mike Brind]

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.

 

[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.

 

[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

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.

 

[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?

 

[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

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]

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.

 

[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.

 

[Bobbo]

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.

 

[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...

 

[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

 

[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]]

See Monkey Pi's response.

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.

 

[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")
%>

 

[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]

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

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