使查询与参数和“赞”一起使用。

Question

我已经看到了许多有关在Sql查询中使用参数和“赞”的问题，但是我尝试了各种方法来对其进行编码，但仍然无法使我的查询给出结果。如果我在查询本身中输入一个值，它会很好地运行。当我运行列出的第一个查询时，出现错误“必须声明标量变量“
@Search”，但我认为我使用cmd.Parameters.AddWithValue语句做到了。有人可以看到我可能在做错什么吗？感谢您的任何帮助。

            //Declare the connection object
        SqlConnection Conn = new SqlConnection();
        Conn.ConnectionString = ConfigurationManager.ConnectionStrings["MyDatabase"].ConnectionString;

        //Connect to the db
        Conn.Open();

        //Define query

        //This query doesn't work
        string sql = "SELECT CustomerID, LastName, FirstName, Email, Password, Address1, Address2, City, State, Zip, Phone, Fax FROM Customer WHERE (State LIKE '%' + @Search + '%')";

        //This query doesn't work either
        string sql = "SELECT CustomerID, LastName, FirstName, Email, Password, Address1, Address2, City, State, Zip, Phone, Fax FROM Customer WHERE State LIKE @Search";

        //This query works
        string sql = "SELECT CustomerID, LastName, FirstName, Email, Password, Address1, Address2, City, State, Zip, Phone, Fax FROM Customer WHERE State LIKE 'MI'";

        //Declare the Command
        SqlCommand cmd = new SqlCommand(sql, Conn);

        //Add the parameters needed for the SQL query
        cmd.Parameters.AddWithValue("@Search", "%" + txtSearch.Text + "%");

        //Declare a SQL Adapter
        SqlDataAdapter da = new SqlDataAdapter(sql, Conn);

        //Declare a DataTable
        DataTable dt = new DataTable();

        //Populate the DataTable
        da.Fill(dt);

        //Bind the Listview
        lv.DataSource = dt;
        lv.DataBind();

        dt.Dispose();
        da.Dispose();
        Conn.Close();

Answer 1

在上面的代码中，没有使用SqlDataAdapter中的参数，在下面的代码中，将在命令中使用SqlDataAdapter。

    //This query doesn't work
    string sql = "SELECT CustomerID, LastName, FirstName, Email, Password, Address1, Address2, City, State, Zip, Phone, Fax FROM Customer WHERE (State LIKE @Search)";

    //Declare the Command
    SqlCommand cmd = new SqlCommand(sql, Conn);

    //Add the parameters needed for the SQL query
    cmd.Parameters.AddWithValue("@Search", "%" + txtSearch.Text + "%");

  //Declare a SQL Adapter
    SqlDataAdapter da = new SqlDataAdapter();

    **sa.SelectCommand = cmd**

如果您不想使用参数化查询，则可以使用：

 //Declare the connection object
    //This query doesn't work
    string sql = "SELECT CustomerID, LastName, FirstName, Email, Password, Address1, Address2, City, State, Zip, Phone, Fax FROM Customer WHERE (State LIKE '%" + **txtSearch.Text** + "%')";

  //Declare a SQL Adapter
    SqlDataAdapter da = new SqlDataAdapter(sql, conn);