creating query

  • Hi!

    I have to create sql query for searching accouts in my database. Parameters for search i enter in textboxes (id,name,last name...).I know how to create that query when i enter all parameters

    SqlCommand cmd = new SqlCommand("SELECT * FROM accounts WHERE id=@id2 AND name=@name2 ...")

    but problem is because sometimes i will enter all parameters and sometimes i will enter some of these.In some textboxes i will not enter text at all, and in that case query needs to search accounts parametered only with non empty fields.

  • lazarjojic (11/11/2012)


    Hi!

    I have to create sql query for searching accouts in my database. Parameters for search i enter in textboxes (id,name,last name...).I know how to create that query when i enter all parameters

    SqlCommand cmd = new SqlCommand("SELECT * FROM accounts WHERE id=@id2 AND name=@name2 ...")

    but problem is because sometimes i will enter all parameters and sometimes i will enter some of these.In some textboxes i will not enter text at all, and in that case query needs to search accounts parametered only with non empty fields.

    That's affectionately known as a "Catch All" query and the definitive (IMHO) article on the subject was written by Microsoft Certified Master Gail Shaw and may be found at the following URL:

    http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks!

    It's like this in C#.

    SqlCommand cmd = new SqlCommand("SELECT * FROM accounts WHERE (id=@id2 OR @id2 is null) AND (name=@name2 or @name2 is null) AND (last_name=@last_name2 or @last_name2 is null)",con);

    if (id.Text.ToString() == "") cmd.Parameters.AddWithValue("id2", DBNull.Value);

    else cmd.Parameters.AddWithValue("id2", id.Text);

    if (name.Text.ToString() == "") cmd.Parameters.AddWithValue("name2", DBNull.Value);

    else cmd.Parameters.AddWithValue("name2", ime.Text);

    if (last_name.Text.ToString() == "") cmd.Parameters.AddWithValue("last_name2", DBNull.Value);

    else cmd.Parameters.AddWithValue("last_name2", last_name.Text);

  • use like

    something like.

    (name like '%' +@name + '%' name is null)

  • lazarjojic (11/12/2012)


    Thanks!

    It's like this in C#.

    SqlCommand cmd = new SqlCommand("SELECT * FROM accounts WHERE (id=@id2 OR @id2 is null) AND (name=@name2 or @name2 is null) AND (last_name=@last_name2 or @last_name2 is null)",con);

    if (id.Text.ToString() == "") cmd.Parameters.AddWithValue("id2", DBNull.Value);

    else cmd.Parameters.AddWithValue("id2", id.Text);

    if (name.Text.ToString() == "") cmd.Parameters.AddWithValue("name2", DBNull.Value);

    else cmd.Parameters.AddWithValue("name2", ime.Text);

    if (last_name.Text.ToString() == "") cmd.Parameters.AddWithValue("last_name2", DBNull.Value);

    else cmd.Parameters.AddWithValue("last_name2", last_name.Text);

    Yep... and I'm suggesting that's incorrect for performance.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply