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

stackoverflow.com/questions/12312898/… would be a similar question where the answer appears to be using a Stored Procedure instead of Command Text. — JB King
– JB King, Commented Oct 20, 2015 at 21:21

C.Evenhuis · Accepted Answer · 2015-10-20 21:25:24Z

3

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 + '%')));

answered Oct 20, 2015 at 21:25

C.Evenhuis

26.4k2 gold badges60 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Scott Chamberlain · Accepted Answer · 2015-10-20 21:27:24Z

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

Collectives™ on Stack Overflow

Adding Parameters to SQL Server SELECT queries

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related