November 11, 2012 at 7:40 am
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.
November 11, 2012 at 8:50 am
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
Change is inevitable... Change for the better is not.
November 12, 2012 at 1:37 am
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);
November 12, 2012 at 10:02 am
use like
something like.
(name like '%' +@name + '%' name is null)
November 12, 2012 at 10:31 am
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
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply