Multi-search criteria with checkbox

hi mamta ,

i know my solution is not professional , below are my codes ,thanks a lot .

using (SqlConnection cn = new SqlConnection(Common.ConnectionString))

{

using (SqlCommand cmd = cn.CreateCommand())

{

try

{

cn.Open();

cmd.Parameters.AddWithValue("@CaseNo", textBox1.Text.Trim());

cmd.Parameters.AddWithValue("@Name", textBox2.Text.Trim());

cmd.Parameters.AddWithValue("@Conclusion", richTextBox1.Text.Trim());

cmd.Parameters.AddWithValue("@DateOfRecieving", dateTimePicker1.Value.ToString("yyyy/MM/dd"));

cmd.Parameters.AddWithValue("@DateOfIssuance", dateTimePicker2.Value.ToString("yyyy/MM/dd"));

if (checkBox1.Checked == true)

{

cmd.CommandText = "SELECT * FROM Cytology WHERE CaseNo=@CaseNo ";

}

if (checkBox2.Checked == true)

{

cmd.CommandText = "SELECT * FROM Cytology WHERE Name like '%' +@Name+'%' ";

}

if ((checkBox1.Checked == true) &(checkBox2.Checked == true))

{

cmd.CommandText = "SELECT * FROM Cytology WHERE CaseNo=@CaseNo AND Name like '%' +@Name+'%'";

}

if (checkBox3.Checked == true)

{

cmd.CommandText = "SELECT * FROM Cytology WHERE Conclusion like '%' + @Conclusion +'%' ";

}

if ((checkBox1.Checked == true)&(checkBox3.Checked == true))

{

cmd.CommandText = "SELECT * FROM Cytology WHERE CaseNo=@CaseNo AND Conclusion like '%' + @Conclusion +'%' ";

}

if ((checkBox5.Checked == true) & (checkBox4.Checked == true))

{

cmd.CommandText = "SELECT * FROM Cytology WHERE DateOfRecieving BETWEEN @DateOfRecieving AND DATEADD(day, 1, @DateOfIssuance) ";

}

if ((checkBox1.Checked == true) & (checkBox2.Checked == true) & (checkBox3.Checked == true))

{

cmd.CommandText = "SELECT * FROM Cytology WHERE CaseNo=@CaseNo AND Name like '%' +@Name+'%' AND Conclusion like '%' + @Conclusion +'%'";

}

if ((checkBox1.Checked == true) & (checkBox2.Checked == true) & (checkBox3.Checked == true) & (checkBox5.Checked == true) & (checkBox4.Checked == true))

{

cmd.CommandText = "SELECT * FROM Cytology WHERE CaseNo=@CaseNo AND Name like '%' +@Name+'%' AND Conclusion like '%' + @Conclusion +'%' AND DateOfRecieving BETWEEN @DateOfRecieving AND DATEADD(day, 1, @DateOfIssuance)";

}

SqlDataAdapter da = new SqlDataAdapter(cmd);

dt = new DataTable();

da.Fill(dt);

if (dt.Rows.Count > 0)

{

bindingSource1.DataSource = dt;

int rowCount = ((DataTable)this.bindingSource1.DataSource).Rows.Count;

label3.Text = rowCount.ToString();

}

else

{

MessageBox.Show("Sorry, No Data Matched", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);

//textBox1.Clear();

textBox1.Clear();

textBox2.Clear();

label3.Text = "0";

richTextBox1.Clear();

dateTimePicker1.ResetText();

dateTimePicker2.ResetText();

checkBox1.Checked = false;

checkBox2.Checked = false;

checkBox3.Checked = false;

checkBox4.Checked = false;

checkBox5.Checked = false;

}

catch (Exception ex)

{

MessageBox.Show(ex.ToString());

}

finally

{

cn.Close();

}

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

sara lion14y ago

hi mamta ,

i know my solution is not professional , below are my codes ,thanks a lot .

using (SqlConnection cn = new SqlConnection(Common.ConnectionString))
   {
   using (SqlCommand cmd = cn.CreateCommand())
   {
   try
   {
   cn.Open();

   cmd.Parameters.AddWithValue("@CaseNo", textBox1.Text.Trim());
   cmd.Parameters.AddWithValue("@Name", textBox2.Text.Trim());
   cmd.Parameters.AddWithValue("@Conclusion", richTextBox1.Text.Trim());
   cmd.Parameters.AddWithValue("@DateOfRecieving", dateTimePicker1.Value.ToString("yyyy/MM/dd"));
   cmd.Parameters.AddWithValue("@DateOfIssuance", dateTimePicker2.Value.ToString("yyyy/MM/dd"));


   if (checkBox1.Checked == true)
   {
   cmd.CommandText = "SELECT * FROM Cytology WHERE CaseNo=@CaseNo ";
   }

   if (checkBox2.Checked == true)
   {
   cmd.CommandText = "SELECT * FROM Cytology WHERE Name like '%' +@Name+'%' ";
   }

   if ((checkBox1.Checked == true) &(checkBox2.Checked == true))
   {
   cmd.CommandText = "SELECT * FROM Cytology WHERE CaseNo=@CaseNo AND Name like '%' +@Name+'%'";
   }

   if (checkBox3.Checked == true)
   {
   cmd.CommandText = "SELECT * FROM Cytology WHERE Conclusion like '%' + @Conclusion +'%' ";
   }
   if ((checkBox1.Checked == true)&(checkBox3.Checked == true))
   {
   cmd.CommandText = "SELECT * FROM Cytology WHERE CaseNo=@CaseNo AND Conclusion like '%' + @Conclusion +'%' ";
   }
   if ((checkBox5.Checked == true) & (checkBox4.Checked == true))
   {
   cmd.CommandText = "SELECT * FROM Cytology WHERE DateOfRecieving BETWEEN @DateOfRecieving AND DATEADD(day, 1, @DateOfIssuance) ";
   }

   if ((checkBox1.Checked == true) & (checkBox2.Checked == true) & (checkBox3.Checked == true))
   {
   cmd.CommandText = "SELECT * FROM Cytology WHERE CaseNo=@CaseNo AND Name like '%' +@Name+'%' AND Conclusion like '%' + @Conclusion +'%'";
   }

   if ((checkBox1.Checked == true) & (checkBox2.Checked == true) & (checkBox3.Checked == true) & (checkBox5.Checked == true) & (checkBox4.Checked == true))
   {
   cmd.CommandText = "SELECT * FROM Cytology WHERE CaseNo=@CaseNo AND Name like '%' +@Name+'%' AND Conclusion like '%' + @Conclusion +'%' AND DateOfRecieving BETWEEN @DateOfRecieving AND DATEADD(day, 1, @DateOfIssuance)";
   }
   SqlDataAdapter da = new SqlDataAdapter(cmd);
   dt = new DataTable();
   da.Fill(dt);
   if (dt.Rows.Count > 0)
   {
   bindingSource1.DataSource = dt;

   int rowCount = ((DataTable)this.bindingSource1.DataSource).Rows.Count;
   label3.Text = rowCount.ToString();
   }
   else
   {
   MessageBox.Show("Sorry, No Data Matched", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
   //textBox1.Clear();
   textBox1.Clear();
   textBox2.Clear();
   label3.Text = "0";
   richTextBox1.Clear();
   dateTimePicker1.ResetText();
   dateTimePicker2.ResetText();
   checkBox1.Checked = false;
   checkBox2.Checked = false;
   checkBox3.Checked = false;
   checkBox4.Checked = false;
   checkBox5.Checked = false;

   }

   }
   catch (Exception ex)
   {
   MessageBox.Show(ex.ToString());
   }
   finally
   {
   cn.Close();
   }
   }
   }
   }
Mamta M14y ago
Hi sara,

Can you show what code you are using so far?

-Mamta
sara lion14y ago
thanks for your reply, but my case is i have 4 criteria , each with checkbox , if user wants to use one criteria for search he/she checks that check box and if by 2 then checks 2 checkbox and so on .

i did many if s ans select queries ,which works but not always .:(
Mamta M14y ago
You can write the query easily using an OR.

Consider an example.

Assuming that you want to search for all candidates having qualification as MCA, or name containing Nicole, or a id less than or equal to 100, you can write the query as below:

SELECT candidateId, Name, Address, qualification
FROM Candidates where qualification='MCA' OR Name LIKE 'Nicole%' OR candidateId<=100