?
Solved

ASP page with SQL string to pull recordset based on today's date versus dates in recordset

Posted on 2006-04-12
6
Medium Priority
?
292 Views
Last Modified: 2008-01-09


I have a simple MS Access database that with one table that has the following fields : FileId, FileStoreID, FileStartDate, FileEndDate, FileName, FilePageNum.

I plan to use this database to display content based on the start and end dates in an ASP page...

I am currently using the following Sql string, attempting to pull a recordset based on today's date:


todaysdate = Date()
SELECT *  FROM files WHERE FileStoreID = 1 AND " & todaysdate &  " BETWEEN FileDateStart AND FileDateEnd ORDER BY FilePageNum

Data exists with FileDateStart values of 4/11/2006 and FileDateEnd 4/18/2006 and FileStoreID's of 1....but I'm getting this error message on the line right after opening the recordset that does a Recordset1.movefirst  :

***********
ADODB.Recordset error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

/kait/recipe_channel/circular1.asp, line 26
***************

Am I not pulling the recordset correctly? Any ideas?

-cdr21

0
Comment
Question by:cdr21
6 Comments
 
LVL 14

Expert Comment

by:CtrlAltDl
ID: 16439129
You don't need the Recordset1.movefirst because you are already at the beginning (BOF = Beginning Of File) of the Recordset.
0
 
LVL 12

Accepted Solution

by:
peterxlane earned 1800 total points
ID: 16439379
<%
Set oConn = Server.CreateObject("ADODB.Connection")
Set oRecordset = Server.CreateObject("ADODB.Recordset")
oConn.Open(connStr)
todaysdate = Date()
strSQL = "SELECT * FROM files WHERE FileStoreID = 1 AND FileDateStart < #" & todaysdate & "# AND FileDateEnd > #" & todaysdate & "# ORDER BY FilePageNum"
Response.Write strSQL
oRecordset.Open strSQL, oConn

Do While Not oRecordset.EOF      
   Response.Write oRecordset("FileDateStart") & "<br />"
   oRecordset.MoveNext
Loop
%>
0
 
LVL 12

Expert Comment

by:peterxlane
ID: 16439420
Access deals best with date values when they are enclosed in #'s

You also could have used the between operator as you did like this:

strSQL = "SELECT *  FROM files WHERE FileStoreID = 1 AND #" & todaysdate &  "# BETWEEN FileDateStart AND FileDateEnd ORDER BY FilePageNum"

 

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 12

Assisted Solution

by:Ashley Bryant
Ashley Bryant earned 200 total points
ID: 16439429
yeah peter, but he also wants the files that are starting on that day, not just the ones that are actually between the two dates.  Gotta make those <= and >=

strSQL = "SELECT * FROM files WHERE FileStoreID = 1 AND FileDateStart <= #" & todaysdate & "# AND FileDateEnd >= #" & todaysdate & "# ORDER BY FilePageNum"

Also, does the BETWEEN command not work in Access?  I would've thought that this would work:

SELECT *  FROM files WHERE FileStoreID = 1 AND #"& todaysdate & "# BETWEEN FileDateStart AND FileDateEnd ORDER BY FilePageNum
0
 
LVL 12

Expert Comment

by:Ashley Bryant
ID: 16439436
I didn't refresh before I posted, so we kinda overwrote there...
0
 

Author Comment

by:cdr21
ID: 16441228


Thank you for your rapid and accurate responses! Not being a full time programmer, you people make my life much easier by being there to help with specific problems in a timely fashion, allowing me to do the things I want to do!

thanks again
-cdr21
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses
Course of the Month13 days, 12 hours left to enroll

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question