如何使用通配符在SQL查询与参数

问题描述

说我有一个基本的查询，像这样：

Say I have a basic query, something like this:

 SELECT holiday_name
 FROM holiday
 WHERE holiday_name LIKE %Hallow%

这在执行我的SQL查询窗格并返回万圣节的罚款。当我尝试用通配符'％'字符在我的代码以使用参数，则会出现我的问题。

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

但后来我收到一个错误说我还没有宣布标量@name。那么，你如何正确格式通配符将包含在查询参数？任何帮助表示赞赏！

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!

推荐答案

首先，你的的SqlParameter 的名字是 @name 不是名称。

First off, your SqlParameter name is @name not name.

二，我会。移动你的通配符

Second, I would move your wildcards.

因此，这将是这样的：

string CommandText = "SELECT holiday_name "
               + "FROM holiday "
               + "WHERE holiday_name LIKE @name;"
Connection = new SqlConnection(ConnectionString);

try
{
  string searchTerm = string.Format("%{0}%", HolidatyTextBox.Text);
  Connection.Open();
  Command = new SqlCommand(CommandText, Connection);
  Command.Parameters.Add(new SqlParameter("@name", searchTerm));
  var results = Command.ExecuteScalar();
}

这篇关于如何使用通配符在SQL查询与参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！