Display image based on week day

[RedfordGeeks]

In my MSSQL db I have the following fields that are based on 'True' or 'False'

We'll call these our displayAd fields the actual field names are below:

displayAdMonday

displayAdTuesday

diplayAdWednesday

and so on through Sunday.

In my page I want the application to check which day of the week it is and display the records that have the displayAd fields set to true. I'm working in ASP / VB Script

Please help 🙂

[bregent]

Use the datepart VBScript function passing it the interval type "w" for weekday:

response.write (DatePart("w",Now()))

You can finding a listing of VBScript functions everywhere. Here's one you can use for reference:

http://www.w3schools.com/vbscript/default.asp

[RedfordGeeks]

Bregent -

I was hoping for a little more help with the actual code. I have the recordset in my page rsLeaderboard and based on the SQL query my ads are displaying according to the criteria. However, I need help with the inline code for only the ads that have the adDay fields set to true to display based on the weekday name. I know enough VBScript to tweak things but not enough to write my own. Can you help?

Thank you.

[bregent]

To be of further help I would need to know more about the database design and what your specific requirements are.

[RedfordGeeks]

Bregent -

Here is what I have:

MSSQL db - ASp / VBScript

rsLeaderboard query:

<%

Dim rsLeaderboard

Dim rsLeaderboard_cmd

Dim rsLeaderboard_numRows

Set rsLeaderboard_cmd = Server.CreateObject ("ADODB.Command")

rsLeaderboard_cmd.ActiveConnection = MM_DB_f2e8aa_STRING

rsLeaderboard_cmd.CommandText = "SELECT * FROM dbo.adInfo t1 JOIN dbo.advertisers t2 ON t1.company_username = t2.company_username WHERE ad_level = 1 AND ad_date_expire >= GETDATE() AND open_ar = 'False' AND adStatusLeaderboard = 'Active'"

rsLeaderboard_cmd.Prepared = true

Set rsLeaderboard = rsLeaderboard_cmd.Execute

rsLeaderboard_numRows = 0

%>

Inline code(within asp page)for diplaying ad:

<%

If (rsLeaderboard.Fields.Item("leaderboard").Value) <> "" Then

Response.Write("!|height=75|width=600|src=!")

Else

Response.Write("Advertise in this space contact info@laurascard.com") End if

%>

Within my database in the adInfo table I have the following displayAd fields based on a true or false value:

displayAdMonday

displayAdTuesday

displayAdWednesday

displayAdThursday

and so on through Sunday

I want the leaderboard ads that have a "true" value for the weekday only to display based on the other criteria within the query as well. So if a advertiser has a Leaderboard image to display on only Monday and Friday the application (or page) will check which day it is and return only the Leaderboard images that have a true value based on the weekday (diplayAd field)

[bregent]

Based on your criteria, I'd say you have a 1NF database design flaw. You should remove the displayAdMonday... fields from the ad table and create a new table that contains a row for each weekday for the ad. Otherwise you will either need to:

1) Pull all of the data as you are now doing and evaluate whether or not the ad is appropriate for the current day. This is ugly because you need to evaulate 7 different fields depending on which day it is. You are also bringing back lots of data (6 fields) that you will not be using.  or,

2) Create 7 different queries - one for each day of the week - and execute the correct one depending on the day. Also ugly. You could get creative and dynamically build the where clause - selecting the appropriate column to use based on the current day.

I don't like either of these methods. The correct way is to properly normalize the database and remove the repeating groups.

[RedfordGeeks]

Ok so I know an ID 10 T error is idiot, what's 1NF? LOL Sorry, I know discussions are for problems just couldn't help wondering

[RedfordGeeks]

Bregent -

Back to a real question:

You could get creative and dynamically build the where clause - I know the snippet of code is going to be lengthy but I think this is the direction I need to take being that:

The advertisers have certian functions they can perform in the DB like uploading the images for banner ads, leaderboard ads, skyscraper ads and text ads. They can also set the active date and expiration date as well as if the ad is for Today Only. However the administrators (owners) of the company have control over the other fields depending on the advertising contract the advertiser has signed. The administrators will go in and set the days for the ads to display as well as if the advertiser is active, inactive or if their account is over due. I thought keeping all the "adInfo" in one table would be the best solution for both administrative functions. If I can dynamically create the "check weekday" within the inline code on the page this is what I need to do.

Is there a way I can create a variable if displayAdMonday = "True" then weekdayname("monday")in the head portion of the code and then inline(within the page) have it look at that variable to determine whether or not the ad gets displayed.

<%

If (rsLeaderboard.Fields.Item("leaderboard").Value)& weekdayname("monday") <> "" Then

Response.Write("!|height=75|width=600|src=!")

Else

Response.Write("Advertise in this space contact info@laurascard.com") End if

%>

I understand this variable would have to be set for all 7 fields and the inline code to follow. And like I said I know enough code to tweak but obviously I cannot write it.

Thank you for your patience

[bregent]

>I thought keeping all the "adInfo" in one table would be the

>best solution for both administrative functions

Well it might be the simplest but maybe not the best. But you could try something like this. I don't have time now to verify all of the syntax but it should get you started. First of all, since the VBScript datepart function for weekday returns a number, I would consider changing your weekday column names to something like displayAdDay1, displayAdDay2, etc.  Then:

dim dayColumn

dayColumn = "displayAdDay" &  (DatePart("w",Now()))

......

rsLeaderboard_cmd.CommandText  = "select * from mytable where " & dayColumn & " = True"

Obviously I did not include all of the other criteria in the select statement but you get the idea.