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

Multi-search criteria with checkbox

Answers (4)