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

[David]

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
%>

 

[Bob Barrows [MVP]]

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