creating query

Question

creating query

lazarjojic

SSC Rookie

Points: 30
More actions
November 11, 2012 at 7:40 am

#393582

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.

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

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

Jeff Moden SSC Guru Points: 1003903 More actions · Answer 1

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)

lazarjojic SSC Rookie Points: 30 More actions · Answer 2

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

freecoder SSC Eights! Points: 951 More actions · Answer 3

use like

something like.

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

Jeff Moden SSC Guru Points: 1003903 More actions · Answer 4

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)