Question 88330 in ASP.NET Question posted in
The official documentation can be found here.

Why does my SQL query not work as I want to? – Asp.net

I am working on a project for user administration in ASP.NET Web App (Framework 4.8). In the code have made search box where I want to be able to search for Name,EmployeeNumber and if they are Excluded or not. I use it as a filter so I do not need to scroll through all the pages in the gridview. I have tried some changes in the query but it does not seem to make a difference. I am not so familiar with these kind of SQL queries just so you know.

So I wonder if someone can simply explain to me exactly what is wrong with my query and why?

PopulateGridView:

 void PopulateGridView()
        {
            string find = "select * from TBL_USERS where (Name like '%' + @Name + '%') or (employee like '%' + @Employee + '%') and (case when excluded =1 then 'True' else 'False' end like '%false%')";
            SqlCommand comm = new SqlCommand(find, con);
            comm.Parameters.Add("@Name", SqlDbType.VarChar, 255).Value = TextBox1.Text;
            comm.Parameters.Add("@Employee", SqlDbType.VarChar, 32).Value = TextBox1.Text;
            comm.Parameters.Add("@Excluded", SqlDbType.Bit, 1).Value = 1;
            con.Open();
            comm.ExecuteNonQuery();
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = comm;
            DataSet ds = new DataSet();
            da.Fill(ds, "Name");
            da.Fill(ds, "Employee");
            da.Fill(ds, "Excluded");
            gvTestUsers.DataSource = ds;
            gvTestUsers.DataBind();
            con.Close();
            
        }
Tags:

2

Answers


  1. 0

    Your primary issue appears to be the lack of parenthesis around the OR. It’s not entirely clear what sort of filtering you want: whether you want to search for either value, or whether you want to search only for a non-empty value. You may need to adjust the conditions

    Your C# code should look like this

    void PopulateGridView()
{
    const string find = @"
select *
from TBL_USERS
where (Name like '%' + @Name + '%' or employee like '%' + @Employee + '%')
and excluded = @Excluded
";
    using(var con = new SqlConnection(YourConnString))
    using(var comm = new SqlCommand(find, con))
    {
        comm.Parameters.Add("@Name", SqlDbType.VarChar, 255).Value = TextBox1.Text;
        comm.Parameters.Add("@Employee", SqlDbType.VarChar, 32).Value = TextBox1.Text;
        comm.Parameters.Add("@Excluded", SqlDbType.Bit).Value = 1;
        con.Open();
        var dt = new DataTable();
        using(var reader = comm.ExecuteReader())
        {
            dt.Load(reader);
        }
        gvTestUsers.DataSource = dt;
        gvTestUsers.DataBind();
    }            
}
    • Note the use of a new connection object, not a cached one
    • Note the using blocks
    • Note that the query string is a const, this makes it somewhat less likely for you to get that itch to inject data into it
    • Since you only have one table, you can put it into a DataTable
    • An adapter is only useful when you have existing data to merge with
    Login or Signup to reply.
  2. 0

    when I put a Name in the box, the gridview will show me all the users that has that Name. When I put an Employeenumber in the box, it will only show the one user with that exactly Employeenumber

    Personally I’d use different queries if e.g. the EmployeeNumber is an integer/you can tell what the user wants from what they typed. Trying to make dynamic queries where you have a bunch of parameters that are all ORred plan typically quite badly and give poor performance. Building an SQL with a targeted where clause is preferred over fudging something together with N different parameters that are ORred.

    And I’d use Dapper:

    if(!int.TryParse(TextBox.Text)) //by name
  gvTestUsers.DataSource = con.Query<User>(
    @"select * from TBL_USERS where Name LIKE @Name AND excluded=0", 
    new { Name = "%" + TextBox.Text + "%" }
  ).AsList();

else //employee 
  gvTestUsers.DataSource = con.Query<User>(
    @"select * from TBL_USERS where EmployeeNumber = @Emp AND excluded=0", 
    new { Emp = TextBox.Text }
  ).AsList();

    If EmployeeNumber is alphameric (and you’re truly after "contains") and you don’t have any way to tell if they typed a name or number, then OR might be the way you have to go:

    gvTestUsers.DataSource = con.Query<User>(
  @"select * from TBL_USERS where (Name LIKE @X OR EmployeeNumber LIKE @X) AND excluded=0", 
  new { X= "%" + TextBox.Text + "%" }
).AsList();

    Yep.. that really is all you have to do with Dapper (after creating a SqlConnection con) – it handles all the parameters, running the query, retrieving the results, turning them into instances of your User class.. (which I’m sure you have, right? If you’re using a version of C# that supports records, it’s as easy as record User(string Name, int EmployeeNumber, ...))

    By comparison, working with datatables is a lot more painful; everything is stringly typed, needs casting from object all the time.. Awful

    —

    However, if you do want to carry on with an SqlDataAdapter, it’d look like:

    var dt = new DataTable;
var da = new SqlDataAdapter("select * from TBL_USERS where (Name LIKE @X OR EmployeeNumber LIKE @X) AND excluded=0", connstringhere);
da.SelectCommand.Parameters.Add("@X", SqlDbType.VarChar, 255).Value = "%"+ TextBox.Text + "%";
da.Fill(dt);
gvTestUsers.DataSource = dt;
gvTestUsers.DataBind();

    If you want name matching to be fuzzy, but emp no. matching to be exact, flip the query to Name LIKE '%' + @X + '%' OR EmployeeNumber = @X

    There’s some debate around whether dataadapters need disposing or not; Microsoft don’t in their example, but some people feel "it’s IDisposable, it should be disposed" – adding using in front of var will do that if you fall into that camp

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search