469,576 Members | 1,834 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,576 developers. It's quick & easy.

Need possible array help

I have a SQL table called "EXAMPLE" with 2 columns; personId and reportDate. personId consists of a 36 character GUID. reportDate has a date such as 1/1/2005, 2/1/2005,3/1/2005, etc. The date is always the first of the month. Sometimes they are skipping months, ie; 6/1/2006,7/1/2006,11/1/2006,12/1/2006, etc.

Now to my long winded question:

I have an ASP page that displays the selected month and associated personId data based on passing the personId and reportDate in the querystring. If I am on a page looking at personId '123' and reportDate '2/1/2006', for example, I want to place 2 hyperlinks on the page for the reportDate before and after for that personId. So, using my example, the page should show a link to 1/1/2006 and 3/1/2006. The problem is, if 3/1/2006 is not in the DB, it should go to the next available date in the DB. Also, if 2/1/2006 is the last date, or similarly 1/1/2006 is the first, their should not be a link to the dates before and/or after due to this.

Any ideas?
Mar 21 '07 #1
2 978
jhardman
3,406 Expert 2GB
The problem is, if 3/1/2006 is not in the DB, it should go to the next available date in the DB.
this one is easy if answered by itself. I assume you are linking with something like:[html]<a href="samePage.asp?date=3/1/2006">next month</a>[/html] right? just open the db with this query:
Expand|Select|Wrap|Line Numbers
  1. query = "SELECT * FROM EXAMPLE WHERE reportDate >= " & request("date") & " ORDER BY reportDate"
  2.  
then just use the first record. The big problem is you can't tell with this method if there is anything before the record you pulled up, so it won't work to try this and solve the second question.

Also, if 2/1/2006 is the last date, or similarly 1/1/2006 is the first, their should not be a link to the dates before and/or after due to this.

Any ideas?
This one is tougher. To solve both together you need to do something like this:
Expand|Select|Wrap|Line Numbers
  1. query = "SELECT * FROM EXAMPLE ORDER BY reportDate"
  2. objRS.open query, objConn, adOpenDynamic
  3. prevMonth = objRS("reportMonth")
  4. if prevMonth = request("date") then 
  5.    'date requested is first in db, so handle accordingly
  6.    'you might not actually need anything here at all
  7. else
  8.    objRS.moveNext
  9.    do until dateDiff("d", objRS("reportMonth"), request("date")) <=0
  10.       prevMonth = objRS("reportDate")
  11.       objRS.moveNext
  12.    loop
  13.    'the exact date of the previous
  14.    'month is saved as "prevMonth"
  15.    'I would create the link to the previous month here
  16. end if
  17. 'you are currently on the record requested, the link to the previous
  18. 'month was create if needed. I would try "objRS.moveNext" to find
  19. 'the exact date of the next record so that I can write the link to
  20. 'the exact date needed next, but that's just me
  21.  
Let me know if this helps.

Jared
Mar 22 '07 #2
Jared,

Thank you very much. You solved the problem for me. This was a mental block that I could not figure out.
Mar 22 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Mekon | last post: by
2 posts views Thread by Jackson Yap | last post: by
4 posts views Thread by semooo | last post: by
23 posts views Thread by vinod.bhavnani | last post: by
12 posts views Thread by nephish | last post: by
13 posts views Thread by James | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.