Invalid SQL - Please help .........

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

  1. David

    David Guest

    Hi,

    I cannot get the following (MS Access) SQL statement working in my asp
    page, please can anyone help me ? Thanks :)

    ------------------------------------------------
    <%

    strQuery = "SELECT Customers.CustomerName, OrderLines.JobNumber,
    Orders.PONumber, OrderLines.OrderQuantity,
    Sum(StockMovements.QtyShipped) AS SumOfQtyShipped,
    [OrderQuantity]-Sum([QtyShipped]) AS RemainingUnits,
    OrderLines.OrderNotes, Products.ProductRangeID, OrderLines.SelectAll,
    PCBForecast.ShipQty, Products.ProdCode, PCBForecast.HeldMarker,
    Products.ProductID, PCBForecast.ShipETA"


    strQuery = strQuery & " FROM (Customers INNER JOIN (Orders INNER JOIN
    (Products INNER JOIN (OrderLines INNER JOIN StockMovements ON
    OrderLines.JobNumber = StockMovements.JobNumber) ON (Products.ProductID
    = OrderLines.ProductID) AND (Products.ProductID =
    OrderLines.ProductID)) ON Orders.OrderID = OrderLines.OrderID) ON
    Customers.CustomerID = Orders.CustomerID) INNER JOIN PCBForecast ON
    OrderLines.JobNumber = PCBForecast.JobNumber"


    strQuery = strQuery & " GROUP BY Customers.CustomerName,
    OrderLines.JobNumber, Orders.PONumber, OrderLines.OrderQuantity,
    OrderLines.OrderNotes, Products.ProductRangeID, OrderLines.SelectAll,
    PCBForecast.ShipQty, Products.ProdCode, PCBForecast.HeldMarker,
    Products.ProductID, PCBForecast.ShipETA"


    strQuery = strQuery & " HAVING
    ((([OrderQuantity]-Sum([QtyShipped]))>=0) AND
    ((OrderLines.SelectAll)=Yes) AND ((PCBForecast.HeldMarker)="Scheduled")
    AND ((PCBForecast.ShipETA) Between Now() And #12/30/3000#));"

    Set RS = adoDataConn.Execute(strQuery)
    if RS.EOF then
    response.write "Boo Hoo"
    else
    response.write "Hooray"
    end if
    %>

    ----------------------------------------------------------------------------

    Thank you very much :)
     
    David, Sep 25, 2006
    #1
    1. Advertising

  2. David wrote:
    > Hi,
    >
    > I cannot get the following (MS Access) SQL statement working in my asp
    > page, please can anyone help me ? Thanks :)


    Please describe your symptoms without using the words "not working" or
    "can't get it to work".
    Is the subject of this post related to an error message you are receiving?
    If so, the only way to debug a sql statement is to look at it. All you have
    shown us in the vbscript code that is supposed to result in a sql statement.
    You need to look at the generated statement itself. The only way to do that
    is:

    Response.Write strQuery
    Response.End

    Run the page and look at the result. is it what you expect it to be? If
    you've done it correctly, you should be able to copy the statement from the
    browser window to the clipboard, open your database in Access, create a new
    query in Design View, switching to SQL View at the first opportunity, paste
    the statement into the sql window and run it it without modification (with a
    couple exceptions that don't apply here). Usually, Access will give you a
    better error message than the one supplied to vbscript by ADO.

    Now, I have looked at the vbscript, and I do see a problem with it here:

    .... ((PCBForecast.HeldMarker)="Scheduled") ...

    What do yyou expect the vbscript compiler to do when it encounters that
    double quote preceding Sch in the string? Well, the compiler cannot read
    your mind. All it can see is that you used a double quote to delimit the
    beginning of the string, and therefore, when it encounters a second double
    quote, it is expecting to end the string expression. But no, more characters
    appear after the quote so the compiler raises an error because it does not
    know how to deal with them.

    There are two ways to handle this:
    1. "Escape" the double quote, so the compiler treats it as a literal quote
    in the string rather than a delimiter. In vbscript (and sql for that matter)
    characters are escaped by doubling them. When the compile encounters two
    double quotes, it realizes that you want it to insert a literal value into
    the resulting string:
    .... ((PCBForecast.HeldMarker)=""Scheduled"") ...

    2. SQL allows the use of single quotes (apostrophes) to delimit literal
    string values in sql statements. So the above can be changed to:
    .... ((PCBForecast.HeldMarker)='Scheduled') ...
    Of course, this presents its own special problems, when the data being
    supplied contains apostrophes (O'Malley). So, the apostrophes in the data
    being supplied now have to be escaped.


    Now let me address the efficiency of this query you are creating: you have
    put all the filtering criteria in the HAVING clause. This is not a good
    idea. The only filtering criteria that should appear in the HAVING clause
    are those that concern aggregated columns (columns resulting from aggregate
    functions like SUM, COUNT, etc.) in the grouping query. All other criteria
    should be put in a WHERE clause that appears BEFORE the GROUP BY clause.
    This is so the records can be filtered BEFORE the grouping is applied.
    Maximizing a grouping queries performance involves minimizing the number of
    records that have to be grouped. So, your query should look like this:

    SELECT ...
    FROM ...
    WHERE OrderLines.SelectAll)=Yes AND PCBForecast.HeldMarker ='Scheduled' AND
    PCBForecast.ShipETA Between Now() And #12/30/3000#
    GROUP BY ...
    HAVING [OrderQuantity]-Sum([QtyShipped])>=0


    BTW, why not simplify this to:
    .... PCBForecast.ShipETA >= Now() ...


    Lastly, especially with a huge query statement like this, I would not build
    it in vbscript. i would create a saved query in Access and execute the saved
    query via ADO. See:
    http://groups.google.com/group/microsoft.public.inetserver.asp.db/msg/b3d322b882a604bd

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Sep 25, 2006
    #2
    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. =?Utf-8?B?dmE=?=
    Replies:
    4
    Views:
    2,858
    =?Utf-8?B?dmE=?=
    Feb 22, 2006
  2. KK
    Replies:
    2
    Views:
    648
    Big Brian
    Oct 14, 2003
  3. ecoolone
    Replies:
    0
    Views:
    783
    ecoolone
    Jan 3, 2008
  4. cooldv
    Replies:
    3
    Views:
    283
    WIlliam Morris
    Oct 6, 2003
  5. David
    Replies:
    4
    Views:
    162
    Bob Barrows [MVP]
    Sep 28, 2006
Loading...

Share This Page