Weekly Dynamic Content?

Results 1 to 6 of 6

Thread: Weekly Dynamic Content?

  1. #1
    Dayv Guest

    Default Weekly Dynamic Content?

    Hello, <BR><BR>Can anyone tell me if there is a way in ASP to Dynamically (not randomly) change the content on a given page every day/week? Isn&#039;t there a date span that asp could read and in turn display the correct text/html/asp if it falls within that date span? <BR><BR>I would like to have certain content changed automatically from a text/HTML file w/o having to manually change it. <BR><BR>Currently I am using an Access DB with the following fields:<BR><BR>QuoteID - AutoNumber <BR>Quote - Memo <BR>Author - Memo <BR>start_date - Date/Time<BR>end_date - Date/Time<BR><BR>For reference, I am including the error encounted and code used.<BR><BR>&#060;snip&#062;<BR>================= =========<BR><BR>Microsoft VBScript compilation error &#039;800a0401&#039; <BR>This is the error that I&#039;m getting: <BR>&#060;snip&#062; <BR>Microsoft JET Database Engine error &#039;80040e14&#039; <BR><BR>Syntax error (missing operator) in query expression &#039;startdate is between #4/20/01# and #4/25/01#&#039;. <BR><BR>/scripts/Projects/pending/dynamic/quotes.asp, line 63 <BR>&#060;/snip&#062; <BR><BR>... and this is the code that I am using: <BR><BR>&#060;snip&#062; <BR>&#060;HTML&#062; <BR>&#060;HEAD&#062; <BR>&#060;TITLE&#062;Weekly Quotes&#060;/TITLE&#062; <BR>&#060;% <BR>Sub GetQuote(strQuote, strAuthor) <BR><BR>Dim intMaxID &#039; Integer to hold highest record identifier <BR>Dim intRecordID &#039; Integer to hold random record identifier <BR>Dim intLoop &#039; Integer used for loop index <BR>Dim strSQL &#039; String to hold Query <BR>Dim strPath &#039; String to hold Database Path <BR>Dim oConn &#039; Object to hold Connection <BR>Dim oRS &#039; Object to hold Recordset <BR><BR><BR>&#039; Get the filename of our MS-Access file... <BR>strPath = Server.mappath(request("script_name")) <BR>For intLoop = Len(strPath) To 1 Step -1 <BR> If Mid(strPath, intLoop, 1) = "\" Then <BR> strPath = Left(strPath, intLoop) & "quotes.mdb" <BR> Exit For <BR> End If <BR>Next <BR><BR>&#039; Create the Connection <BR>Set oConn = Server.CreateObject("ADODB.Connection") <BR>&#039; Open connection using System DSN or as shown using ODBC string <BR>oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath <BR><BR>&#039; Get the highest ID number from the Database. We are assuming that the database <BR>&#039; ID numbers are sequential starting at 1 <BR><BR>strSQL = "SELECT max(QuoteId) as MaxID from Quotes" <BR>Set oRS = oConn.Execute(strSQL) <BR>If oRS.EOF Then <BR> &#039; An Error Occurred so use a static quote that is appropriate <BR> strQuote = "I haven&#039;t failed, I&#039;ve found 10,000 ways that don&#039;t work." <BR> strAuthor = "Thomas Eddison" <BR> Exit Sub <BR>Else <BR> intMaxID = oRS("MaxID") <BR>End If <BR><BR>&#039; Get Random record ID <BR>&#039;Randomize <BR>&#039;intRecordID = Int(Rnd * intMaxID) + 1 <BR><BR><BR>&#039; Select that record from the database <BR>strSQL = "Select * from quotes where QuoteID=" & intRecordID & ";" <BR>strSQL = "SQL = "Select * from quotes where startdate is between #" & start_date & "# and #" & end_date & "#";" <BR><BR><BR>&#039; Get Current Date record ID <BR>start_date = date <BR>end_date = date + 5 <BR><BR>Set oRS = oConn.Execute(strSQL) <BR>If oRS.EOF And oRS.BOF Then <BR> &#039; An Error Occurred so use a static quote that is appropriate <BR> strQuote = "I haven&#039;t failed, I&#039;ve found 10,000 ways that don&#039;t work." <BR> strAuthor = "Thomas Eddison" <BR> Exit Sub <BR>Else <BR> oRS.MoveFirst <BR> strQuote = Trim(oRS("Quote")) <BR> strAuthor = Trim(oRS("Author")) <BR>End If <BR>&#039;Clean up <BR>oRS.Close <BR>oConn.Close <BR>Set oRS = Nothing <BR>Set oConn = Nothing <BR>End Sub <BR>%&#062; <BR><BR>&#060;link rel="stylesheet" href="/css/general.css" type="text/css"&#062; <BR><BR>&#060;/HEAD&#062; <BR><BR>&#060;BODY&#062; <BR><BR>&#060;% &#039;Define variables and call our subroutine <BR> Dim strQuote <BR> Dim strAuthor <BR> GetQuote strQuote, strAuthor <BR>%&#062; <BR> &#060;% =strQuote %&#062; <BR>::&#060;b&#062;&#060;% =strAuthor %&#062;&#060;/b&#062; <BR>&#060;/BODY&#062; <BR>&#060;/HTML&#062; <BR><BR>&#060;/snip&#062;<BR>==========================<BR>--- Any assistance would be most appreciative.<BR><BR>Dayv

  2. #2
    Join Date
    Dec 1969

    Default Try getting rid of 'IS'...

    startdate Between #4/15/2001# AND #4/21/2001#<BR><BR>Have you tried simply making the query in Access, itself, to be sure it works? Not via ASP. Simply using the Access query designer?<BR><BR>And this line can&#039;t POSSIBLY be right (it wouldn&#039;t even compile! so you&#039;d never get as far as the error you are showing):<BR><BR>strSQL = "SQL = "Select * from quotes where startdate is between #" & start_date & "# and #" & end_date & "#";" <BR><BR>So it would be nice if you&#039;d show *real* code. Why do you have the random quote code still in there, if that isn&#039;t what you want? All it does is confuse the issue.<BR><BR><BR>

  3. #3
    Dayv Guest

    Default Ummm...

    Sigh... <BR><BR>I initially used this page as a random quote, but have been asked to have content displayed weekly instead of randomly. I commented out some of my code which was giving me difficulty. But I see your point - I should&#039;ve removed it instead. <BR><BR>I did design the db from scratch so I don&#039;t believe that is the problem, instead I believe it instead does lie in the ASP code.<BR><BR>Any help on what the problem may be would be appreciated. If you really need me to, I&#039;ll remove the comments of my previous attempts to eliviate any confusion.<BR><BR>TIA,<BR>Dayv

  4. #4
    Dayv Guest

    Default RE: Try getting rid of 'IS'...

    Oh yeah, and it should be:<BR><BR>strSQL = "SQL = "Select * from quotes where startdate is between #" & start_date & "# and #" & end_date & "#"<BR><BR>Still no dice...<BR><BR>Any advise?<BR>- Dayv

  5. #5
    Kasper Guest

    Default Still no dice ?

    Did you actually try what Bill suggested? Did you copy and pasted the sqlcode in the query editor of Access?<BR>(filling in start_date and end_date ofcourse)<BR><BR>I most certainly do not understand the first portion : strSQL = "SQL = "Select<BR>What do you want to accomplish with this? To concatenate strings you use ampersand (&). You most certainly will generate an error by putting Select * right behind the closing " of the string strSQL<BR>Why would you even want to put "SQL = " before your query? Ok, I don&#039;t know exactly if it might work, but I know for sure it will work without it.<BR>Put your semicolon back in there, but not as & "#";" but as & "#;"<BR><BR>And finally as Bill already said remove the &#039;is&#039; between startdate and between. Same comment goes here : might work, don&#039;t know, but know for sure it will work if you omit!<BR><BR>Now cast your dice again :)<BR>

  6. #6
    Join Date
    Dec 1969

    Default If you would do as suggested...

    ...you *might* get closer.<BR><BR>As I said: Go try that query in Access, itself. Instead of using ASP.<BR><BR>Guess what: IT WILL NOT WORK!<BR><BR>Now get rid of the word "IS", as I suggested.<BR><BR>Guess what: NOW it works.<BR><BR>Enough said. <BR><BR>Aside, of course, from your 100% bogus strSQL string which I *know* is not what you *actually* used or you&#039;d have gotten a compile error!<BR><BR>strSQL = "SQL = "Select<BR><BR>And right there you are hosed. The " after the = sign ends the first string and what comes after is not a valid string, so...<BR><BR>As for the old code being commented out: NO IT IS NOT! <BR><BR>What are you talking about? AS THAT CODE IS SHOWN, you still have ALL THE CODE to do the random selection still sitting there.<BR><BR>You make it very, very hard for US to debug--and surely just as hard on yourself, as you have clearly evidenced--when you leave in extraneous code. Clean up your act! <BR><BR>Here&#039;s my very, very quicky attempt at cleaning up your code.<BR><BR>THIS IS 100% UNTESTED. But it has a better chance of working than the code you posted that I then edited.<BR><BR>&#060;%<BR>...<BR>Sub GetQuote(strQuote, strAuthor) <BR>&nbsp; &nbsp; Dim strSQL &#039; String to hold Query <BR>&nbsp; &nbsp; Dim strPath &#039; String to hold Database Path <BR>&nbsp; &nbsp; Dim oConn &#039; Object to hold Connection <BR>&nbsp; &nbsp; Dim oRS &#039; Object to hold Recordset <BR><BR>&nbsp; &nbsp; &#039; Get the filename of our MS-Access file... <BR>&nbsp; &nbsp; strPath = Server.mappath(request("script_name")) <BR>&nbsp; &nbsp; strPath = Left(strPath, InStrRev(strPath,"\") ) & "quotes.mdb" <BR><BR>&nbsp; &nbsp; &#039; Create the Connection <BR>&nbsp; &nbsp; Set oConn = Server.CreateObject("ADODB.Connection") <BR>&nbsp; &nbsp; oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath <BR><BR>&nbsp; &nbsp; &#039; Use today as start date and 5 days from now as end<BR>&nbsp; &nbsp; start_date = Date( ) <BR>&nbsp; &nbsp; end_date = Date( ) + 5 <BR>&nbsp; &nbsp; &#039; and build the query<BR>&nbsp; &nbsp; strSQL = "SELECT Quote, Author FROM quotes WHERE dateValue(startdate) " _<BR>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; & " BETWEEN #" & start_date & "# AND #" & end_date & "#" <BR><BR>&nbsp; &nbsp; Set oRS = oConn.Execute(strSQL) <BR>&nbsp; &nbsp; If oRS.EOF Then <BR>&nbsp; &nbsp; &nbsp; &nbsp; &#039; An Error Occurred so use a static quote that is appropriate <BR>&nbsp; &nbsp; &nbsp; &nbsp; strQuote = "I haven&#039;t failed, I&#039;ve found 10,000 ways that don&#039;t work." <BR>&nbsp; &nbsp; &nbsp; &nbsp; strAuthor = "Thomas Eddison" &#039; WHO IS HE? Cousin of Tom Edison???<BR>&nbsp; &nbsp; Else <BR>&nbsp; &nbsp; &nbsp; &nbsp; strQuote = Trim(oRS("Quote")) <BR>&nbsp; &nbsp; &nbsp; &nbsp; strAuthor = Trim(oRS("Author")) <BR>&nbsp; &nbsp; End If<BR>&nbsp; &nbsp; &#039;Clean up in any case!<BR>&nbsp; &nbsp; oRS.Close <BR>&nbsp; &nbsp; oConn.Close <BR>&nbsp; &nbsp; Set oRS = Nothing <BR>&nbsp; &nbsp; Set oConn = Nothing <BR>End Sub <BR>%&#062; <BR><BR>That code also gets rid of some of the crap that you have learned from sites and samples that teach bad habits. <BR><BR>Example:<BR><BR>(1) If RS.EOF AND RS.BOF THEN<BR><BR>There are only two ways to get BOF: (1) The recordset is empty, in which case EOF is true so why bother testing BOF also? (2) You are moving *backwards* through the recordset (which you clearly are not doing here), in which case EOF can NOT be true, so testing it at the same time is a mistake.<BR><BR>(2) RS.MOVEFIRST<BR><BR>When you open a recordset, you are *guaranteed* to be at the start of it, so that call is useless. It&#039;s worse than useless if the ODBC drive were to actually try to implement it and the DB you are using doesn&#039;t support it. (Luckily, all the ODBC drivers I&#039;ve seen simply ignore it if the recordset has not moved from the starting position yet.)<BR><BR>You should *ONLY* use MoveFirst if you are doing random access inside a record set and need to go back to the beginning before doing RS.MOVE. <BR><BR>(3) EXIT SUB<BR><BR>When you don&#039;t get a valid record, you do this. Ummm...and then who, pray tell, will close the recordset and connection?<BR><BR>Of course, this just proves another myth wrong: You don&#039;t *really* have to close those and set them to nothing. When your ASP page exits, it happens automatically. If it didn&#039;t, then pages such as your original version would be leaving opened ADODB objects lying around all over the place and crashing ASP every 3 minutes. (This myth has a basis in fact: In the very earliest version of ASP, if you didn&#039;t close them yourself they weren&#039;t reliably closed. That&#039;s been fixed for 3 years, now, but the myth lives on.)<BR><BR>Enough. If you are going to post in ADVANCED, then maybe you should at least start reading and trying the help that people give you?<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts