CODEWITHSUNDEEP
c#sql-server
user3125130
user3125130

Reputation: 21

sql like query does not work

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

Upvotes: 2

Views: 200

Answers (4)

Steve
Steve

Reputation: 216243

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;

Upvotes: 10

Soner Gönül
Soner Gönül

Reputation: 98740

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.

Upvotes: 0

senthilkumar2185
senthilkumar2185

Reputation: 2566

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

Upvotes: 0

Marciano.Andrade
Marciano.Andrade

Reputation: 306

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+"%'"

Upvotes: 2

Related Questions