Default Date and Search by Range

Default Date and Search by Range (Full Version)

All Forums >> [Web Development] >> ASP and Database

Message

Aelaron -> Default Date and Search by Range (1/26/2005 8:40:20)
Gang, I am trying to accomplish a couple things with regards to searching by date. 1. I'd like to set a default date that would show all DB information for the last 7 days. 2. I want to make the user be able to search for information between a certain date range. I'm not quite sure where to start, as I have searched the forum for the previous date questions, but am just kind of confused by it all. Thanks in advance, JB

Aelaron -> RE: Default Date and Search by Range (1/26/2005 10:32:23)

Ok...here's my first attempt at the search code:


fp_sQry="SELECT * FROM CriticalProjects WHERE (Layer LIKE '%::Layer::%' AND ProjectOwner LIKE '%::ProjectOwner::%' AND Scanner LIKE '%::Scanner::%' AND Product LIKE '%::Product::%' AND ID LIKE '%::ID::%') AND ((Purpose LIKE '%::WordSearch::%') OR (ScannerInstructions LIKE '%::WordSearch::') OR (SemInstructions LIKE '%::WordSearch::%')) AND ( ProjectDate BETWEEN '#FromDate#' AND '#ToDate#') ORDER BY ProjectDate DESC"
fp_sDefault="Layer=%&ProjectOwner=%&Scanner=%&Product=%&ID=%&Purpose=%&ScannerInstructions=%&SemInstructions=%&ProjectDate=%"

Here is the error I generate:

Database Results Error
Description: Syntax error converting datetime from character string.
Number: -2147217913 (0x80040E07)
Source: Microsoft OLE DB Provider for SQL Server

One or more form fields were empty. You should provide default values for all form fields that are used in the query.

All suggestions welcome.....

JB

rdouglass -> RE: Default Date and Search by Range (1/26/2005 11:37:29)
quote: ProjectDate BETWEEN '#FromDate#' AND '#ToDate#' Try without the apostrophes (at least for Access) (ProjectDate BETWEEN #FromDate# AND #ToDate#)

rdouglass -> RE: Default Date and Search by Range (1/26/2005 11:38:51)
quote: (ProjectDate BETWEEN #FromDate# AND #ToDate#) OOPS! I just saw you were using SQL Server. Try without the # signs and use the apostrophes: (ProjectDate BETWEEN 'FromDate' AND 'ToDate') That any help?

Aelaron -> RE: Default Date and Search by Range (1/26/2005 12:15:00)
rdouglass, Thanks for the help. I now have the query working by using: (ProjectDate BETWEEN '::FromDate::' AND '::ToDate::') Now I need to be able to set the default date to the current date minus 7 days. Any ideas? JB

dzirkelb1 -> RE: Default Date and Search by Range (1/26/2005 12:20:01)
Is it possible in the drw to do Date()-7 for the default value?

Aelaron -> RE: Default Date and Search by Range (1/26/2005 12:36:52)
I'm not sure how to code it. I have: fp_sDefault="Layer=%&ProjectOwner=%&Scanner=%&Product=%&ID=%&Purpose=%&ScannerInstructions=%&SemInstructions=%&ProjectDate > 'Date()-7'" I have also tried ProjectDate > Date()-7 But get no records returned..... JB

rdouglass -> RE: Default Date and Search by Range (1/26/2005 12:46:54)
quote: fp_sDefault="Layer=%&ProjectOwner=%&Scanner=%&Product=%&ID=%&Purpose=%&ScannerInstructions=%&SemInstructions=%&ProjectDate > 'Date()-7'" Been a while since I hacked up any DRW's but how about this: fp_sDefault="Layer=%&ProjectOwner=%&Scanner=%&Product=%&ID=%&Purpose=%&ScannerInstructions=%&SemInstructions=%&ProjectDate=" & DateAdd("d",-7,Date()) That any help?

Aelaron -> RE: Default Date and Search by Range (1/26/2005 12:54:45)
....ProjectDate>"& DateAdd("d",-7,Date()) still gives me no records returned. :(

rdouglass -> RE: Default Date and Search by Range (1/26/2005 13:06:24)
Assuming the DRW is on a diet, (inside the DRW) can you do a: Response.write(fp_sDefault) To see if the date is being calc'd?

rdouglass -> RE: Default Date and Search by Range (1/26/2005 13:09:59)
Oh![:o] Maybe we're looking at this wrong; maybe we should be defaulting the FromDate and ToDate? Maybe: fp_sDefault="Layer=%&ProjectOwner=%&Scanner=%&Product=%&ID=%&Purpose=%&ScannerInstructions=%&SemInstructions=%&FromDate=" & DateAdd("d",-7,Date()) & "&ToDate=" & Date() Maybe?

Aelaron -> RE: Default Date and Search by Range (1/26/2005 13:12:03)
Response.write(fp_sDefault) generates: Layer=%&ProjectOwner=%&Scanner=%&Product=%&ID=%&Purpose=%&ScannerInstructions=%&SemInstructions=%&ProjectDate>1/19/2005 So it looks like it's getting the date, just not filtering with it. JB

rdouglass -> RE: Default Date and Search by Range (1/26/2005 13:15:51)
quote: So it looks like it's getting the date, just not filtering with it. Did you see my post immediately above reguarding the wrong default fields?

Aelaron -> RE: Default Date and Search by Range (1/26/2005 13:19:12)
That did the trick :) Thanks a ton rdouglass! One last thing I am trying to figure out...but it's for another post. Thanks again! JB

Aelaron -> RE: Default Date and Search by Range (1/26/2005 17:28:30)
OK...I thought I had what I needed, but not so fast :( Is there a way to wildcard the From and To values for the date? Here's my deal, when a user initially goes to the query results page, I want it to default to information from the last 14 days. However, if the user submits different search criteria, I want his query to pull all ata matching his criteria regardless of the date, unless he enters a date range. JB

rdouglass -> RE: Default Date and Search by Range (1/27/2005 9:26:07)
quote: when a user initially goes to the query results page, I want it to default to information from the last 14 days. However, if the user submits different search criteria, I want his query to pull all ata matching his criteria regardless of the date, unless he enters a date range. Right where the fp_sDefault line is, maybe add some logic like so: IF Instr(Request.servervariables(HTTP_REFERER),Request.servervariables(URL))+0 = 0 THEN fp_sDefault="Layer=%&ProjectOwner=%&Scanner=%&Product=%&ID=%&Purpose=%&ScannerInstructions=%&SemInstructions=%&FromDate=" & DateAdd("d",-7,Date()) & "&ToDate=" & Date() ELSE fp_sDefault="Layer=%&ProjectOwner=%&Scanner=%&Product=%&ID=%&Purpose=%&ScannerInstructions=%&SemInstructions=%" END IF See what I'm doing? If the page didn't post to itself, then use the default dates; otherwise don't use 'em Haven't tested, but I think it should work. Any help?

Aelaron -> RE: Default Date and Search by Range (1/27/2005 12:10:13)
rdouglass... This line: IF Instr(Request.servervariables(HTTP_REFERER),Request.servervariables(URL))+0 = 0 THEN is giving me this error: Request object error 'ASP 0102 : 80004005' Expecting string input /project_folder_test/completed_projects.asp, line 231 The function expects a string as input. JB

rdouglass -> RE: Default Date and Search by Range (1/27/2005 12:53:03)
quote: IF Instr(Request.servervariables(HTTP_REFERER),Request.servervariables(URL))+0 = 0 THEN OOPS![:(] IF Instr(Request.servervariables("HTTP_REFERER"),Request.servervariables("URL"))+0 = 0 THEN Forgot the double quotes. That any better?

Aelaron -> RE: Default Date and Search by Range (1/27/2005 13:05:40)

That got rid of the error, however I get no recordds when I post from the page with anything other than a form/to date.

Here's the code I have:


fp_sQry="SELECT * FROM CriticalProjects WHERE (ID LIKE '%::ID::%' AND Layer LIKE '%::Layer::%' AND ProjectOwner LIKE '%::ProjectOwner::%' AND Scanner LIKE '%::Scanner::%' AND Product LIKE '%::Product::%' AND ID LIKE '%::ID::%') AND ((Purpose LIKE '%::WordSearch::%') OR (ScannerInstructions LIKE '%::WordSearch::') OR (SemInstructions LIKE '%::WordSearch::%')) AND ( ProjectDate BETWEEN '::FromDate::' AND '::ToDate::') ORDER BY ProjectDate DESC"

IF Instr(Request.servervariables("HTTP_REFERER"),Request.servervariables("URL"))+0 = 0 THEN 
fp_sDefault="Layer=%&ProjectOwner=%&Scanner=%&Product=%&ID=%&Purpose=%&ScannerInstructions=%&SemInstructions=%&FromDate=" & DateAdd("d",-14,Date()) & "&ToDate=" & Date() 
ELSE 
fp_sDefault="Layer=%&ProjectOwner=%&Scanner=%&Product=%&ID=%&Purpose=%&ScannerInstructions=%&SemInstructions=%" 
END IF

Also, I get no records if I only enter a from date.

The only way I get records is if I go to the page from another url, or if I give a from/to date and post from the page. Otherwise I get nothing.

JB

rdouglass -> RE: Default Date and Search by Range (1/27/2005 13:15:02)
quote: The only way I get records is if I go to the page from another url, or if I give a from/to date and post from the page. Otherwise I get nothing. Exactly. Isn't that what you wanted? To know if the page has been posted to itself? Do you have situations where users will go directly to the page as opposed to using a link? If so, maybe something like: IF (Instr(Request.servervariables("HTTP_REFERER"),Request.servervariables("URL"))+0 = 0) OR (trim(Request.servervariables("HTTP_REFERER"))&"" = "") THEN Haven't tested so just a quick guess....

Aelaron -> RE: Default Date and Search by Range (1/27/2005 13:29:31)
Maybe that's what I said I wanted...but not what I meant to say I wanted. Here's the deal.... This database gets about 200+ new entries a week and right now when the user comes to the search/results page they default view was all entries in the database. As you can imagine, this would be a huge amount of items in a month or so. Therefore, I wanted to default to the last 2 weeks of entries when a user comes to the page. We've accomplished this. Thanks. Now the user should be able to search by his name or some other parameter and pull up all entries matching that criteria regardles off the date they were submitted. I also what them to be able to insert a from/to date if they want to refine their search even more. This is not currently capable. How can this part be accomplished? Side Note: It would also be nice to make the to date default to the current day whenever a from date is entered and a to date is not. Thanks for your help so far, JB

rdouglass -> RE: Default Date and Search by Range (1/27/2005 13:57:36)
You want fries with that as well?[;)] With the most current version I posted: IF (Instr(Request.servervariables("HTTP_REFERER"),Request.servervariables("URL"))+0 = 0) OR (trim(Request.servervariables("HTTP_REFERER"))&"" = "") THEN What happens with that in the 3 sceanrios; from a link, post to itself, and direct from address bar or favorite? Do we get expected info with any of those 3 situations? I would expect that line above to cover all 3 but I'm not sure. The last thing you asked: quote: Side Note: It would also be nice to make the to date default to the current day whenever a from date is entered and a to date is not. We're stepping on the DRW a lot when we do this. It can be done but let's deal with this first issue first. Then you can look at something like: <pseudocode> IF theFromDate does have data THEN. IF theToDate does NOT have data THEN. Set theToDate to today. Replace theToDate in the DWR with the new theToDate. END IF. END IF. </pseudocode>

Aelaron -> RE: Default Date and Search by Range (1/27/2005 14:25:47)
OK, here's the report: I get the expected results when going directly to the page, to the page from a link, and if I enter a from/to date. I get no recordds if I search with any parameter besides a from/to date. JB

rdouglass -> RE: Default Date and Search by Range (1/27/2005 14:35:31)
quote: I get no recordds if I search with any parameter besides a from/to date. OK. One last stab at it: IF (Instr(Request.servervariables("HTTP_REFERER"),Request.servervariables("URL"))+0 = 0) OR (trim(Request.servervariables("HTTP_REFERER"))&"" = "") THEN fp_sDefault="Layer=%&ProjectOwner=%&Scanner=%&Product=%&ID=%&Purpose=%&ScannerInstructions=%&SemInstructions=%&FromDate=" & DateAdd("d",-7,Date()) & "&ToDate=" & Date() ELSE fp_sDefault="Layer=%&ProjectOwner=%&Scanner=%&Product=%&ID=%&Purpose=%&ScannerInstructions=%&SemInstructions=%" IF trim(Request.Form("FromDate")&"") = "" THEN fp_sDefault = fp_sDefault & "&FromDate=1/1/1900" END IF IF trim(Request.Form("ToDate")&"") = "" THEN fp_sDefault = fp_sDefault & "&ToDate=" & Date() END IF END IF See what I did? Check the fields and if blank, set them to something and add it to the default. I used 1/1/1900 as a FromDate. That should grab stuff from the beginning. That help any?

Aelaron -> RE: Default Date and Search by Range (1/27/2005 14:42:14)
rdouglass, THANKS! That took care of everything, even the salt and ranch for the fries. [:D] It defaults to current date ifno to date is entered as well as does full search for other parameters. Thanks for the teachings, JB

DRW desperation -> RE: Default Date and Search by Range (8/28/2005 21:05:04)
Aelaron, Been pulling my hair out trying to work this one out and its killing me. I like you have a date field that I wish to default using the Frontpage DRW. Default date of 1 month ago. DateAdd("m",-1,Date()) I figured the Dateadd function out but cannot get the code to stick on the page. I am a relative novice in Frontpage and are attempting to use the wizard as opposed to the editor. When setting the default in the wizard it does not save correctly. So I attempt to key the code in directly. I do this is the grey and marone coloured areas but continue to get date and time conversion issues. Can you provide any tips as to where I might be going wrong or maybe what code I should be placing when using the wizard? Any help would be greatly appreciated.

BeTheBall -> RE: Default Date and Search by Range (8/28/2005 21:15:50)
Welcome to OutFront. Your code changes get undone by FP's webbots. Run the macro here: http://www.frontpagewebmaster.com/m-279509/tm.htm It will delete the bot portion of the code so you can then edit the ASP. The bot portion is for the most part simply bloat anyway. The only functionality you lose is the ability to rerun the wizard on the page in question so you are forced to hand code any changes.

Page: [1]

0.125