locked
Query with dynamic parameters works ok in SQL but not in c# code behind query RRS feed

  • Question

  • User-718146471 posted

    Hello all, I am trying to use some code from an earlier post of mine. I tried replying to it but the thread is locked due to age. I'm trying to make it so my web app offers a simple search engine. If I drop the query into a query window, tit works just fine. However, if I do this same query in asp.net c# code behind I get an error. The caught exception looks like this:

    {"The name 'SELECT * FROM [TABLE1] WHERE (Title like '%testing%' OR [Problem] like '%testing%' 
    OR [RootCause] like '%testing%' OR [Notes] like '%testing%' OR [Tags] like '%testing%') OR
    (Title like '%again%' OR [Problem] like '%again%' OR [RootCause] like '%again%' OR [Notes] like '%again%'
    OR [Tags] like '%again%')' is not a valid identifier."}

    The code:

                try
                {
                    StringBuilder sqlBuilder = new StringBuilder();
                    sqlBuilder.Append("SELECT * FROM [Table1] WHERE ");
                    foreach (string item in txtSearchTerm.Text.ToString().Split(' '))
                    {
                        sqlBuilder.AppendFormat("(Title like '%{0}%' OR [Problem] like '%{0}%' 
    OR [RootCause] like '%{0}%' OR [Notes] like '%{0}%' OR [Tags] like
    '%{0}%') OR ", item); } string sql = sqlBuilder.ToString(0, sqlBuilder.Length - 4); DataTable dt = new DataTable(); // Create DB Connection objects string read = "@searchQuery"; SqlCommand cmd = new SqlCommand(read, conn); cmd.Parameters.AddWithValue("@searchQuery", sql.ToString()); try { conn.Open(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dt); GridView1.DataSource = dt; GridView1.DataBind(); } catch (Exception ex) { lbStatus.Text = "An error has occurred. Development team has been notified.
    Please try again later or send email with the time and date of the error. Thank you."; } finally { conn.Close(); } } catch (Exception ex) { lbStatus.Text = "An error has occurred. Development team has been notified.
    Please try again later or send email with the time and date of the error.
    Thank you."; } finally { }

    Monday, November 6, 2017 9:33 PM

Answers

  • User2103319870 posted

    I think the issue is with the way you are passing the dynamic query to sql command. You have assigned "@searchQuery" to command text so at runtime the query which will get executed will be this "@searchQuery" not the actual query

    You can directly pass the string to sql command like below

    try
                {
                    StringBuilder sqlBuilder = new StringBuilder();
                    sqlBuilder.Append("SELECT * FROM [Table1] WHERE ");
                    foreach (string item in txtSearchTerm.Text.ToString().Split(' '))
                    {
                        sqlBuilder.AppendFormat("(Title like '%{0}%' OR [Problem] like '%{0}%' 
                        OR[RootCause] like '%{0}%' OR[Notes] like '%{0}%' OR[Tags] like '%{0}%') OR ", item);
                    }
                    string sql = sqlBuilder.ToString(0, sqlBuilder.Length - 4);
    
                    DataTable dt = new DataTable();
                    // Create DB Connection objects
                    //string read = "@searchQuery";
                    SqlCommand cmd = new SqlCommand(sql.ToString(), conn);
                    //cmd.Parameters.AddWithValue("@searchQuery", sql.ToString());
                    try
                    {
                        conn.Open();
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        da.Fill(dt);
                        GridView1.DataSource = dt;
                        GridView1.DataBind();
                    }
                    catch (Exception ex)
                    {
                        lbStatus.Text = "An error has occurred. Development team has been notified. 
                        Please try again later or send email with the time and date of the error.Thank you.";
                    }
                        finally
                        {
                            conn.Close();
                        }
                    }
                    catch (Exception ex)
                    {
                        lbStatus.Text = "An error has occurred. Development team has been notified. 
                        Please try again later or send email with the time and date of the error.
                    Thank you.";
                }
                        finally
                        {
                        }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 6, 2017 9:56 PM