CODEWITHSUNDEEP
c#.net
Fanto
Fanto

Reputation: 137

How to handle OdbcException

I have this problem that I try to resolve without any success. I have an input research in which I can make research with keywords. I have a checkBoxList representing different topics (Logistic, Administration, Finance...) to filter the queries. If my keyword is "problem" and I check "Administration" checkbox, the results will only be about "problem" related to Administration. It works fine, but if I dont check any checkbox it leads me to an OdbcException. I tried to handle it with try catch, but it is completely ignored, it is not executed and I still get an error..

Here is my code for the checkboxlist research :

<asp:CheckBoxList ID="CheckboxID" runat="server" OnSelectedIndexChanged="CheckboxID_SelectedIndexChanged" AutoPostBack="true">
        <asp:ListItem Value="ADV"> ADV </asp:ListItem>
        <asp:ListItem Value="Logistique"> Logistique </asp:ListItem>
        <asp:ListItem Value="Finance"> Finance </asp:ListItem>
        <asp:ListItem Value="EDI"> EDI </asp:ListItem>
        <asp:ListItem Value="Administration"> Administration </asp:ListItem>
    </asp:CheckBoxList>

    <label for="tbSearch">Recherche : </label>
     <asp:TextBox ID="tbSearch" runat="server"></asp:TextBox>`


protected void Populate()
{
    Occurence = 0;

    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; 
        }
    }

    strSql += @"  )";

EDIT : Got another pb, if I don't check any checkboxes and I enter a word, leads me to that same error, tried everything to make it work I'm going crazy

any ideas ?

Upvotes: 2

Views: 106

Answers (3)

Thomas Ayoub
Thomas Ayoub

Reputation: 29451

If you don't check any checkboxes your condition will be AND () which leads to an exception. Use this:

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

if (CheckboxID.Items.Cast<ListItem>().Any(x => x.Selected))
{
    List<string> conditionList = CheckboxID.Items.Cast<ListItem>().Where(x => x.Selected).Select(item => " SousRubrique Like '%" + item.Value + "%' ").ToList();
    strSql += " WHERE (" + string.Join(" OR ", conditionList) + "  )";
}

It will concatenate the string only if at least one checkbox is checked.

You need to add using System.Linq; on top of your files

Upvotes: 0

sujith karivelil
sujith karivelil

Reputation: 29036

You will get an invalid syntax error after Where if there is no Items selected or all if (item.Selected) evaluates to False, So what you need to Do is Attach the And part only if there is any selected item in the Checkbox. I would like to check whether any items are selected or not before performing the iteration:

if (CheckboxID.Items.Any(x => x.Selected))
{
    strSql +=" AND (";

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

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

One more thing, use StringBuilder and avoid += for concatination

Upvotes: 0

Aganju
Aganju

Reputation: 6405

When I look at your code, if CheckboxID.Item is empty, you generate the string ...WHERE ( 1 = 1 ) AND ( ). That doesn't look like a valid syntax to me.

I think you need to handle the AND ( and the ) differently - they should only be concatenated if anything is in CheckboxID.Item.

Upvotes: 1

Related Questions