oledbcommand and sql query problem
first of all aorry for my bad english
i have a problem below:
i was searching to change ibuyspy store of microsoft to use access db and not sqlserver
there is a function, This displays a list of other products
also purchased with a specified product. In sql server there is a stored procedure:
CREATE Procedure CustomerAlsoBought
(
@ProductID int
)
As
/* We want to take the top 5 products contained in
the orders where someone has purchased the given Product */
SELECT TOP 5
OrderDetails.ProductID,
Products.ModelName,
SUM(OrderDetails.Quantity) as TotalNum
FROM
OrderDetails
INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID
WHERE OrderID IN
(
/* This inner query should retrieve all orders that have contained the productID */
SELECT DISTINCT OrderID
FROM OrderDetails
WHERE ProductID = @ProductID
)
AND OrderDetails.ProductID != @ProductID
GROUP BY OrderDetails.ProductID, Products.ModelName
ORDER BY TotalNum DESC
GO
this stored procedure is call as parameter in oledbcommand
now in access i have change it with sql query:
Dim myCommand As OleDbCommand = New OleDbCommand("SELECT TOP 5 OrderDetails.ProductID, Products.ModelName, SUM(OrderDetails.Quantity) " & _
"FROM OrderDetails INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID WHERE OrderID IN (SELECT DISTINCT OrderID FROM OrderDetails WHERE ProductID = ?) AND (OrderDetails.ProductID <> ?) " & _
"GROUP BY OrderDetails.ProductID, Products.ModelName ORDER BY SUM(OrderDetails.Quantity) DESC", myConnection)
but this query don't work.
Work only if where clause is:
WHERE OrderID IN (SELECT DISTINCT OrderID FROM OrderDetails WHERE ProductID = ?) (DISPLAY PRODUCT PURCHASED WITH PRODUCTID)
or:
WHERE (OrderDetails.ProductID <> ?) (cut productid as product PURCHASED WITH PRODUCTID)
not work with both clause
please,
i hope that anyone can help me, thanks in advance
|
