Creating query RSS

• • Reply
  

  lazarjojic

  0 Points

  6 Posts

  Creating query

  Nov 11, 2012 01:35 PM|LINK

  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.

• • Reply
  

  Dan Bracuk

  Contributor

  3970 Points

  1096 Posts

  Re: Creating query

  Nov 11, 2012 01:59 PM|LINK

  lazarjojic

  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.

  Sounds like you have to use if/else logic.  Make sure you cope with situations where all fields are empty.

• • Reply
  

  spapim

  Contributor

  2450 Points

  370 Posts

  Re: Creating query

  Nov 11, 2012 03:13 PM|LINK

  Hi,

  Try something like this:

  SELECT * FROM accounts
  WHERE
      (id = @id2 or @id2 is null) AND
      (name = @name2 or @name2 is null) -- AND ...

  Hope this helps. 

  www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial
  www.imobiliariasemsuzano.com.br
• • Reply
  

  Anil Srivast...

  Member

  442 Points

  292 Posts

  Re: Creating query

  Nov 11, 2012 04:11 PM|LINK

  Hi

  make javascript so that textbox should accept only valid values

  string str=select * from tblLogin where @id='" + txtUserId.Text +"';

  Anil

• • Reply
  

  lazarjojic

  0 Points

  6 Posts

  Re: Creating query

  Nov 12, 2012 07:34 AM|LINK

  Thanks spapim! 

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

  • Edited by lazarjojic on Nov 12, 2012 07:35 AM
• • Reply
  

  Dan Bracuk

  Contributor

  3970 Points

  1096 Posts

  Re: Creating query

  Nov 12, 2012 12:26 PM|LINK

  lazarjojic

  Thanks spapim! 

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

  I suggest testing that query.  I don't think you will get the results you expected.

• • Reply
  

  lazarjojic

  0 Points

  6 Posts

  Re: Creating query

  Nov 12, 2012 02:05 PM|LINK

  I tested it before. It works

• • Reply
  

  Amy Peng - M...

  Star

  10135 Points

  960 Posts

  Microsoft

  Re: Creating query

  Nov 13, 2012 04:21 AM|LINK

  lazarjojic

  I tested it before. It works

  Hi,

  So you have solved the question now, am I right?

  Thanks,
  Amy Peng

  Please mark the replies as answers if they help or unmark if not.
  Feedback to us
  
  Develop and promote your apps in Windows Store