将参数添加到SQL Server SELECT查询

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?

尽我所能，我无法正确地向SQL Server SELECT查询添加参数。如果我将查询更改为单个字符串（没有参数），代码可以工作，所以我知道它必须是SqlCommand参数。谁能发现我哪里出错？

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

2 个解决方案

#1

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:

相反，您必须将SQL通配符包含在参数值中，并使用：

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

#2

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.

您正在搜索不使用参数的实际字符串“@date”，参数不能在字符串内。拆分％和参数分开。

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

#1