Querying Based On > Than DBDATE (Full Version)

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



Message

Nathan Goulette -> Querying Based On > Than DBDATE (5/4/2006 15:06:19)

I have a table full of records that have 3 date fields (DATE, TIME & DATE TIME) [Don't Ask Why I Have 3, Cuz I Don't Remember [8D]]

So any way, I want to query the data that is X days older than the date it was submitted.

Table: tblREQUESTS
Date: reqSUBMITTEDDATE [5/3/2006]
Time: reqSUBMITTEDTIME [2:42:10 PM]
Date Time: reqWHEN [5/3/2006 3:12:19 PM]

So I have a drop down list with 5, 10, 30 and 45. I want those values to pass to a query that will show me all the requests that have submitted dates that are greater than the number passed from the drop down.

So if I have a request that has a submitted date of April 1, 2006 and I select 5 from the drop down and on April 7th I run this, I should see that request since it's submitted date is greater then 5 days old.


Not quite sure how I would go about this.

Any thoughts?

Thanks
Nate



rdouglass -> RE: Querying Based On > Than DBDATE (5/4/2006 15:55:28)

"SELECT *, DateDiff('d',reqSUBMITTEDDATE,reqWHEN) AS Expr1 FROM tblREQUESTS WHERE Expr1 >= " & Request.form(myDropdown)

Something like that?



Nathan Goulette -> RE: Querying Based On > Than DBDATE (5/5/2006 11:44:08)

Yea, something like this.

In looking at this tho, it looks like you are trying to get the difference between 'reqSUBMITTED' and 'reqWHEN'. What I really need is to get the difference between 'reqSUBMITTED' OR 'reqWHEN' AND the current date when the query is run.

The reason I say 'reqSUBMITTED' OR 'reqWHEN' is because I have both those fields populated when the initial request is submitted. I listed them both and the formats that they write since I didn't know which format would be most appropriate for getting the results I need.

Thanks.
Nate



rdouglass -> RE: Querying Based On > Than DBDATE (5/5/2006 12:03:07)

quote:

"SELECT *, DateDiff('d',reqSUBMITTEDDATE,reqWHEN) AS Expr1 FROM tblREQUESTS WHERE Expr1 >= " & Request.form(myDropdown)


OK. I think I see now. So reqWhen is always a combination of the reqSUBMITTEDDATE and the reqSUBMITTEDTIME? (OK as you say I won't ask why. [:D])

Anyways, if that is the case, how about this then:

"SELECT *, DateDiff('d',reqSUBMITTEDDATE,Date()) AS Expr1 FROM tblREQUESTS WHERE Expr1 >= " & Request.form(myDropdown)

See, this here:

DateDiff('d',reqSUBMITTEDDATE,Date())

will give the number of days difference between the reqSUBMITTEDDATE and Today.

Is that more like what you're trying to do?



Nathan Goulette -> RE: Querying Based On > Than DBDATE (5/5/2006 12:19:56)

Would it be "normal" for FP not to accept this as a custom sql [STEP 2] cript in the DRW?

I am putting it on a diet to see if it will work if I manually put that query in place.



Nathan Goulette -> RE: Querying Based On > Than DBDATE (5/5/2006 12:38:03)

I dieted it and I am getting this error:
quote:

Request object error 'ASP 0102 : 80004005'

Expecting string input

/Test_B.asp, line 12

The function expects a string as input.



Here is the code from Test_A.asp that has the dropdown.
<html>

<head>
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Test Page A</title>
</head>

<body>
<form method="POST" action="Test_B.asp">
	<p>
	<!--webbot bot="Validation" S-Display-Name="Date Range" B-Value-Required="TRUE" B-Disallow-First-Item="TRUE" --><select size="1" name="reqSEARCH">
	<option selected>Select A Date Range</option>
	<option>5</option>
	<option>15</option>
	<option>30</option>
	<option>45</option>
	<option>60</option>
	</select><input type="submit" value="Submit" name="B1"></p>
</form>

</body>

</html>


And here is the dieted page that recieves the number of days from the dropdown.
<html>

<head>
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Test Page B</title>
</head>

<body>
<!--#include file="_fpclass/fpdblib.inc"--><%
fp_sQry="SELECT *, DateDiff('d',reqSUBMITTEDDATE,Date()) AS Expr1 FROM tblREQUESTS WHERE Expr1 >= " & Request.form(reqSEARCH)
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=23 align=""LEFT"" width=""100%"">No records returned.</td></tr>"
fp_sDataConn="connNWRDATA"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&reqID=3&reqSUBMITTEDBY=202&reqSUBMITTEDGROUP=202&reqSUBMITTEDDATE=135&reqSUBMITTEDTIME=135&reqSUBMITTEDFOR=202&reqDUEDATE=135&reqTYPE_CONTENT=202&reqTYPE_FIREWALL=202&reqTYPE_IP=202&reqTYPE_PORT=202&reqTYPE_SSL=202&reqTICKETCREATED=202&reqTICKETNUMBER=202&reqTICKETASSIGNED=202&reqTICKETOWNER=202&reqSUMMARY=203&reqRECIEVED=202&reqFORMLINK=202&reqSTATUS=202&reqWHEN=135&reqURGENT=202&reqFORMLINKSITE=202&"
fp_iDisplayCols=23
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->

<%=FP_FieldVal(fp_rs,"reqID")%><br><br>
<%=FP_FieldVal(fp_rs,"reqSUBMITTEDBY")%><br><br>
<%=FP_FieldVal(fp_rs,"reqSUBMITTEDGROUP")%><br><br>
<%=FP_FieldVal(fp_rs,"reqSUBMITTEDDATE")%><br><br>
<%=FP_FieldVal(fp_rs,"reqSUBMITTEDTIME")%><br><br>
<%=FP_FieldVal(fp_rs,"reqSUBMITTEDFOR")%><br><br>
<%=FP_FieldVal(fp_rs,"reqDUEDATE")%><br><br>
<%=FP_FieldVal(fp_rs,"reqTYPE_CONTENT")%><br><br>
<%=FP_FieldVal(fp_rs,"reqTYPE_FIREWALL")%><br><br>
<%=FP_FieldVal(fp_rs,"reqTYPE_IP")%><br><br>
<%=FP_FieldVal(fp_rs,"reqTYPE_PORT")%><br><br>
<%=FP_FieldVal(fp_rs,"reqTYPE_SSL")%><br><br>
<%=FP_FieldVal(fp_rs,"reqTICKETCREATED")%><br><br>
<%=FP_FieldVal(fp_rs,"reqTICKETNUMBER")%><br><br>
<%=FP_FieldVal(fp_rs,"reqTICKETASSIGNED")%><br><br>
<%=FP_FieldVal(fp_rs,"reqTICKETOWNER")%><br><br>
<%=FP_FieldVal(fp_rs,"reqSUMMARY")%><br><br>
<%=FP_FieldVal(fp_rs,"reqRECIEVED")%><br><br>
<%=FP_FieldVal(fp_rs,"reqFORMLINK")%><br><br>
<%=FP_FieldVal(fp_rs,"reqSTATUS")%><br><br>
<%=FP_FieldVal(fp_rs,"reqWHEN")%><br><br>
<%=FP_FieldVal(fp_rs,"reqURGENT")%><br><br>
<%=FP_FieldVal(fp_rs,"reqFORMLINKSITE")%><br><br>
<!--#include file="_fpclass/fpdbrgn2.inc"-->

</p>

</body>

</html>



rdouglass -> RE: Querying Based On > Than DBDATE (5/5/2006 13:03:31)

quote:

I dieted it and I am getting this error:


Are you getting that error when you first enter the page or when you're POSTing to it?



Nathan Goulette -> RE: Querying Based On > Than DBDATE (5/5/2006 13:12:53)

i am posting from one page to another. TEST_A.ASP -> TEST_B.ASP



Nathan Goulette -> RE: Querying Based On > Than DBDATE (5/5/2006 14:01:44)

Ok, I modified the query to this and I am able to bow put it into the CUSTOM SQL step of the DRW.
"SELECT *, DateDiff('d',reqSUBMITTEDDATE,Date()) AS Expr1 FROM tblREQUESTS WHERE Expr1 >= ::reqSEARCH::"


But now I get:
quote:

Database Results Error
Description: No value given for one or more required parameters.
Number: -2147217904 (0x80040E10)
Source: Microsoft JET Database Engine


I have even tried bypassing the form submitting [TEST_A.ASP] by calling TEST_B.ASP as this: Test_B.asp?reqSEARCH=15



rdouglass -> RE: Querying Based On > Than DBDATE (5/5/2006 14:22:15)

OK. Let's confirm you actually are picking up something in the DRW. Tray adding this to that code temporarily to see what we're sending to the DB:


....
fp_iDisplayCols=23
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>

<%Response.write(fp_sQry)
Response.end%>

<!--#include file="_fpclass/fpdbrgn1.inc"-->

<%=FP_FieldVal(fp_rs,"reqID")%><br><br>
<%=FP_FieldVal(fp_rs,"reqSUBMITTEDBY")%><br><br>
<%=FP_FieldVal(fp_rs,"reqSUBMITTEDGROUP")%><br><br>
........


Just to see what fp_sQry is.



Nathan Goulette -> RE: Querying Based On > Than DBDATE (5/5/2006 14:26:21)

I get this: SELECT *, DateDiff('d',reqSUBMITTEDDATE,Date()) AS Expr1 FROM tblREQUESTS WHERE (Expr1 > ::reqSEARCH::)



rdouglass -> RE: Querying Based On > Than DBDATE (5/5/2006 15:31:59)

quote:

Request.form(reqSEARCH)


So that is returning:

::reqSEARCH::

?? Obviously that's not right. Sorry, I did not see this:

"SELECT *, DateDiff('d',reqSUBMITTEDDATE,Date()) AS Expr1 FROM tblREQUESTS WHERE Expr1 >= ::reqSEARCH::"

What we want is to request the form the normal way IMO like so:

fp_sQry="SELECT *, DateDiff('d',reqSUBMITTEDDATE,Date()) AS Expr1 FROM tblREQUESTS WHERE Expr1 >= " & Request.form(reqSEARCH)

and then see what the Response.write(fp_sQry) outputs.



Nathan Goulette -> RE: Querying Based On > Than DBDATE (5/5/2006 15:46:55)

I get this:
quote:

Request object error 'ASP 0102 : 80004005'
Expecting string input

/test.asp, line 43

The function expects a string as input.

I don't think that the DRW likes & Request.form(reqSEARCH) at the end of the fp_sQry line.

If I remove that, I get this:
quote:

"SELECT *, DateDiff('d',reqSUBMITTEDDATE,Date()) AS Expr1 FROM tblREQUESTS WHERE Expr1 >= "


I then changed the query to this:
"SELECT *, DateDiff('d',reqSUBMITTEDDATE,Date()) AS Expr1 FROM tblREQUESTS WHERE Expr1 >= 15"


Then I got:
quote:

"SELECT *, DateDiff('d',reqSUBMITTEDDATE,Date()) AS Expr1 FROM tblREQUESTS WHERE Expr1 >= 15"



rdouglass -> RE: Querying Based On > Than DBDATE (5/5/2006 16:07:35)

quote:

I don't think that the DRW likes & Request.form(reqSEARCH) at the end of the fp_sQry line.

If I remove that, I get this:quote:

"SELECT *, DateDiff('d',reqSUBMITTEDDATE,Date()) AS Expr1 FROM tblREQUESTS WHERE Expr1 >= "


The DRW will deal with that just fine. Look closely at the request object. See it? I missed the quotes. For a quick test, try changing the fp_sQry to this:

fp_sQry="SELECT *, DateDiff('d',reqSUBMITTEDDATE,Date()) AS Expr1 FROM tblREQUESTS WHERE Expr1 >= " & Request.form("reqSEARCH")



Nathan Goulette -> RE: Querying Based On > Than DBDATE (5/5/2006 16:32:38)

Yea, I still cannot VERIFY the Custom QRY in the DRW.

I get the attached error.

If I put the code on a diet to bypass the error I get this:
quote:

SELECT *, DateDiff('d',reqSUBMITTEDDATE,Date()) AS Expr1 FROM tblREQUESTS WHERE Expr1 >= 45


If I take out the:
<%Response.write(fp_sQry) 
Response.end%>
Then my error is this:
quote:

Database Results Error
Description: No value given for one or more required parameters.
Number: -2147217904 (0x80040E10)
Source: Microsoft JET Database Engine


[image]local://upfiles/6916/B42747D2E75D4DCDAF13AF6A58415DFA.jpg[/image]



rdouglass -> RE: Querying Based On > Than DBDATE (5/5/2006 16:47:08)

quote:

SELECT *, DateDiff('d',reqSUBMITTEDDATE,Date()) AS Expr1 FROM tblREQUESTS WHERE Expr1 >= 45


Can we try this one?

SELECT *, DateDiff('d',reqSUBMITTEDDATE,Date()) AS Expr1 FROM tblREQUESTS WHERE DateDiff('d',reqSUBMITTEDDATE,Date()) >= 45



Nathan Goulette -> RE: Querying Based On > Than DBDATE (5/5/2006 16:57:18)

Yes, that does give me the results I am looking for.
I also needed some additional criteria which I added and that also works with your query.

Now how do I get the range to be dynamic from the form?

This is what I currently have and it does work:
SELECT *, DateDiff('d',reqSUBMITTEDDATE,Date()) AS Expr1 FROM tblREQUESTS WHERE reqSTATUS <> '::reqSTATUS::' AND DateDiff('d',reqSUBMITTEDDATE,Date()) >= 45



rdouglass -> RE: Querying Based On > Than DBDATE (5/8/2006 9:15:44)

quote:

SELECT *, DateDiff('d',reqSUBMITTEDDATE,Date()) AS Expr1 FROM tblREQUESTS WHERE reqSTATUS <> '::reqSTATUS::' AND DateDiff('d',reqSUBMITTEDDATE,Date()) >= 45


I would suggest this:

"SELECT *, DateDiff('d',reqSUBMITTEDDATE,Date()) AS Expr1 FROM tblREQUESTS WHERE reqSTATUS <> '::reqSTATUS::' AND DateDiff('d',reqSUBMITTEDDATE,Date()) >= " & Request("reqSEARCH")

But since I still don't know wy that didn't work the first time (I use it like that all the time), we may have to use it this way:

SELECT *, DateDiff('d',reqSUBMITTEDDATE,Date()) AS Expr1 FROM tblREQUESTS WHERE reqSTATUS <> '::reqSTATUS::' AND DateDiff('d',reqSUBMITTEDDATE,Date()) >= ::reqSearch::

That help any?



Nathan Goulette -> RE: Querying Based On > Than DBDATE (5/30/2006 17:53:33)

Sorry it took so long to get back to you on this. I got dragged off to a bunch of other projects related to our SOX Audits and jsut got back to this.

I am still having troubles with both of your recommended options when utilized within the DRW. When I edit the DRW and enter your suggestions in via notepad they both work. The main difference is that I am unable to set the default like I typically do whereas the second suggestion does allow me to do so.

So even though they are not functioning as expected from within the DRW, I still have the results I am looking for so I think that we are good there.

How do I use the ORDER BY with this query?
SELECT *, DateDiff('d',reqSUBMITTEDDATE,Date()) AS howMANY FROM tblREQUESTS WHERE reqSTATUS <> '::reqSTATUS::' AND DateDiff('d',reqSUBMITTEDDATE,Date()) >= ::reqSEARCHDAYS::

I tried adding ORDER BY howMANY ASC but it gives me an error:
Database Results Error
Description: No value given for one or more required parameters.
Number: -2147217904 (0x80040E10)
Source: Microsoft JET Database Engine
And I remove jsut the ORDER BY section it goes back to working as normal.

Another question too, how would I get a count of the results based on the query we are using?

Thanks.



Nathan Goulette -> RE: Querying Based On > Than DBDATE (5/30/2006 19:05:55)

quote:

How do I use the ORDER BY with this query?
SELECT *, DateDiff('d',reqSUBMITTEDDATE,Date()) AS howMANY FROM tblREQUESTS WHERE reqSTATUS <> '::reqSTATUS::' AND DateDiff('d',reqSUBMITTEDDATE,Date()) >= ::reqSEARCHDAYS::
Nevermind this request, I figured out that I cannot use the "AS howMANY" as the item to search by. I changed it to a field in the DB and it seems to work fine.

I am still looking to get the count for a set up designated days. For example I would like to get a count of the number of records that are 30 or more.



rdouglass -> RE: Querying Based On > Than DBDATE (5/30/2006 20:41:29)

quote:

...DateDiff('d',reqSUBMITTEDDATE,Date()) >= ::reqSEARCHDAYS::...


Does that return the appropriate records? For instance, if you put 30 in the field 'reqSEARCHDAYS', does it return the correct records? If so, you may be able to use <%=fp_iCount%> from the DRW. If i was pulling them into an array using just ASP, it would be simply <%=ubound(myarray,2)%> but we're not using an array.

I must point out that fp_iCount is not really the same as ubound. In reality it represents the record number (or index) that the DWR loop is on. If you did something like

myCount = fp_iCount

inside the DWR, it would reset the variable myCount to the loop number (or index) on each iteration of the loop but we would end up with the count on the last time thru. (Actually it may be 1 less than the count - I cannot remeber offhand.)

That help any? It's so difficult sometimes (IMO anyways) to force the DRW into doing stuff which seems so easy using straight ASP.



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.109375