CODEWITHSUNDEEP
c#asp.netsql
Sohail
Sohail

Reputation: 574

SQL parameterized query not showing result

I have following function in my DataAcess class, but it is not showing any result.
My code is as follow: 

public List<Products> GetProduct(string productName)
 {
    System.Data.DataSet ds = null;
    db = DBWrapper.GetSqlClientWrapper();
    db.ClearParameters();
    db.AddParameter(db.MakeInParam("@ProductName", DbType.String, 30, productName));
    string query = @"SELECT ProductId   
                     FROM [Products]   
                     WHERE Name LIKE '%@ProductName%'";
    ds = db.GetDataSet(query);
    db.ClearParameters();
        // Rest of Code
 }

I also tried: 

string query = @"SELECT ProductId    
                 FROM [Products]   
                 WHERE Name LIKE '%"+"@ProductName"+"%'";

But it runs fine without parameterized like: 

string query = @"SELECT ProductId  
                 FROM [Products]   
                 WHERE Name LIKE '%"+productName+"%'";

How to write this with parameterized using @ProductName???

Upvotes: 3

Views: 302

Answers (3)

Soner G&#246;n&#252;l
Soner G&#246;n&#252;l

Reputation: 98750

You should use 

LIKE '%' + @ProductName + '%'

instead of

LIKE '%@ProductName%'

Why? Because in query, your parameter is inside quotes. In quotes, SQL will recognize it as a string literal and never sees it as a parameter.

As an alternative, you can use your % % part in your AddParameter method as Damien_The_Unbeliever mentioned.

Upvotes: 7

Ahmed Salman Tahir
Ahmed Salman Tahir

Reputation: 1779

Rather than adding a parameter, you can also use:

string query = String.Format("Select ProductId FROM Products where Name LIKE '{0}'", productName);

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239694

Try, instead:

db.AddParameter(db.MakeInParam("@ProductName", DbType.String, 30, "%" + productName + "%"));
string query = @"SELECT ProductId   
                 FROM [Products]   
                 WHERE Name LIKE @ProductName";

SQL doesn't look for parameters inside of literal strings. So you can make the parameter be the entire string argument for the LIKE operator.

Upvotes: 3

Related Questions