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.

Yes it works if you **hard-code** your value - but it will **NEVER** work if you want to make the parameter configurable. — marc_s, Apr 29 '11 at 07:40
Updated my answer yet again - tried to provide a full solution. Adapt as needed. — marc_s, Apr 29 '11 at 08:18
Thanks Marc. I tried adapting your solution. But the same error... param not declared. I am giving up on injection. I will do manual term checking and removing all non alphanumeric. Thanks anyway to everyone who helped me learn :) — Ranjanmano, Apr 29 '11 at 12:06

marc_s · Accepted Answer · 2011-04-29T08:18:03.797

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

Tried it. Does not work either :( Sent an input like "%hello%" to the @param2. Even then the same error. — Ranjanmano, Apr 29 '11 at 07:18
@Ranjanmano: your approach is fundamentally flawed - you cannot expect the SqlCommand's `.CommandText` to contain the actual SQL statement with the parameter value filled in - it doesn't work that way in ADO.NET ! You need to pass back the `SqlCommand` object and use that - do not rely on a string! — marc_s, Apr 29 '11 at 07:23
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 Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers — Ranjanmano, Apr 29 '11 at 07:32
as per docs/samples available using %+@param should be perfectly usable SELECT * FROM Catalog WHERE Description LIKE '%' + @SearchTerm + '%' The other alternative is to add %XX% before sending the parameter over such a — Adam Tuliper, Apr 29 '11 at 17:57
Solved using the connection string name as the connection string and then assign it to the declared SqlConnection — HShbib, Nov 29 '12 at 13:47

score 3 · Answer 2 · answered Jun 18 '12 at 19:39

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

I think the `@` prefix is optional IIRC. Did you try your code with it as well? — Martin Smith, Jun 18 '12 at 19:40
Yes. That was the final thing that I fixed to get it working. If you add a parameter to the SQLDataSource object on the page and inspect the object's markup, the parameter is named without the @. — Andrew Comeau, Jun 18 '12 at 19:53

Muhammad Akhtar · Answer 3 · 2011-04-29T06:05:28.027

2

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

edited Apr 29 '11 at 06:05

answered Apr 29 '11 at 05:34

Muhammad Akhtar

51,913
37
138
191

tried this. This sends the value '%@param2%' to the database. It doesn't think of param as a parameter. – Ranjanmano Apr 29 '11 at 05:42

score 1 · Answer 4 · answered Apr 29 '11 at 06:02

1

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.

answered Apr 29 '11 at 06:02

Adam Tuliper

29,982
4
53
71

removed the declaration also. Tried it with one parameter alone. Even then it does not work. Same error. – Ranjanmano Apr 29 '11 at 07:19

score 0 · Answer 5 · answered Apr 29 '11 at 05:33

0

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

answered Apr 29 '11 at 05:33

Sai Kalyan Kumar Akshinthala

11,704
8
43
67

Must declare the scalar variable @param problem

5 Answers5