How to Form Where Clause on the Basis of CheckListBox Selected Items

Question

0.00/5 (No votes)

See more:

How to Form Where Clause on the Basis of CheckListBox Selected Items
This my sp

SQL

BEGIN
declare @SQL VARCHAR(MAX)
 
SET @SQL='Select [Hadith_Text]
      ,[Hadith_Urdu]
      ,[Hadith_English]
      ,[Chapter_English_Name]
      ,[Chapter_Urdu_Name]
      ,[Baab_English_Name]
      ,[Baab_Urdu_Name]
      ,[Baab_Id]
      ,[Hadith_Book_Number]
	  From Hadiths_old
	  Where ID < 34192 '
 
IF @WhereClause<>''
	SET @SQL=@SQL+'  '+ @WhereClause + ' order by ID'
 
EXEC(@SQL)

while in C#

C#

private void buttonX1_Click(object sender, EventArgs e)
       {

           if (clbBooksName.GetItemChecked(0) == true)
           {
               BookSelection += "OR Hadith_Book_Number = 1 ";
           }
            if (clbBooksName.GetItemChecked(1) == true)
           {
               BookSelection += "OR Hadith_Book_Number = 2 ";
           }
          if (clbBooksName.GetItemChecked(2) == true)
           {
               BookSelection += "OR Hadith_Book_Number = 3 ";
           }
           if (clbBooksName.GetItemChecked(3) == true)
           {
               BookSelection += "OR Hadith_Book_Number = 4 ";
           }
           if (clbBooksName.GetItemChecked(4) == true)
           {
               BookSelection += "OR Hadith_Book_Number = 5 ";
           }
            if (clbBooksName.GetItemChecked(5) == true)
           {
               BookSelection += "OR Hadith_Book_Number = 6 ";
           }
           foreach (DataGridViewRow Datarow in dataGridView1.Rows)
           {

               if (dataGridView1.RowCount == 1) //When DataGridview Contain only one row
               {
                   if (Datarow.Cells[0].Value != null && Datarow.Cells[1].Value != null && Datarow.Cells[2].Value != null)//&& Datarow.Cells[3].Value != null)
                   {

                       WhereClause += Datarow.Cells[0].Value.ToString() + "  " + Datarow.Cells[1].Value.ToString() + " " + Datarow.Cells[2].Value.ToString() + " ";
                       SearchQuery = BookSelection + " AND " + WhereClause;
                   }

                   else
                   {
                       MessageBox.Show("Must Fill Column-کالم لازمی پر کریں","Error",MessageBoxButtons.OK,MessageBoxIcon.Information);
                   }

               }
               else if (dataGridView1.RowCount > 1) //When DataGridview Contain MOre then 1 row
               {
                   if (Datarow.Cells[0].Value != null && Datarow.Cells[1].Value != null && Datarow.Cells[2].Value != null && Datarow.Cells[3].Value != null)
                   {

                       WhereClause += Datarow.Cells[0].Value.ToString() + "  " + Datarow.Cells[1].Value.ToString() + " " + Datarow.Cells[2].Value.ToString()+ " " + Datarow.Cells[3].Value.ToString()+ " ";
                       SearchQuery = BookSelection + " AND " + WhereClause;
                   }

                   else
                   {
                       MessageBox.Show("Must Fill Column-کالم لازمی پر کریں", "Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
                   }

               }

           }


           //for testing search query ! What is this?
           MessageBox.Show(SearchQuery);


           //passing value to BLL CLass
           objSearching.WhereClause = SearchQuery;

           //pass Searched values to Form1 (Main Form) of Application.Bind also Search's tab Combobx and textboxes
           //this work require to do!!!!!
           FrmMain.dgvhadiths.DataSource = objSearching.HadithSearching();

           dgvSearchHadith.DataSource = objSearching.HadithSearching();
           //reset the searching query variable
           WhereClause = ""; SearchQuery = ""; BookSelection="";
       }

But I want To Send Directly Where Clause to SP. YOu can See SP already contain Where keyword But i dont want this. I want to Send Where clause on the basis of Checklist box item selection

Posted 2-Nov-13 16:43pm

Muhamad Faizan Khan

Add a Solution

Comments

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 3-Nov-13 1:15am

Then make one SP parameter and send that.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Bheeshm · Answer 1 · 2013-11-05T01:44:00

Hi ,

Try Doing Something like this ?

SQL

BEGIN
declare @SQL VARCHAR(MAX)
SET @SQL='Select [Hadith_Text]
      ,[Hadith_Urdu]
      ,[Hadith_English]
      ,[Chapter_English_Name]
      ,[Chapter_Urdu_Name]
      ,[Baab_English_Name]
      ,[Baab_Urdu_Name]
      ,[Baab_Id]
      ,[Hadith_Book_Number]
	  From Hadiths_old
	  --Where ID < 34192 '
 
IF @WhereClause<>''
	SET @SQL=@SQL+'  '+'Where ID < 34192 AND'+ @WhereClause + ' order by ID'
ELSE
	SET @SQL=@SQL+'  '+'Where ID < 34192  order by ID'
EXEC(@SQL)

And In Your C# Code , Optimize it little bit ... I think You can do something like the following ...

On Your Button Click...

C#

StringBuilder BookSelection = new StringBuilder();

foreach (int item in checkedListBox1.CheckedIndices)
{
    if (BookSelection.Length == 0)
        BookSelection.Append("Hadith_Book_Number In (");
    BookSelection.Append(" '");
    BookSelection.Append((item+1).ToString());
    BookSelection.Append("' ");
}

if (BookSelection.Length > 0)
    BookSelection.Append(")");