2. ASPRunnerPro General questions
  3. Dynamic where clause
This topic is locked

Dynamic where clause

 		8/17/2007 5:53:14 PM
ASPRunnerPro General questions
stanl author

I have a form that is filtered by "where mydate = date()" or the current date. I would like to add two buttons on the list view that would move forward one day and/or backward one day. This would need to change the filtered where clause. Any help in setting this up is appreciated.
Thanks

Stan
Sergey Kornilov admin 8/20/2007

I think this is the job for a custom OnScreen event. In version 5.1 proceed to Visual Editor and use "Insert ASP code button"
This event should get the current filter value and display two links: Yesterday and Tomorrow.
Here is the very sample code. I don't think it works however you can get the idea:

Response.Write "<a href=# onclick=""java script:frmAdmin.a.value='prev'; frmAdmin.submit();"">Yesterday</a> "

Response.Write "<a href=# onclick=""java script:frmAdmin.a.value='next'; frmAdmin.submit();"">Tomorrow</a>"


Besides that you need to use BeforeSQLQuery event:

if Session("mydate")="" then

d=now()

else

d=Session("mydate")

end if
If GetRequestForm("a")="next" then

d = DateAdd("d",1,d)

elseif GetRequestForm("a")="prev" then

d = DateAdd("d",-1,d)

end if
strSQL = AddWhere(strSQL, "mydate=' & d & "'")
Session("mydate") = d
stanl author 8/21/2007

I have a form that is filtered by "where mydate = date()" or the current date. I would like to add two buttons on the list view that would move forward one day and/or backward one day. This would need to change the filtered where clause. Any help in setting this up is appreciated.

Thanks

Stan


I added the code and did some testing, the sql statement ends up being:
"select [Time_ID], [Day], [user_pk], [Projects_ID], [Task_ID], [Description], [Hours] From [TimeSheet] Where (([Day]=date()) and ([user_pk]=1) ORDER BY [Day] DESC) and (mydate=8/21/2007 8:10:21 AM)"
It looks as if it appended the mydate... to the end of the strsql. Two things, 1) it doesn't resplace the original Day = Date() and 2) it places it after the order by statement.
Suggestions?
Thanks
Sergey Kornilov admin 8/21/2007

First of all you need to remove WHERE clause from the SQL query on SQL query tab.
I guess you also need to modify my code to extract the date potion from the datetime value returned by now().