asp.net - 必须声明标量变量@param问题

Question

新手警报!

错误:


Must declare the scalar variable "@param2".





Must declare the scalar variable "@param2"
(两个 param2 两次)

protected void Button1_Click(object sender, EventArgs e)
{
   SqlDataSource ds1 = new SqlDataSource(GetConnectionString(), GetSelectionString());
   GridView1.DataSource = ds1;
   GridView1.DataBind();
}

和



<pre> protected string GetSelectionString()
{
string SearchString = TextBox1.Text.ToString();
if (RadioButtonList1.SelectedValue == "ALL")
{
SqlParameter @param2 = new SqlParameter();
SqlCommand SearchAll = new SqlCommand("SELECT Document_Name, Document_Summary FROM Document_Details WHERE (Document_Id IN (SELECT Document_Id FROM Search_Index WHERE (Tag_Id IN (SELECT DISTINCT Tag_Id FROM Tags WHERE (Tag_Name LIKE '%'+@param2+'%'))))) UNION SELECT Document_Name, Document_Summary FROM Document_Details AS Document_Details_1 WHERE (Document_Name LIKE '%'+@param2+'%')");
SearchAll.Parameters.AddWithValue("@param2", SearchString.ToString());
return (string)SearchAll.CommandText.ToString();
}
</pre>



TextBox1 值将由用户传递。我已经搜索了大约 6 个小时的解决方案......但仍然坚持这个问题。请问有什么解决办法吗？

通过 MS SQL Server 2008 R2 连接使用 VS2008。

编辑1:给出完整的代码。::

<p></p>

<p>protected string GetSelectionString()
    {
        string SearchString = "%";
        SearchString = SearchString+ TextBox1.Text.Trim().ToString();
        SearchString =SearchString+ "%";</p>

<pre><code>    if (RadioButtonList1.SelectedValue == "ALL")
    {
        SqlParameter @param2 = new SqlParameter();
        SqlCommand SearchAll = new SqlCommand("SELECT Document_Name, Document_Summary FROM Document_Details WHERE (Document_Id IN (SELECT Document_Id FROM Search_Index WHERE (Tag_Id IN (SELECT DISTINCT Tag_Id FROM Tags WHERE (Tag_Name LIKE @param2))))) UNION SELECT Document_Name, Document_Summary FROM Document_Details AS Document_Details_1 WHERE (Document_Name LIKE @param2)");
        SearchAll.Parameters.AddWithValue("@param2", SearchString.ToString());
        return (string)SearchAll.CommandText.ToString();
    }
    if (RadioButtonList1.SelectedValue == "FILENAMES")
    {
        SqlParameter param2 = new SqlParameter();

        SqlCommand SearchFileName = new SqlCommand("SELECT Document_Name, Document_Summary FROM Document_Details WHERE (Document_Name LIKE @param2)");
        SearchFileName.Parameters.AddWithValue("@param2", SearchString.ToString());
        return (string)SearchFileName.CommandText.ToString();
    }
</code></pre>

<p>protected void Button1_Click(object sender, EventArgs e)
    {
        SqlDataSource ds1 = new SqlDataSource(GetConnectionString(), GetSelectionString());
        GridView1.DataSource = ds1;
        GridView1.DataBind();
    }
</p>




请注意:我将其绑定(bind)到 GridView 控件。如果我在查询中对 @param2 的值进行硬编码，则此方法有效。

EDIT2:具有不同错误的不同方法:

tried it this way, 
SqlCommand temp1 = GetSelectionString();
string temp2 = temp1.CommandText.ToString();
SqlDataSource ds1 = new SqlDataSource(GetConnectionString(), temp1.ToString());
GridView1.DataSource = ds1;
GridView1.DataBind();

....出现新错误

在 sys.servers 中找不到服务器“系统”。验证是否指定了正确的服务器名称。如有必要，执行存储过程 sp_addlinkedserver 将服务器添加到 sys.servers

System.Data.SqlClient.SqlException:在 sys.servers 中找不到服务器“系统”。验证是否指定了正确的服务器名称。如有必要，执行存储过程 sp_addlinkedserver 将服务器添加到 sys.servers。

Answer 1

您需要使用@param2作为“独立”参数 - 不要不要将其打包到字符串中!

SqlCommand SearchAll = new SqlCommand(
   "SELECT Document_Name, Document_Summary FROM Document_Details 
    WHERE (Document_Id IN 
       (SELECT Document_Id FROM Search_Index 
        WHERE (Tag_Id IN (SELECT DISTINCT Tag_Id 
                          FROM Tags 
                          WHERE Tag_Name LIKE @param2)))) 
    UNION 
    SELECT Document_Name, Document_Summary FROM Document_Details AS Document_Details_1 
    WHERE Document_Name LIKE @param2");

如果要搜索开头和结尾带有 % 的字符串，则需要将其提供给 @param2 的值

此外:如果您分解这些子选择并使用单个 SQL 语句(使用 JOIN 将表连接在一起)，您的查询可能会工作得更好...

更新:你的方法有一个非常基本的缺陷:你似乎期望如果你在SqlCommand中使用参数化查询，你会得到完整的SQL语句访问 SearchAll.CommandText 时填写的参数值 - 事实并非如此 - 参数 @param2 将不会被替换为它的值(value)!

所以基本上，您不能按照现在的方式执行此操作 - 您需要做的是传回 SqlCommand 实例 - 不只是一个字符串!那永远行不通

更新#2:你需要做这样的事情:

protected void Button1_Click(object sender, EventArgs e)
{
   // grab search string from web UI
   string searchString = "%" + TextBox1.Text.Trim() + "%";

   // get connection string
   string connectionString = GetConnectionString();

   SqlDataSource ds1 = new SqlDataSource(connectionString);

   // get the SqlCommand to do your SELECT
   ds1.SelectCommand = GetSelectCommand(connectionString, searchString);

   GridView1.DataSource = ds1;
   GridView1.DataBind();
}

和

protected SqlCommand GetSelectCommand(string connectionString, string searchValue)
{
   // define query string - could be simplified!
   string queryStmt = "SELECT Document_Name, Document_Summary FROM Document_Details WHERE (Document_Id IN (SELECT Document_Id FROM Search_Index WHERE (Tag_Id IN (SELECT DISTINCT Tag_Id FROM Tags WHERE Tag_Name LIKE @param2)))) UNION SELECT Document_Name, Document_Summary FROM Document_Details AS Document_Details_1 WHERE Document_Name LIKE @param2";

   // set up a SqlCommand based on the query string and the connection string passed in       
   SqlCommand cmd = new SqlCommand(queryStmt, connectionString);

   // define parameter
   cmd.Parameters.Add("@param2", SqlDbType.VarChar, 100);

   // set value for parameter
   cmd.Parameters["@param2"].Value = searchValue;

   // pass back SqlCommand to fill the data source
   return cmd;
}