none
SqlDataSource.FilterExpression causing exception on DataBind RRS feed

  • Question

  •  I have the following filter expression:   Company = 'hh' AND CreatedOn >= '1/1/2009' AND CreatedOn < '1/10/2009' AND PageGuid = '{222222-22-222222222}'

    Exception:
    {"Index (zero based) must be greater than or equal to zero and less than the size of the argument list."}

    If I remove the "{" and "}" from PageGuid the exception is no longer thrown.  Why?  I plugged the expression into the statement below which works fine in SQL Server Management Studio:

    SELECT * FROM VISITORTRACKING WHERE Company = 'hh' AND CreatedOn >= '1/1/2009' AND CreatedOn < '1/10/2009' AND PageGuid = '{222222-22-222222222}'

    Wednesday, January 14, 2009 3:35 PM

Answers

  • Thank you for the response.  I believe that solution is a bit more than I was a looking for.  I was able to work around the problem by parameterizing the filter:

    Went from: Company = 'hh' AND pageGuid = '{2222-22-22222}'
    To:  Company = 'hh' AND pageGuid = '{0}'

    Then added the parameter to the datasource: 

    SqlDataSource1.FilterParameters.Add("pageGuid",tb.Text);



    • Marked as answer by P.Brian.Mackey Thursday, January 15, 2009 9:03 PM
    Thursday, January 15, 2009 9:03 PM

All replies

  • I would recommend you ask this kind of question in ASP.NET forum. http://forums.asp.net/

    The syntax of PageGuid='{0} is just a place holder in FilterExpression. I have worked out a solution to handle multiple FilterParameters declaratively with default value check to deal with SELECT ALL case. Here is the ASP.NET sample:
          <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:forumConnectionString %>" 
            SelectCommand="SELECT dob, name, LiveYear, LiveMonth, [id], UserName, PageGuid FROM [Dates4] "   
               FilterExpression="(UserName ='{0}' or '{0}' ='-1') AND (LiveYear={1} or {1}=-1)  AND (PageGuid='{2}' or '{2}'='25b721f2-646b-4c0c-9c44-949e9d18dcf2')" >   
    <FilterParameters> 
     
    <asp:ControlParameter ControlID="ddlUsers" Name="UserName" PropertyName="SelectedValue" Type="String"  /> 
    <asp:ControlParameter ControlID="ddlYear" Name="LiveYear" PropertyName="SelectedValue" Type="Int32"  /> 
    <asp:ControlParameter ControlID="ddlGuid" Name="PageGuid" PropertyName="SelectedValue"  Type="Object"  /> 
     
    </FilterParameters> 
    </asp:SqlDataSource> 
     
     
          
       
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="id"  DataSourceID="SqlDataSource1">  
                <Columns> 
                <asp:BoundField DataField="LiveMonth" HeaderText="LiveMonth" SortExpression="LiveMonth" /> 
                    <asp:BoundField DataField="dob" HeaderText="dob" SortExpression="dob" /> 
                     <asp:BoundField DataField="name" HeaderText="name" SortExpression="name" /> 
                </Columns> 
     </asp:GridView> 
       
     <hr />     
              
              
            
                  
            <asp:DropDownList ID="ddlUsers" runat="server" AutoPostBack="true" > 
            <asp:ListItem Value="-1">All</asp:ListItem> 
            <asp:ListItem Value="aaa">aaa</asp:ListItem> 
              <asp:ListItem Value="bbb">bbb</asp:ListItem> 
                <asp:ListItem Value="ccc">ccc</asp:ListItem> 
             <asp:ListItem Value="ddd">ddd</asp:ListItem> 
             <asp:ListItem Value="xxx">xxx</asp:ListItem> 
                </asp:DropDownList> 
           
      <asp:DropDownList ID="ddlYear" runat="server" AutoPostBack="true" > 
                           <asp:ListItem Value="-1">All</asp:ListItem> 
            <asp:ListItem Value="2007">2007</asp:ListItem> 
              <asp:ListItem Value="2008">2008</asp:ListItem> 
                <asp:ListItem Value="2009">2009</asp:ListItem> 
          
            </asp:DropDownList> 
              
              <asp:DropDownList ID="ddlGuid" runat="server" AutoPostBack="true" > 
                           <asp:ListItem Value="25b721f2-646b-4c0c-9c44-949e9d18dcf2">All</asp:ListItem> 
            <asp:ListItem Value="a65fa543-ff7e-4fd7-85cf-3069e2039948">Josh</asp:ListItem> 
              <asp:ListItem Value="102a3f7e-e9cc-4238-b8b8-fcd4c8d6b007">George</asp:ListItem> 
                <asp:ListItem Value="f2bdd05b-e6f0-485c-8fb5-ce970ebe00c6">Dan</asp:ListItem> 
          
            </asp:DropDownList> 
    Wednesday, January 14, 2009 4:35 PM
    Moderator
  • Thank you for the response.  I believe that solution is a bit more than I was a looking for.  I was able to work around the problem by parameterizing the filter:

    Went from: Company = 'hh' AND pageGuid = '{2222-22-22222}'
    To:  Company = 'hh' AND pageGuid = '{0}'

    Then added the parameter to the datasource: 

    SqlDataSource1.FilterParameters.Add("pageGuid",tb.Text);



    • Marked as answer by P.Brian.Mackey Thursday, January 15, 2009 9:03 PM
    Thursday, January 15, 2009 9:03 PM