Access SQL to ASP SQL not working ... need help please

Discussion in 'ASP General' started by David, Sep 28, 2006.

  1. David

    David Guest

    Hi,

    I have taken some SQL from MS Access and got it working.
    I have since updated the SQL in Access but cannot get the additional
    part working in ASP.

    The working SQL I have in asp is:-

    strQuery = strQuery & " HAVING ((OrderQuantity-Sum(QtyShipped)>=0) AND
    (OrderLines.SelectAll=-1) AND (PCBForecast.HeldMarker='Scheduled'))"

    I need to add the following but keep getting errors saying my SQL is
    incorrect.

    This is the additional part which should also be on this line:-

    AND ((PCBForecast.ShipETA) Between Now() And #12/30/3000#))"

    or another variant I require

    AND ((PCBForecast.ShipETA)=#12/31/3000#))"

    What is the correct way to write both of these, as I cannot get either
    to work in asp, yet they work in Access.

    Apprecaite your advise

    Thanks

    David
    David, Sep 28, 2006
    #1
    1. Advertising

  2. "David" <> wrote in message
    news:...
    > Hi,
    >
    > I have taken some SQL from MS Access and got it working.
    > I have since updated the SQL in Access but cannot get the additional
    > part working in ASP.
    >
    > The working SQL I have in asp is:-
    >
    > strQuery = strQuery & " HAVING ((OrderQuantity-Sum(QtyShipped)>=0) AND
    > (OrderLines.SelectAll=-1) AND (PCBForecast.HeldMarker='Scheduled'))"
    >
    > I need to add the following but keep getting errors saying my SQL is
    > incorrect.
    >
    > This is the additional part which should also be on this line:-
    >
    > AND ((PCBForecast.ShipETA) Between Now() And #12/30/3000#))"
    >
    > or another variant I require
    >
    > AND ((PCBForecast.ShipETA)=#12/31/3000#))"
    >
    > What is the correct way to write both of these, as I cannot get either
    > to work in asp, yet they work in Access.
    >
    > Apprecaite your advise
    >


    The first thing you should do when ever you have a problem like this is to
    check the SQL that is actually being executed.

    Response.Write strQuery
    Response.End

    Just before you would normally exectute it. Check that the SQL is what you
    expect it to be.

    Another useful piece of advise would be specific! "I cannot get either to
    work" tells us nothing.

    Did it ignore your changes? Produce the wrong results?
    Generate an error? If so what did it say? I dunno but it might be a clue
    :p
    There is a space before the AND right?

    > Thanks
    >
    > David
    >
    Anthony Jones, Sep 28, 2006
    #2
    1. Advertising

  3. David wrote:
    > Hi,
    >
    > I have taken some SQL from MS Access and got it working.
    > I have since updated the SQL in Access but cannot get the additional
    > part working in ASP.
    >
    > The working SQL I have in asp is:-
    >
    > strQuery = strQuery & " HAVING ((OrderQuantity-Sum(QtyShipped)>=0) AND
    > (OrderLines.SelectAll=-1) AND (PCBForecast.HeldMarker='Scheduled'))"
    >
    > I need to add the following but keep getting errors saying my SQL is
    > incorrect.
    >
    > This is the additional part which should also be on this line:-
    >
    > AND ((PCBForecast.ShipETA) Between Now() And #12/30/3000#))"
    >
    > or another variant I require
    >
    > AND ((PCBForecast.ShipETA)=#12/31/3000#))"
    >
    > What is the correct way to write both of these, as I cannot get either
    > to work in asp, yet they work in Access.
    >

    Two things:
    1. Datetime literals in SQL Server are delimited by quotes ('), not
    hashmarks (#).
    2. There are no VBA functions such as Now() available in SQL Server. The
    variant of sql used by SQL Server is called Transact-SQL (sometimes
    called T-SQL for short). Transact-SQL has builtin functions for doing
    many of the things VBA functions are used for in Jet. GETDATE() is the
    T-SQL equivalent to Now().

    You may want to have a look at this article:
    http://www.aspfaq.com/show.asp?id=2214


    --
    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], Sep 28, 2006
    #3
  4. "Bob Barrows [MVP]" <> wrote in message
    news:...
    > David wrote:
    > > Hi,
    > >
    > > I have taken some SQL from MS Access and got it working.
    > > I have since updated the SQL in Access but cannot get the additional
    > > part working in ASP.
    > >
    > > The working SQL I have in asp is:-
    > >
    > > strQuery = strQuery & " HAVING ((OrderQuantity-Sum(QtyShipped)>=0) AND
    > > (OrderLines.SelectAll=-1) AND (PCBForecast.HeldMarker='Scheduled'))"
    > >
    > > I need to add the following but keep getting errors saying my SQL is
    > > incorrect.
    > >
    > > This is the additional part which should also be on this line:-
    > >
    > > AND ((PCBForecast.ShipETA) Between Now() And #12/30/3000#))"
    > >
    > > or another variant I require
    > >
    > > AND ((PCBForecast.ShipETA)=#12/31/3000#))"
    > >
    > > What is the correct way to write both of these, as I cannot get either
    > > to work in asp, yet they work in Access.
    > >

    > Two things:
    > 1. Datetime literals in SQL Server are delimited by quotes ('), not
    > hashmarks (#).
    > 2. There are no VBA functions such as Now() available in SQL Server. The
    > variant of sql used by SQL Server is called Transact-SQL (sometimes
    > called T-SQL for short). Transact-SQL has builtin functions for doing
    > many of the things VBA functions are used for in Jet. GETDATE() is the
    > T-SQL equivalent to Now().
    >
    > You may want to have a look at this article:
    > http://www.aspfaq.com/show.asp?id=2214
    >


    Did I miss something that points to SQL Server?

    >
    > --
    > 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.
    >
    >
    Anthony Jones, Sep 28, 2006
    #4
  5. Anthony Jones wrote:
    >
    > Did I miss something that points to SQL Server?
    >

    Nope. I misread it.

    To the OP:
    Show us the vbscript code that is failing. Better yet, read these:

    See here for a better, more secure way to execute your queries by using
    parameter markers:
    http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

    Personally, I prefer using stored procedures, or saved parameter queries
    as
    they are known in Access:

    Access:
    http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=

    http://groups.google.com/groups?hl=...=1&selm=



    --
    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], Sep 28, 2006
    #5
    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. Teep
    Replies:
    1
    Views:
    295
    Trevor Benedict R
    Jun 17, 2004
  2. KK
    Replies:
    2
    Views:
    498
    Big Brian
    Oct 14, 2003
  3. dyingrage

    CSS not working...need HELP!!! please!

    dyingrage, Nov 19, 2009, in forum: HTML and CSS
    Replies:
    0
    Views:
    522
    dyingrage
    Nov 19, 2009
  4. JeffQ888
    Replies:
    0
    Views:
    164
    JeffQ888
    May 23, 2006
  5. cooldv
    Replies:
    3
    Views:
    255
    WIlliam Morris
    Oct 6, 2003
Loading...

Share This Page