Asp.Net How can I pass parameters using an 'IN' clause in the query to refine a search

Question

I'm following Mikes post on the IN Clause on http://www.mikesdotnetting.com/article/156/webmatrix-database-helpers-for-in-clauses but I'm having trouble adding additional params. As per his post, I started with some checkboxes for the categories and worked fine but I added some other checkboxes for the brands but it's not working. I'm not sure how to pass additional params to the in clause.

The idea of what I want to accomplish is to have a side bar with different product filters (categories, brands, age, price, etc) and as the user clicks it will update the results. Can someone please help me on this one?

This is the code i'm working with but this gives errors:

// for the categories
var cTemp = Request["categoryId"].Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
var cParms = cTemp.Select((s, i) => "@" + i.ToString()).ToArray();
var cIn = string.Join(",", cParms);

// for the brands
var bTemp = Request["brandId"].Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
var bParms = bTemp.Select((s, i) => "@" + i.ToString()).ToArray();
var bIn = string.Join(",", bParms);

var sql = "SELECT DISTINCT P.ProductID, J.CategoryID, J.BrandID, P.ProductName, P.Price, " +
       "FROM Products P " +
       "JOIN Junction J ON J.ProductID = P.ProductID " +
       "WHERE J.CategoryID IN ({0}) OR J.BrandID IN ({1})";

var products = db.Query(String.Format(sql, cIn, bIn), cTemp, bTemp);

Answer 1

1. Inspect the value of String.Format(sql, cIn, bIn) - make it a separate string variable. You will see that you have duplicate parameter names in this SQL expression: @0, @1, etc.

2. When calling db.Query, do:

db.Query(<final sql statement>, allTemp)

where allTemp is concatenation of cIn and bIn.

Item 2 is the cause of the error that you see.