Error while sorting filtered data from a GridView

Question

Hello guys,

I have an error I cannot solve, on a ASP.NET website.

One of its pages - Countries.aspx, has the following controls:

• a CheckBox called "CheckBoxNAME":

< asp:CheckBox ID="CheckBoxNAME" runat="server" Text="Name" /> 

• a TextBox called "TextBoxName":

< asp:TextBox ID="TextBoxNAME" runat="server" Width="100%" Wrap="False"> < /asp:TextBox> 

• a SQLDataSource called "SqlDataSourceCOUNTRIES", that selects all records from a Table with 3 columns - ID (Number, PK), NAME (Varchar2(1000)), and POPULATION (Number) called COUNTRIES

< asp:SqlDataSource 
    ID="SqlDataSourceCOUNTRIES" 
    runat="server" 
    ConnectionString="< %$ ConnectionStrings:myDB %> " 
    ProviderName="< %$ ConnectionStrings:myDB.ProviderName %> " 
    SelectCommand="SELECT COUNTRIES.ID, COUNTRIES.NAME, COUNTRIES.POPULATION FROM COUNTRIES ORDER BY COUNTRIES.NAME, COUNTRIES.ID"> 
< /asp:SqlDataSource> 
 

• a GridView called GridViewCOUNTRIES:

< asp:GridView 
    ID="GridViewCOUNTRIES" 
    runat="server" 
    AllowPaging="True" 
    AllowSorting="True" 
    AutoGenerateColumns="False" 
    DataSourceID="SqlDataSourceCOUNTRIES" 
    DataKeyNames="ID" 
    DataMember="DefaultView">  
    < Columns> 
        < asp:CommandField ShowSelectButton="True" /> 
        < asp:BoundField DataField="ID" HeaderText="Id" SortExpression="ID" /> 
        < asp:BoundField DataField="NAME" HeaderText="Name" SortExpression="NAME" /> 
        < asp:BoundField DataField="POPULATION" HeaderText="Population" SortExpression="POPULATION" /> 
    < /Columns> 
< /asp:GridView> 

• a Button called ButtonFilter:

< asp:Button ID="ButtonFilter" runat="server" Text="Filter" onclick="ButtonFilter_Click"/> 

This is the onclick event:

    protected void ButtonFilter_Click(object sender, EventArgs e)
    {
        Response.Redirect("Countries.aspx?" + 
            (this.CheckBoxNAME.Checked ? string.Format("NAME={0}", this.TextBoxNAME.Text) : string.Empty));
    }

Also, this is the main onload event of the page:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (Page.IsPostBack == false)
        {   
            if (Request.QueryString.Count != 0)
            {
                Dictionary parameters = new Dictionary();
                string commandTextFormat = string.Empty;

                if (Request.QueryString["NAME"] != null)
                {
                    if (commandTextFormat != string.Empty && commandTextFormat.EndsWith("AND") == false)
                    {
                        commandTextFormat += "AND";
                    }
                    commandTextFormat += " (UPPER(COUNTRIES.NAME) LIKE '%' || :NAME || '%') ";
                    parameters.Add("NAME", Request.QueryString["NAME"].ToString());
                }

                this.SqlDataSourceCOUNTRIES.SelectCommand = string.Format("SELECT COUNTRIES.ID, COUNTRIES.NAME, COUNTRIES.POPULATION FROM COUNTRIES WHERE {0} ORDER BY COUNTRIES.NAME, COUNTRIES.ID", commandTextFormat);

                foreach (KeyValuePair parameter in parameters)
                {
                    this.SqlDataSourceCOUNTRIES.SelectParameters.Add(parameter.Key, parameter.Value.ToUpper());
                }
            }
        }
    }

Basicly, the page displays in the GridViewCOUNTRIES all the records of table COUNTRIES.

The scenario is the following: - the user checks the CheckBox; - the user types a value in the TextBox (let's say "ch"); - the user presses the Button; - the page loads displaying only the records that match the filter criteria (in this case, all the countries that have names containing "Ch"); - the user clicks on the header of the column called "Name" in order to sort the data in the GridView

Then, I get the following error: ORA-01036: illegal variable name/number.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OracleClient.OracleException: ORA-01036: illegal variable name/number

Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Any help is greatly appreciated, tnks.

PS: I'm using ASP.NET 3.5, under Visual Studio 2008, with an OracleXE database.

Answer 1

The problem was an error in the SelectCommand of the SqlDataSource... Pretty lame... If anyone would like to find out about it in full details, contact me. Other wise, I'll move on something else right now.

This question can be considered answered, from my point of view, and closed.

Thanks anyway.