How to specify variable with multiple values

[samuel_1991]

Hi I have a ASP.Net code below that need to query with MS SQL database.

"select SUM(ShopCartItem.Price-OfferedPrice) As Discount from ShopCartItem " & _
"Inner Join Product " & _
"On ShopCartItem.ProductID = Product.ProductID " & _
        "Where(ShopCartItem.ShopCartID = @ShopCartID) And ShopCartItem.ProductID != @ProductID"

The MS SQL query is effectively:

select SUM(ShopCartItem.Price-OfferedPrice) As Discount 
from ShopCartItem
Inner Join Product
On ShopCartItem.ProductID = Product.ProductID
Where(ShopCartItem.ShopCartID = @ShopCartID) And ShopCartItem.ProductID != @ProductID

Where @ShopCartID is a particular ShopCart (Fixed value, not a issue) and @ProductID is something variable values where it takes its value from another dataset.

The rationale for this code is because I need to get ProductIDs that are not eligible for discount due to the fact that the customer has bought the same item before during the offer peroid.

So my question how do I assign @ProductID to all values from another dataset. The dataset has only 1 column.

[padtes]

I am not sure how you are loading the dataset that has previously purchased products for the customer. Can you pass that customer ID as parameter and re-query that in you query and use something like ShopCartItem.ProductID not in (select ProductID from pastPurchase where custID=@custID) May be there are more paramters like promotion ID or date etc.

Other option is (a little ugly) is to use a string for whole query and not in will take whole comma seperated product ID string.
for ex.

string sql = "select ... from " 
  + " and productID not in (" + previousProdCSV + ")";

//.executeNonquery(sql)

or same can be passed as param to MS sql query and use exec(@sql) in same fashion.

[samuel_1991]

I am not sure how you are loading the dataset that has previously purchased products for the customer. Can you pass that customer ID as parameter and re-query that in you query and use something like ShopCartItem.ProductID not in (select ProductID from pastPurchase where custID=@custID) May be there are more paramters like promotion ID or date etc.

Other option is (a little ugly) is to use a string for whole query and not in will take whole comma seperated product ID string.
for ex.

string sql = "select ... from " 
  + " and productID not in (" + previousProdCSV + ")";

//.executeNonquery(sql)

or same can be passed as param to MS sql query and use exec(@sql) in same fashion.

I see, meanwhile I will try the ugly option first.

It is possible to parse in the customer id due to the fact that the @Product comes from a dataset with this query

select ShopCartItem.ProductID from ShopCartItem
Inner Join Product 
On ShopCartItem.ProductID = Product.ProductID
Inner join OrderData
On ShopCartItem.ShopCartID = OrderData.ShopCartID 
Inner join ShopCart
On ShopCartItem.ShopCartID = ShopCart.ShopCartID
Where ShopperID = @ShopperID And Offered = 1 And GETDATE() Between OfferStartDate And OfferEndDate"

Where the ShopperID is the CustomerID, Offered = 1 means item on offer (Offered = 0 is item is not on offer on the moment)

[samuel_1991]

I am not sure how you are loading the dataset that has previously purchased products for the customer. Can you pass that customer ID as parameter and re-query that in you query and use something like ShopCartItem.ProductID not in (select ProductID from pastPurchase where custID=@custID) May be there are more paramters like promotion ID or date etc.

Other option is (a little ugly) is to use a string for whole query and not in will take whole comma seperated product ID string.
for ex.

string sql = "select ... from " 
  + " and productID not in (" + previousProdCSV + ")";

//.executeNonquery(sql)

or same can be passed as param to MS sql query and use exec(@sql) in same fashion.

Base on his idea, I have tweaked the query to:

Select SUM(ShopCartItem.Price-OfferedPrice) As Discount From ShopCartItem
Inner Join ShopCart
On ShopCart.ShopCartID = ShopCartItem.ShopCartID 
Inner Join Product
On ShopCartItem.ProductID = Product.ProductID
Where Offered = 1 And GETDATE() Between OfferStartDate And OfferEndDate And
ShopCartItem.ProductID not in 
(select ShopCartItem.ProductID from OrderData 
Inner Join ShopCartItem
On OrderData.ShopCartID = ShopCartItem.ShopCartID 
Inner Join Product
On ShopCartItem.ProductID = Product.ProductID
where ShopperID=1 And Offered = 1 And GETDATE() Between OfferStartDate And OfferEndDate)

that can check which item is currently on offer, and should provide a total of how much rebate with due consideration on the limitation that 1 customer can only buy 1 item on offer price during the peroid for my ComputeDiscount function. (There are 2 items on offer, 1 of the item is by default -- SQL Create database time already state the customer has buy this stuff @ discount price, so the other i still need to give rebate)

Thank you ! :D

[ChrisPadgham]

I don't think you should use GETDATE, you should hold the date of purchase in ShopCart otherwise when you run this query later you will get a different result.

[samuel_1991]

I don't think you should use GETDATE, you should hold the date of purchase in ShopCart otherwise when you run this query later you will get a different result.

Thanks for the reminder! However, the database was created such that it holds the order date (Known as after checkout) and date created the shopping cart (What if someone purchase the first item regardless if on offer or not on 31 Dec 2009, 11:59pm and checkout at 1 Jan 2010 12:02am issue)

Nevertherless, I had created a similar version to check for purchase history. That is using the Order date instead of GetDate()