Multi-search criteria with checkbox

Question

good morning i have created a winform that search with multiple criteria  and each criteria has a checkbox to decide to u choose this criteria or not. my question is how to write the query??? so that it works fine if one checkbox is selected or 2 or more. thanks

sara lion · Answer

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 M · Answer

Hi sara Can you show what code you are using so far? -Mamta

sara lion · Answer

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 M · Answer

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