c# issues with filtering sql query from checkboxlist

Question

Here's my problem. I have a textBox in which can be realised research with keyword. I have a checklistbox with different topic (ADV, Logistic, Finance, Administration) to filter the sql queries. If I search for a key word and I checked "logistic" the return result will only be related to "Logistic". This works well, the problem is that, if I check 2 checkbox, "logistic" and"finance" for example I will only have result related to "logistic" but I would like to have the 2 results.. I made it worked like 20 minutes ago and suddenly doesn't work anymore I don't undersand why. Can anyone tell me what am I missing ?

Here's my code :

string word = tbSearch.Text;

string strSql = @"SELECT CAST(ID as VarChar(50)) ID, Aggregation, DateDerniereSolution, DateDescription, DerniereSolution, DescriptionDemande, FileDeTraitement, NomContact, Numero, SousRubrique, TitreDemande
               FROM cfao_DigiHelp_index.DigiHelpData WHERE ( 1 = 1 )";

string selectedValue = "";
bool IsFirst = false;
strSql += @" AND (";

foreach (ListItem item in CheckboxID.Items)
{
    if (item.Selected)
    {
        selectedValue +=  item.Value ;

        if (IsFirst)
        {
            strSql += " OR ";
        }

        strSql += " SousRubrique Like '%" + selectedValue + "%' ";
        IsFirst = true; 
    }

    if (CheckboxID.SelectedIndex == -1)
    {
        Label2.Visible = true;
        Label2.Text = "Veuillez cocher au moins une rubrique";
    }
}

strSql += @"  )";

What you mean by doesn't work anymore are you getting any error or not getting the expected output? — sujith karivelil
– sujith karivelil, Commented Jun 23, 2016 at 13:35

Steve · Accepted Answer · 2016-06-23 13:36:41Z

1

This line

 selectedValue +=  item.Value ;

wreak havoc your query, because at every loop you keep concatenating to the selectedValue the value of the checked item. Just use the item.Value

strSql += " SousRubrique Like '%" + item.Value + "%' ";

Also, if your checked items match exactly with the SousRubrique contents you could avoid using the LIKE and the wild search pattern "%" but use just the equal operator

Keep in mind that you need to have a strict control on the contents of your checkedlistbox item because if your user is able to write the value for SousRubrique your code is exposed to a Sql Injection attacks.

edited Jun 23, 2016 at 13:36

answered Jun 23, 2016 at 13:33

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Fanto Over a year ago

Thank you so much !

Collectives™ on Stack Overflow

c# issues with filtering sql query from checkboxlist

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related