CODEWITHSUNDEEP
c#asp.netrazorwebwebmatrix
Alvin
Alvin

Reputation: 109

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

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);

Upvotes: 1

Views: 822

Answers (2)

Igor
Igor

Reputation: 15893

  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.

Upvotes: 2

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

For the error you mentioned in the comment, try removing parameters from db.Query method.you are passing string array to sql parameters,which is not correct. Your method call should be this.

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

I think there is also error in your WHERE clause handling. Try something like this..

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 ";

var whereClause = ""
if(!string.IsNullOrEmpty(cIn)||!string.IsNullOrEmpty(bIn))
{
    whereClause = "WHERE";
    if(!string.IsNullOrEmpty(cIn))
    {
        whereClause+=" J.CategoryID IN ({0}) ";
        if(!string.IsNullOrEmpty(bIn))
        whereClause+=" OR ";
    }
    if(!string.IsNullOrEmpty(bIn))
    {
        whereClause+=" J.BrandID IN ({1}) ";
    }
}
sql = sql+whereClause;

Upvotes: 1

Related Questions