syntax help

13 Nov 2007 6:25 PM

Mangler

I created a query in SQL that works fine but when i put it in the asp
code ( with dreamweaver ) i am getting an error when viewing the
page: expected end of statment...

rsBERProd_cmd.CommandText = "SELECT ISNULL(sum(case when type = 'BD'
and _dte = Convert(Char(8),GETDATE(),112)   then qty else 0 end),0) as
Dayqty,ISNULL(sum(case when type = 'BD' and _dte between dateadd(dd,-
(day(GetDate())-1),GetDate()) and dateadd(dd,-(day(dateadd(mm,
1,GetDate()))),dateadd(mm,1,GetDate()))    then qty else 0 end),0) as
Monthqty,ISNULL(sum(case when type = 'BD' and   Year(_dte) =
Year(GetDate())   then qty else 0 end),0) as Yearqty,ISNULL(sum(case
when type = 'BD' and DatePart("ww", _dte)=DatePart("ww",
GetDate())     then qty else 0 end),0) as weekqty FROM
dbo.warehousebertransfer"

the problem i know is here: ("ww", _dte)=DatePart("ww",
GetDate())

the query needs those quotes so how can i get this to work? i tried
using just single quotes but then i get a sql error.... so can someone
help me make this work on the page?

13 Nov 2007 6:32 PM

Mangler

On Nov 13, 1:25 pm, Mangler <dwald***@aspdevil.com> wrote:
Show quoteHide quote

> I created a query in SQL that works fine but when i put it in the asp
> code ( with dreamweaver ) i am getting an error when viewing the
> page: expected end of statment...
>
> rsBERProd_cmd.CommandText = "SELECT ISNULL(sum(case when type = 'BD'
> and _dte = Convert(Char(8),GETDATE(),112)   then qty else 0 end),0) as
> Dayqty,ISNULL(sum(case when type = 'BD' and _dte between dateadd(dd,-
> (day(GetDate())-1),GetDate()) and dateadd(dd,-(day(dateadd(mm,
> 1,GetDate()))),dateadd(mm,1,GetDate()))    then qty else 0 end),0) as
> Monthqty,ISNULL(sum(case when type = 'BD' and   Year(_dte) =
> Year(GetDate())   then qty else 0 end),0) as Yearqty,ISNULL(sum(case
> when type = 'BD' and DatePart("ww", _dte)=DatePart("ww",
> GetDate())     then qty else 0 end),0) as weekqty FROM
> dbo.warehousebertransfer"
>
> the problem i know is here: ("ww", _dte)=DatePart("ww",
> GetDate())
>
> the query needs those quotes so how can i get this to work? i tried
> using just single quotes but then i get a sql error.... so can someone
> help me make this work on the page?

nevermind, i just figured it out :)

DatePart(" & """ww"", _dte)=DatePart(" & """ww"", GetDate())

13 Nov 2007 7:08 PM

Bob Barrows [MVP]

Mangler wrote:
Show quoteHide quote

> On Nov 13, 1:25 pm, Mangler <dwald***@aspdevil.com> wrote:
>> I created a query in SQL that works fine but when i put it in the asp
>> code ( with dreamweaver ) i am getting an error when viewing the
>> page: expected end of statment...
>>
>> rsBERProd_cmd.CommandText = "SELECT ISNULL(sum(case when type = 'BD'
>> and _dte = Convert(Char(8),GETDATE(),112)   then qty else 0 end),0)
>> as Dayqty,ISNULL(sum(case when type = 'BD' and _dte between
>> dateadd(dd,- (day(GetDate())-1),GetDate()) and
>> dateadd(dd,-(day(dateadd(mm, 1,GetDate()))),dateadd(mm,1,GetDate()))
>> then qty else 0 end),0) as Monthqty,ISNULL(sum(case when type = 'BD'
>> and   Year(_dte) = Year(GetDate())   then qty else 0 end),0) as
>> Yearqty,ISNULL(sum(case when type = 'BD' and DatePart("ww",
>> _dte)=DatePart("ww",
>> GetDate())     then qty else 0 end),0) as weekqty FROM
>> dbo.warehousebertransfer"
>>
>> the problem i know is here: ("ww", _dte)=DatePart("ww",
>> GetDate())
>>
>> the query needs those quotes so how can i get this to work? i tried
>> using just single quotes but then i get a sql error.... so can
>> someone help me make this work on the page?
>
> nevermind, i just figured it out :)
>
> DatePart(" & """ww"", _dte)=DatePart(" & """ww"", GetDate())

That works?? Given the use of GETDATE and ISNULL, this looks like T-SQL.
Unlike the VB/VBA/vbscript versions of the DATEPART,DATEADD and DATEDIFF
functions, T-SQL does not permit the use of quotes around the datepart
argument. It should simply be:

DatePart(ww, _dte)=DatePart(ww, GetDate())

See earlier in your sql statement where you used the dateadd function?
Same idea.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.