maggiemel
Posts: 75
|
| Posted: 01/18/2007, 12:32 PM |
|
I want my users to be able to return records from today, the past week, the past month, the past year. My datasource is a SQL statement, which runs well in Query Analyzer, and looks like this:
SELECT ItemId, SectionId, StartDate, EndDate, ItemHeader, ItemText
FROM Items
WHERE SectionId = {section}
AND ItemId = {item}
AND EndDate >= DATEADD(DAY, -{DateRange}, GETDATE())
ORDER BY EndDate desc
But when I user this SQL as a datasource I get an error:
Common Functions. CCFormatDate function error '800a0fa0'
Type mismatch.
Common.asp, line 933
I can't make this work with either TABLE or SQL as a datasource. When I try to use a TABLE datasource, and create an expression for the DateRange parameter, I get this error:
Error description: The value in field expr102 is not valid. Use the following format: m/d/yyyy.
Common Functions. CCFormatDate function error '800a0fa0'
Type mismatch.
Common.asp, line 933
I feel like I'm so close, but I am so frustrated! I've tried chaging the date format in the parameter window of the datasource, but it still tells me I'm using the wrong date format. Can anybody help me?
_________________
Melissa Cahill
http://www.hellcatmaggie.net/ |
 |
 |
peterr
Posts: 5971
|
| Posted: 01/19/2007, 1:48 AM |
|
It looks like the variable you're using in {DateRange} is not a date. How exactly is this parameter configured?
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
maggiemel
Posts: 75
|
| Posted: 01/19/2007, 5:35 AM |
|
Right, it's NOT a date. It's the number of days I want to use in my DATEADD function.
Your question pointed out one big mistake to me: I was trying to configure this as a Parameter rather than as an Expression. Originally, in the Table Parameter window, I had "Condition Type" set to parameter, with "FieldName=EndDate, Type=Date" in the left column, >= "EndDate >= DATEADD(DAY, - " & CCGetParam("DateRange",Empty) & ", GETDATE()) " in the right column. That was giving me the errors I reported in my initial post.
So, following you response I've tried to configure this as an Expression, as follows:
"EndDate >= DATEADD(DAY, - " & CCGetParam("DateRange",Empty) & ", GETDATE()) "
and now when I browse I get a different error:
ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.
Classes.asp, line 3319
So, maybe I'm making progress?
Looking forward to more clues...
Thanks, Peter.
_________________
Melissa Cahill
http://www.hellcatmaggie.net/ |
|
|
|
Edd
Posts: 547
|
| Posted: 01/21/2007, 5:06 PM |
|
"EndDate >= DATEADD(DAY, - " & CCGetParam("DateRange",Empty) & ", GETDATE()) "
You have a space after the negaive sign and for safety sake the DateRange should have a default number, i.e
"EndDate >= DATEADD(DAY, -" & CCGetParam("DateRange",1) & ", GETDATE()) "
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
maggiemel
Posts: 75
|
| Posted: 01/22/2007, 9:45 AM |
|
Thanks, Edd. I got all excited hoping it was that simple, but sadly I'm still getting the same ADODB recordset error.
When I put in a Response.Write directive to spit back the SQL, I can see that the CCGetParam("DateRange",1) parameter is not being processed within the expression.
"EndDate >= DATEADD(DAY, -" & CCGetParam("DateRange",1) & ", GETDATE()) "
So how can I get that DateRange parameter into my SQL statement correctly? Maybe I need to add in a "Before Build Select" statement...?
_________________
Melissa Cahill
http://www.hellcatmaggie.net/ |
|
|
|
Edd
Posts: 547
|
| Posted: 01/22/2007, 4:29 PM |
|
Maggie,
Is this MS SQL if it is then the statement should be:
"EndDate >= DATEADD(d, -" & CCGetParam("DateRange",1) & ", GETDATE()) "
Sorry - I didn't see it before.
Cheers
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
maggiemel
Posts: 75
|
| Posted: 01/23/2007, 8:01 AM |
|
Edd, it doesn't seem to be the SQL function -- both "d" and "DAY" work for me when I run the query outside of CodeCharge. The problem seems to be with substituting the DateRange value for the DateRange parameter in the SQL statement. I can't seem to make it happen either by using the TABLE or SQL datasource with the expression.
Related to my trouble with this query, when trying to output the SQL, WHERE and ORDER BY clauses of the datasource, I don't seem to see a complete SQL statement. Rather, I see the query with the parameters still in curly braces so it's hard for me to understand what's being processed as a WHERE parameter and what is not.
_________________
Melissa Cahill
http://www.hellcatmaggie.net/ |
|
|
|
Edd
Posts: 547
|
| Posted: 01/24/2007, 3:41 AM |
|
Melissa,
Can you check one more item for me. In the original code
SELECT ItemId, SectionId, StartDate, EndDate, ItemHeader, ItemText
FROM Items
WHERE SectionId = {section}
AND ItemId = {item}
AND EndDate >= DATEADD(DAY, -{DateRange}, GETDATE())
ORDER BY EndDate desc
can you check your paramater DateRange is in the same case as {DateRange}. CCS is CASE sensitive when it does its string substitution so if you have {daterange} OR {Daterange} it will not work.
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
maggiemel
Posts: 75
|
| Posted: 01/24/2007, 5:52 AM |
|
Edd, I finally gave up trying to make it work this way. Instead, I use a plain Table datasource for Items, then add this code to the grid's Before Build Select event:
If strDateRange <> Empty Then
If gridItems.datasource.Where <> Empty Then
gridItems.datasource.Where = gridItems.datasource.Where & " AND "
End if
Else
strDateRange = 1
End If
gridItems.datasource.Where = gridItems.datasource.Where & " EndDate >= DATEADD(d, -" & strDateRange & ", GETDATE())"
Works good. Many thanks for all your efforts!
P.S. Case sensitivity wasn't the issue, either.
_________________
Melissa Cahill
http://www.hellcatmaggie.net/ |
|
|
|
|
