Adding Parameters to SQL Server SELECT queries

Question

Try as I might, I cannot properly add parameters to my SQL Server SELECT query. The code works if I change the query to a single string (without parameters) so I know it has to be the SqlCommand parameters. Can anyone spot where I'm going wrong?

protected void getSQLData()
{
    string connString = WebConfigurationManager.ConnectionStrings["RegionalHistoryCenterConnectionString"].ConnectionString; /*This can be found in the Web.config file*/

    SqlConnection myConnection = new SqlConnection(connString);
    int recordCount;

    SqlCommand myCommand = generateSQLQuery(myConnection);

    /*The using statement ensures that Dispose is called even if an exception occurs while you are calling methods on the object.*/
    using (myConnection)
    {
        try
        {
            // Checking to see if connection is open.  It should not be, USING should close the connection automatically
            if (myConnection.State != ConnectionState.Open)
            {
                myConnection.Open();
            }

            //lblmsg.Text = string.Empty; // clear any prevous message to prevent confusion.

            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                sda.SelectCommand = myCommand;
                dset = new DataSet();
                sda.Fill(dset);
                GridViewRecords.DataSource = dset;
                GridViewRecords.DataBind();
            }
        }//END try
        catch (Exception ex)        //Good for security purposes, keeps server details from being listed
        {
            LabelMsg.Text = ex.Message;
        }
    }//END using
}//end getSQLData()

/// <summary>
/// </summary>
/// <param name="theConnection"></param>
/// <returns></returns>
private SqlCommand generateSQLQuery(SqlConnection theConnection)
{
    string mySelectQuery = "SELECT * FROM ManuscriptsCollection ";

    bool hasKeyword = !string.IsNullOrEmpty(TextBoxKeywords.Text);

    // If the keyword box is empty and the user was doing a keyword search, throw an error
    if (hasKeyword == false && queryType.Equals("search"))
    {
        LabelMsg.Text = "No search word was entered";
    }

    // If a keyword search is being performed
    if (hasKeyword && queryType.Equals("search"))
    {
        /*.HtmlEncode keeps input from being interpreted as HTML code.  This is for security's sake*/
        keyword = Server.HtmlEncode(TextBoxKeywords.Text);                   
        mySelectQuery += generateKeywordSelects(keyword);
    }

    // Order by immigrant's last name
    mySelectQuery += " ORDER BY Item ASC ";

    // Unless there is a browse of the entire index, add parameters to help prevent SQL Injection
    SqlCommand SelectCommand = new SqlCommand(mySelectQuery, theConnection);
    SelectCommand.Parameters.Add((new SqlParameter("@item", keyword)));
    SelectCommand.Parameters.Add((new SqlParameter("@snum", keyword)));
    SelectCommand.Parameters.Add((new SqlParameter("@fnum", keyword)));
    SelectCommand.Parameters.Add((new SqlParameter("@date", keyword)));
    SelectCommand.Parameters.Add((new SqlParameter("@notes", keyword)));

    // Testing
    //LabelMsg.Text = SelectCommand.Parameters["@item"].Value.ToString();
    LabelMsg.Text = SelectCommand.CommandText;
    return SelectCommand;
}//END generateSQLQuery

/*Simply creates a string containing a keyword-select statement*/
private string generateKeywordSelects(string theKeyword)
{
    string keywordString = "WHERE ";
    //Item name
    keywordString += "Item LIKE '%@item%'";
    keywordString += " OR ";
    //Shelf and Box Number
    keywordString += "ShelfAndBoxNumber LIKE '%@sbnum%'";
    keywordString += " OR ";
    //File number
    keywordString += "FileNumber LIKE '%@fnum%'";
    keywordString += " OR ";
    //Date
    keywordString += "Date LIKE '%@date%'";
    keywordString += " OR ";
    //MISC Notes
    keywordString += "Notes LIKE '%@notes%'";
    return keywordString;
}

http://stackoverflow.com/questions/12312898/how-to-pass-int-parameters-in-sql-commandtext would be a similar question where the answer appears to be using a Stored Procedure instead of Command Text. — JB King, Oct 20 '15 at 21:21

score 3 · Accepted Answer · answered Oct 20 '15 at 21:25

The parameters cannot be included in a string as you're currently doing:

keywordString += "Notes LIKE '%@notes%'";
...
SelectCommand.Parameters.Add((new SqlParameter("@item", keyword)));

Instead, you'll have to include the SQL wildcards to the parameter value, and use:

keywordString += "Notes LIKE @notes";
...
SelectCommand.Parameters.Add((new SqlParameter("@item", '%' + keyword + '%')));

score 1 · Answer 2 · answered Oct 20 '15 at 21:27

You are searching for the actual string "@date" not using a parameter, parameters can not be inside of strings. Split the % and the parameter apart.

private string generateKeywordSelects(string theKeyword)
{
    string keywordString = "WHERE ";
    //Item name
    keywordString += "Item LIKE ('%' + @item + '%')";
    keywordString += " OR ";
    //Shelf and Box Number
    keywordString += "ShelfAndBoxNumber LIKE ('%' + @sbnum + '%')";
    keywordString += " OR ";
    //File number
    keywordString += "FileNumber LIKE ('%' + @fnum + '%')";
    keywordString += " OR ";
    //Date
    keywordString += "Date LIKE ('%' + @date + '%')";
    keywordString += " OR ";
    //MISC Notes
    keywordString += "Notes LIKE ('%' + @notes + '%')";
    return keywordString;
}

Adding Parameters to SQL Server SELECT queries

2 Answers2