Add '%' to string for SQL search

Question

At the moment i have a textbox and a button and i can read the textbox fine and it searches the databse for say "apple"

but if there is a result called "red apple" it will not return it.

I have tried

string getTheBox = (this.searchBox.Text);

string request = "%" +  getTheBox + "%";

But it doesn't seem to be working. This is with "request" being the string variable.

EDIT to include the SQL request part

SqlDataSource2.SelectCommand = "SELECT Recipe_Name FROM New_Recipe WHERE      [ingredient1]=@request

   SqlDataSource2.SelectParameters.Add(newParameter("request",System.TypeCode.String));
        SqlDataSource2.SelectParameters["request"].DefaultValue = request;

You should probably show your database access code as that's the piece that really matters. — Justin Helgerson
– Justin Helgerson, Commented Mar 29, 2013 at 15:52

Steve · Accepted Answer · 2013-03-29 15:58:17Z

6

The adding of % is correct, but you need to change your sql query

you need to use the LIKE operator

for example THE QUERY could be

"SELECT Recipe_Name FROM New_Recipe WHERE ingredient1 LIKE @request"

and your code

   string request = "%" +  getTheBox + "%";
   string sqlText = "SELECT Recipe_Name FROM New_Recipe WHERE ingredient1 LIKE @request";
   using(SqlConnection cn = GetSqlConnection())
   {
       cn.Open();
       using(SqlCommand cmd = new SqlCommand(sqlText, cm);
       {
            cmd.Parameters.AddWithValue("@request", request);
            SqlDataReader dr = cmd.ExecuteReader();
            while(dr.Read())
            {
             ......
            }
       }
   }

edited Mar 29, 2013 at 15:58

answered Mar 29, 2013 at 15:52

Steve

217k22 gold badges242 silver badges296 bronze badges

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

Comments

Olly · Accepted Answer · 2013-03-29 15:53:33Z

3

Please post your SQL query too. Perhaps you need to change WHERE FruitName = @FruitName to WHERE FruitName LIKE @FruitName

answered Mar 29, 2013 at 15:53

Olly

6,04634 silver badges60 bronze badges

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

This is a horrible idea, as anyone can run sql injection. You probably want something akin to Sqlcommand.Prepare

As it will let you set safer arguements. And have two words.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Mar 29, 2013 at 15:53

Jakob Bowyer

34.8k8 gold badges80 silver badges91 bronze badges

Comments

Dustin Kingen · Accepted Answer · 2013-03-29 16:28:20Z

I usually have a few helper functions to add like to my queries depending on what I need done.

public List<T> GetRecipesThatContain<T>(string ingredient)
{
    const string commandText = "SELECT Recipe_Name FROM New_Recipe WHERE ingredient1 LIKE @SearchTerm";
    var searchTerm = Contains(ingredient);

    using(var connection = GetSqlConnection())
    {
        connection.Open();

        using(var command = new SqlCommand(commandText, connection);
        {
            command.Parameters.AddWithValue("@SearchTerm", searchTerm);

            using(var reader = command.ExecuteReader())
            {
                var results = new List<T>();

                while(reader.Read())
                {
                    // Get results
                    // results.Add(result);
                }

                return results;
            }
        }
    }
}

private string StartsWith(string searchTerm)
{
    return string.Format("{0}%", searchTerm);
}

private string EndsWith(string searchTerm)
{
    return string.Format("%{0}", searchTerm);
}

private string Contains(string searchTerm)
{
    return string.Format("%{0}%", searchTerm);
}

Collectives™ on Stack Overflow

Add '%' to string for SQL search

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related