syntax help

Discussion in 'ASP General' started by Mangler, Nov 13, 2007.

  1. Mangler

    Mangler Guest

    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?
     
    Mangler, Nov 13, 2007
    #1
    1. Advertising

  2. Mangler

    Mangler Guest

    On Nov 13, 1:25 pm, Mangler <> 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())
     
    Mangler, Nov 13, 2007
    #2
    1. Advertising

  3. Mangler wrote:
    > On Nov 13, 1:25 pm, Mangler <> 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.
     
    Bob Barrows [MVP], Nov 13, 2007
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. gabriele renzi
    Replies:
    2
    Views:
    242
    gabriele renzi
    Dec 31, 2005
  2. Ken Bloom
    Replies:
    3
    Views:
    248
  3. Good Night Moon
    Replies:
    9
    Views:
    322
    Rick DeNatale
    Jul 25, 2007
  4. Jacob Grover
    Replies:
    5
    Views:
    352
    Jacob Grover
    Jul 18, 2008
  5. Mark Richards
    Replies:
    3
    Views:
    349
    Tad McClellan
    Nov 18, 2007
Loading...

Share This Page