SQL parameterized query not showing result

Question

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???

Community · Accepted Answer · 2017-05-23 11:45:52Z

7

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.

edited May 23, 2017 at 11:45

CommunityBot

11 silver badge

answered Dec 18, 2013 at 7:39

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Damien_The_Unbeliever · Accepted Answer · 2013-12-18 07:41:41Z

3

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.

answered Dec 18, 2013 at 7:41

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

1 Comment

Sohail Over a year ago

Thanx Damien, It also works but I like Soner's way easy (somehow) :)

Ahmed Salman Tahir · Accepted Answer · 2013-12-18 07:44:45Z

0

Rather than adding a parameter, you can also use:

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

answered Dec 18, 2013 at 7:44

Ahmed Salman Tahir

1,7771 gold badge17 silver badges26 bronze badges

1 Comment

Ahmed Salman Tahir Over a year ago

@Sohail - okay Soner came to your rescue :)

Collectives™ on Stack Overflow

SQL parameterized query not showing result

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related