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



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

cdr21Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CtrlAltDlCommented:
You don't need the Recordset1.movefirst because you are already at the beginning (BOF = Beginning Of File) of the Recordset.
0
peterxlaneCommented:
<%
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
peterxlaneCommented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Ashley BryantSenior Software EngineerCommented:
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
Ashley BryantSenior Software EngineerCommented:
I didn't refresh before I posted, so we kinda overwrote there...
0
cdr21Author Commented:


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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.