Say I have a basic query, something like this:
SELECT holiday_name
FROM holiday
WHERE holiday_name LIKE %Hallow%
This executes fine in my sql query pane and returns 'Halloween'. My problem occurs when I try to use parameters with with the wildcard '%' characters in my code.
SqlConnection Connection = null;
SqlCommand Command = null;
string ConnectionString = ConfigurationManager.ConnectionStrings["SQLdb"].ConnectionString;
string CommandText = "SELECT holiday_name "
+ "FROM holiday "
+ "WHERE holiday_name LIKE %@name%";
Connection = new SqlConnection(ConnectionString);
try
{
Connection.Open();
Command = new SqlCommand(CommandText, Connection);
Command.Parameters.Add(new SqlParameter("name", HolidayTextBox.Text));
var results = Command.ExecuteScalar();
}
catch (Exception ex)
{
//error stuff here
}
finally
{
Command.Dispose();
Connection.Close();
}
This throws an incorrect syntax error. I've tried moving the '%' to my parameter like so
Command.Parameters.Add(new SqlParameter("%name%", HolidayTextBox.Text));
but then I receive an error saying I haven't declared the scalar variable '@name'. So, how do you properly format wildcard characters to be included with query parameters? Any help is appreciated!
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: The percent sign (%) represents zero, one, or multiple characters. The underscore sign (_) represents one, single character.
To use wildcard characters in query criteria in Access, open the desired query in query design view. Then click into the “Criteria” row underneath the field into which to add the criteria. Type the criteria for which to search, using the appropriate wildcard characters.
whatever you do don't do this:
string CommandText = "SELECT holiday_name "
+ "FROM holiday "
+ "WHERE holiday_name LIKE '%'" + HolidayTextBox.Text + "'%'";
as that will open you up to sql injection, instead do this:
Command.Parameters.Add(new SqlParameter("@name", "%" + HolidayTextBox.Text + "%"));
you may like to know about Command.Parameters.AddWithValue, e.g:
Command.Parameters.AddWithValue("@name", "%" + HolidayTextBox.Text + "%");
First off, your SqlParameter
name is @name
not name
.
Second, I would move your wildcards.
So it would look like this:
string CommandText = "SELECT holiday_name "
+ "FROM holiday "
+ "WHERE holiday_name LIKE @name;"
Connection = new SqlConnection(ConnectionString);
try
{
var escapedForLike = HolidatyTextBox.Text; // see note below how to construct
string searchTerm = string.Format("%{0}%", escapedForLike);
Connection.Open();
Command = new SqlCommand(CommandText, Connection);
Command.Parameters.Add(new SqlParameter("@name", searchTerm));
var results = Command.ExecuteScalar();
}
Note that LIKE
requires special care when passing parameters and you need to escape some characters Escaping special characters in a SQL LIKE statement using sql parameters.
The %
s should be part of the search string, not the query.
string CommandText = "SELECT holiday_name "
+ "FROM holiday "
+ "WHERE holiday_name LIKE @name";
Connection = new SqlConnection(ConnectionString);
try
{
Connection.Open();
Command = new SqlCommand(CommandText, Connection);
string name = "%" + HolidayTextBox.Text + "%";
Command.Parameters.Add(new SqlParameter("@name", name));
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With