I wrote an SQL SELECT statement that returns the entire table data rather then just the results that match my search

Question

I have the following code that runs on a button click:

protected void Button2_Click(object sender, EventArgs e)
        {
            String str = "SELECT * " +
                "FROM ConcernTicket INNER JOIN Employee " +
                "ON ConcernTicket.EmployeeReportedToID = Employee.EmployeeId " +
                "WHERE (Employee.FirstName LIKE '%' + @search2 + '%')";
            SqlCommand xp = new SqlCommand(str, vid);
           xp.Parameters.Add("@search2", SqlDbType.NVarChar).Value = 
           TextBox1.Text;

            vid.Open();
            xp.ExecuteNonQuery();
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = xp;
            DataSet ds = new DataSet();
            da.Fill(ds, "Employee.FirstName");
            GridView2.DataSource = ds;
            GridView2.DataBind();
            vid.Close();
    }

The problem I am facing is that the search runs with no errors but instead of just returning the results where the FirstName variable matches, it displays all current Concern Tickets. I am assuming it is a fairly simple fix with the SELECT statement, but for some reason I have not been able to figure out what is going wrong. I just started working with sql so I apologize that I am having such a silly issue, any help would be appreciated, thanks!

Why are you called `ExecuteNonQuery`? Your code looks like a query to me. — Gordon Linoff, May 09 '17 at 19:04
You should check what query are you sending to db. http://stackoverflow.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework — Juan Carlos Oropeza, May 09 '17 at 19:08
I would check what value of `@search2` you are passing to the database. If you are pulling back all results then you probably have a code error where you are sending `WHERE (Employee.FirstName LIKE %%)`. In English that's basically telling the parser that you don't care what the employees first name is. — , May 09 '17 at 19:12
Before you execute your query, basically as soon as your pass your parameter do this Debug.Print str and then open your immediate window do ?str and enter - you will see the actual query - if your SQL statement has the parameter, then paste it into a new query window in SQL Server to show what data will be selected — BobSki, May 09 '17 at 19:25

score 1 · Accepted Answer · answered May 09 '17 at 19:15

1

Check that TextBox1.Text is not empty. If it is empty, the query will be:

    WHERE (Employee.FirstName LIKE '%%')";

Also check that @search2 is being replaced properly. The + operator is not what you would expect in MySQL. Perhaps this is what you're looking for:

    "WHERE (Employee.FirstName LIKE '%@search2%')";

Hope that helps

answered May 09 '17 at 19:15

redolent

4,159
5
37
47

Thank you so much! I had copied the code from my first search box for my second one which should have been for TextBox2, but I had forgotten to change the ID of the text box. I had just been staring at it for so long assuming that I was overlooking something with the SELECT statement that I forgot something so simple. I greatly appreciate it! – SCarter May 09 '17 at 19:30

score 0 · Answer 2 · answered May 09 '17 at 19:50

your problem is not the SQL query. In fact you use ExecuteNonQuery() to extract select result. ExecuteNonQuery() just returns a single integer.Please use a code like this and let me know if the problem persists.

  string connetionString = null;
            SqlConnection connection ;
            SqlDataAdapter adapter = new SqlDataAdapter();
            DataSet ds = new DataSet();
            int i = 0;
            connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password";
            connection = new SqlConnection(connetionString);
            try
            {
                connection.Open();
                adapter.SelectCommand = new SqlCommand("Your SQL Statement Here", connection);
                adapter.Fill(ds);
                connection.Close();
                for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
                {
                    MessageBox.Show(ds.Tables[0].Rows[1].ItemArray[1].ToString());
                }
            }

I wrote an SQL SELECT statement that returns the entire table data rather then just the results that match my search

2 Answers2