Sql select query using like

Question

I am trying to run this code

 public long getTopicCountWithTag(String tag)
    {
        long count;
        query = " SELECT count(*) FROM [DB_us2].[dbo].[discns] where tags like '%@tags%'";
        try
        {
            com = new SqlCommand(query, con);
            com.Parameters.AddWithValue("@tags", tag);
            con.Open();          
            sdr = com.ExecuteReader();
            sdr.Read();
            count= sdr.GetInt32(0);

        }
        catch (Exception e)
        {
            count = -1;
            throw e;
        }
        finally
        {
            con.Close();
        }
        return count;
    }

its giving output 0 . So i try figure out what is the problem and run sample query on management studio but output is different its giving 1. After trying all permutation combination, i think problem is with this statement com.Parameters.AddWithValue("@tags", tag); might be possible @tags is not replaced in query.

What happens if you temporarily change the query to = @tags, i think the issue is with the '%tags%' syntax, see this question for further reading: stackoverflow.com/questions/14222900/… — JsonStatham
– JsonStatham, Commented Oct 25, 2013 at 8:03

Indi · Accepted Answer · 2013-10-25 07:56:47Z

10

I think your query should be

string query = "SELECT count(*) FROM [DB_us2].[dbo].[discns] where tags like @tags";

And add the wildcard to the parameter

com.Parameters.AddWithValue("@tags", "%" + tag + "%");

answered Oct 25, 2013 at 7:56

Indi

33.9k7 gold badges61 silver badges80 bronze badges

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

5 Comments

Hot Cool Stud Over a year ago

hello kaf your answer is correct but why its not working with above code whats wrong in that.

Yaugen Vlasau Over a year ago

unfortunatelli it is a bad advice... what happen if the tag variable takes the following value "'; GO; drop table [DB_us2].[dbo].[discns];"?

Indi Over a year ago

@YaugenVlasau: What is the bad advice here?

Yaugen Vlasau Over a year ago

SQL injection is possible

Indi Over a year ago

@YaugenVlasau: The whole idea of parameterised query is to stop sql injection and you are saying otherwise. Values passed through parameters are not evaluate as sql. OP is not running dynamic sql.

aads · Accepted Answer · 2013-10-25 08:08:25Z

0

Should be

 AddWithValue("@tags", "%" + tag + "%");

you have to bring %s inside AddWithValue

answered Oct 25, 2013 at 8:08

aads

2,5154 gold badges25 silver badges25 bronze badges

Comments

Yaugen Vlasau · Accepted Answer · 2013-10-25 08:17:36Z

0

query = " SELECT count(*) FROM [DB_us2].[dbo].[discns] where tags like '%'+ @tags + '%'";

and leave everething as it was.

answered Oct 25, 2013 at 8:17

Yaugen Vlasau

2,2381 gold badge20 silver badges41 bronze badges

Collectives™ on Stack Overflow

Sql select query using like

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related