SQL Statement returns records in Access but not in ADO 
Author Message
 SQL Statement returns records in Access but not in ADO

I am using the following code in an asp page.
When I copy and paste the SQL statement into access it returns the records
under the code I have what it returns on the webpage.


<%
   Set Conn = Server.CreateObject("ADODB.Connection")
   MyConnStr = "DRIVER={Microsoft Access Driver
(*.mdb)};DBQ=C:\Windows\Desktop\MachineShopDatabase.mdb;"
   Conn.Open(MyConnStr)

   sql = "SELECT OrderedParts.DatePromised,
PurchaseOrders.PurchaseOrderNumber, Products.ProductNumber,
Products.ProductName, OrderedParts.UnitsOrdered,
Sum(PackedParts.QuantityShipped) AS SumOfQuantityShipped,
OrderedParts.PartsStatus FROM PurchaseOrders INNER JOIN (Products INNER JOIN
(OrderedParts LEFT JOIN PackedParts ON OrderedParts.OrderedPartsID =
PackedParts.OrderedPartsID) ON Products.ProductID = OrderedParts.ProductID)
ON PurchaseOrders.PurchaseOrderID = OrderedParts.PurchaseOrderID GROUP BY
OrderedParts.DatePromised, PurchaseOrders.PurchaseOrderNumber,
Products.ProductNumber, Products.ProductName, OrderedParts.UnitsOrdered,
OrderedParts.PartsStatus HAVING
(((Sum(PackedParts.QuantityShipped))<[UnitsOrdered])) OR
(((Sum(PackedParts.QuantityShipped)) Is Null)) ORDER BY
OrderedParts.DatePromised;"
   Set RS = Conn.Execute(sql)
%>
<B>Returned Record Count is: </B><%= RS.Recordcount %> <BR>
<B>The SQL Statement is: </B><BR>
<%= sql %>

The output of the webpage is:

Returned Record Count is: -1
The SQL Statement is:
SELECT OrderedParts.DatePromised, PurchaseOrders.PurchaseOrderNumber,
Products.ProductNumber, Products.ProductName, OrderedParts.UnitsOrdered,
Sum(PackedParts.QuantityShipped) AS SumOfQuantityShipped,
OrderedParts.PartsStatus FROM PurchaseOrders INNER JOIN (Products INNER JOIN
(OrderedParts LEFT JOIN PackedParts ON OrderedParts.OrderedPartsID =
PackedParts.OrderedPartsID) ON Products.ProductID = OrderedParts.ProductID)
ON PurchaseOrders.PurchaseOrderID = OrderedParts.PurchaseOrderID GROUP BY
OrderedParts.DatePromised, PurchaseOrders.PurchaseOrderNumber,
Products.ProductNumber, Products.ProductName, OrderedParts.UnitsOrdered,
OrderedParts.PartsStatus HAVING
(((Sum(PackedParts.QuantityShipped))<[UnitsOrdered])) OR
(((Sum(PackedParts.QuantityShipped)) Is Null)) ORDER BY
OrderedParts.DatePromised;

What is going on?

TIA
-Merk



Tue, 16 Sep 2003 06:53:37 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. SQL Statement does not return records

2. ADO SQL Search That Returns 0 Records When it Should Return 1

3. running sql code record by record instead of statement by statement

4. Counting records returned by SQL Select statement

5. SQL Like operator not returning records in code.

6. ADO does not always return Errors collection from SQL 2000

7. HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value

8. HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value

9. MS SQL raiseerror not returned to ADO error collection

10. MS SQL raiseerror not returned to ADO error collection

11. ADO does not return SQL Server Identity value

12. ADO Returns less records than Query run in MS Access

 

 
Powered by phpBB® Forum Software