sql like query does not work

Question

I am trying to retrieve video name from my database where the subject of the video is like my subject to search.

I try the like query but it was not return values.

Can you kindly give the suggestions.

I am using c# with sql server.

Here is my code.

if (con.State == ConnectionState.Open)
     con.Close();
     con.Open();
     string s1 = textBox1.Text;
     cmd = new SqlCommand("select Video_Name,subject from Videos where subject like  '%"+ s1 +" % ' " ,con);

   //cmd = new SqlCommand("select Video_Name from Videos where subject='"+ s1+"' ", con);
            SqlDataReader dr = cmd.ExecuteReader();
            ArrayList a = new ArrayList();
            label2.Visible = true;
            label3.Visible = true;

        //if (dr.Read())
        {
            while (dr.Read())
            {
                a.Add(dr[0].ToString());
            }
            foreach (string n in a)
            {
               comboBox1.Items.Add(n);
            }

            MessageBox.Show("Search succeded");
        }

Apart from SQL injection issues, there seems to be a stray *space* after the second `%` that could be messing things up. — Thorarin, Dec 27 '13 at 09:37
what's the purpose of commented out code? Have you checked, that the actual video is there? How would we know, that your `like` part would actually retrieve some data from db? — Ilya Ivanov, Dec 27 '13 at 09:38
@AgentFire The one before it not inside the single quotes. It's harmless. — Thorarin, Dec 27 '13 at 09:38

score 10 · Answer 1 · edited May 23 '17 at 12:11

Use a parameterized query

string s1 = textBox1.Text;
cmd = new SqlCommand("select Video_Name,subject from Videos where subject like @video",con);
cmd.Parameters.AddWithValue("@video", "%" + s1 + "%");

In this way you avoid the Sql Injection problem and your command text is more readable.
This will help also in formatting your command text without subtle typing errors and without the need to add quotes around strings. With a parameter, the burden to correctly quoting the parameter value is passed to the framework code that knows better how to do it correctly.

By the way, you could avoid the second loop setting the combobox.Datasource property to the ArrayList variable a

 comboBox1.Datasource = a;

score 2 · Answer 2 · answered Dec 27 '13 at 09:38

2

Maybe it is because you have a space after the last % and its '

"select Video_Name,subject from Videos where subject like  '%"+ s1 +">> % ' "<<

try something like this

"select Video_Name,subject from Videos where subject like '%"+s1+"%'"

answered Dec 27 '13 at 09:38

Marciano.Andrade

307
7
19

senthilkumar2185 · Answer 3 · 2013-12-27T09:49:03.610

0

cmd = new SqlCommand("select Video_Name,subject from Videos where subject like @vdnam",con);
cmd.Parameters.AddWithValue("@vdnam", "%" + VdName + "%");

if (dr.HasRows)
{
while (dr.Read())
{
a.Add(dr[0].ToString());
}

comboBox1.Datasource= a.List();


MessageBox.Show("Search succeded");
}

edited Dec 27 '13 at 09:49

answered Dec 27 '13 at 09:42

senthilkumar2185

2,536
3
22
36

score 0 · Answer 4 · edited May 23 '17 at 12:28

0

Steve's answer is of course right.

The main problem is here, your query parameter is inside single quotes. In quotes, SQL will recognize it as a string literal and never sees it as a parameter.

edited May 23 '17 at 12:28

Community

1
1

answered Dec 27 '13 at 13:16

Soner Gönül

97,193
102
206
364

sql like query does not work

4 Answers4