使查询与参数和“喜欢"一起使用. [英] Getting query to work with parameter and "like"

问题描述

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

I've seen many questions regarding using parameters with Sql queries and "like," but I've tried every way I've seen to code it and still can't get my query to give results. If I put a value in the query itself, it runs fine. When I run the first query listed I get the error "Must declare the scalar variable "@Search" but I thought I did that with the cmd.Parameters.AddWithValue statement. Can anyone see what I might be doing wrong? Any help is appreciated.

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

推荐答案

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

In your code above you aren't using the parameter in the SqlDataAdapter, in the code below you will use the SqlDataAdapter in the command.

    //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**

如果您不想使用参数化查询，这将起作用:

If you would like to not use a parameterized query this will work :

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

这篇关于使查询与参数和“喜欢"一起使用.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！