Must declare the scalar variable @param problem

Question

Newbie alert!

Error:

Must declare the scalar variable "@param2".

Must declare the scalar variable "@param2" (twice for two param2's)

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

and

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

TextBox1 value will be passed by user. I have searched solutions for around 6 hours... and still stuck up with this problem. Any solutions please?

Using VS2008 with MS SQL server 2008 R2 connection.

EDIT1: GIVING THE COMPLETE CODE.::

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


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


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

please note: I am binding it to a GridView control. This WORKS if I hardcode the value of the @param2 in the query.

EDIT2: A DIFFERENT APPROACH WITH DIFFERENT ERROR:

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

....getting new error

Could not find server 'System' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers

System.Data.SqlClient.SqlException: Could not find server 'System' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Answer 1

You need to use @param2 as a "stand-alone" parameter - do not pack it into a string!

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

If you want to search for a string with % at the beginning and end, you need to supply that into the value of @param2

Also: your query might work a lot better if you break up those subselects and use a single SQL statement using JOIN's to join together the tables...

Update: your approach has a very basic flaw: you seem to expect that if you use a parametrized query in the SqlCommand you'll get out the full SQL statement with the parameter value filled in when accessing the SearchAll.CommandText - that is simply not the case - the parameter @param2 will not be substituted with its value!

So basically, you cannot do this the way you're doing it right now - what you need to do is pass back a SqlCommand instance - not just a string! That will never work

Update #2: you need to do something like this:

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

and

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

Answer 2

I know this is an old question but I ran across it when trying to remember how to accomplish this same thing and I have a solution. Now that I read Sai Kalyan Akshinthala's most recent answer, I think he might have been hinting at the same thing.

The key is that when you add the parameter to the SQLDataSource's parameter collection, you need to leave the "@" off of the name. The error is stating that it doesn't see a parameter with the correct name to match the one passed in the SQL parameterized string. While the SQL parameter in the string must be named with "@", the matching SQLDataSource parameters should not use it.

Here is my C# code. It is a method used behind a web form that provides a flexible search of an article database.

protected void CreateArticleSearch()
{
    // Declare the base query and start the WHERE clause.
    string articleQuery = "SELECT DisplayTitle, Summary, CreateDate, ArticleID FROM Articles ";
    string whereClause = "WHERE ";

    try
    {
        // Important, clear the parameters first.
        Articles.SelectParameters.Clear();

        // Test the field to see if there's anything there.
        if (textTitle.Text.Length > 0)
        {
            // If there is a value, add to the WHERE clause and add a parameter.
            whereClause += "DisplayTitle LIKE @ArticleTitle ";
            Articles.SelectParameters.Add("ArticleTitle", "%" + textTitle.Text + "%");
        }

        // Do the same for each subsequent field except test to see if the
        // WHERE clause already holds something and add AND as necessary.
        if (textSummary.Text.Length > 0)
        {
            if (whereClause == "WHERE ")
                whereClause += "Summary LIKE @ArticleSummary ";
            else
                whereClause += "AND Summary LIKE @ArticleSummary ";

            Articles.SelectParameters.Add("ArticleSummary", "%" + textSummary.Text + "%");
        }

        // Test WHERE clause to see if it contains anything.
        // Add it to the base query if it does.
        if (whereClause.Length > 6)
            articleQuery += whereClause;

        // Specify the command type for the SQLDataSource and attach the query.
        Articles.SelectCommandType = SqlDataSourceCommandType.Text;
        Articles.SelectCommand = articleQuery;

    }
    catch
    {
        throw;
    }
}

Then, all that remains is to to a DataBind on the GridView that's being supplied by the SQLDataSource. I've used this in a few applications and it works great.

Andrew Comeau

Answer 3

you concatenating parameter to your query that's wrong in your query

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

Answer 4

First your declaration of param2 is useless: SqlParameter @param2 = new SqlParameter() If you were to use it - try to make it something a lot more meaningful such as documentName Second - try removing one of the parameters? does it work? if you add it a second time does it fail? if so then change the name for the second one and add it as a separate parameter.

Answer 5

You, are declaring a Parameter with name param2 and using @param2, so its getting stuck up. Correct it and try.