locked
How to filter rows out of a SQLDataSource in code-behind prior to loading GridView RRS feed

  • Question

  • User1279376247 posted

    I have a sqldatasource hooked up to a Gridview.

    I would like to filter out some rows, during the sqldatasource_selected event.

    I cannot figure out how to get access to the datatable in the _selected event.  I found the following code which apparently works for an ObjectDataSource but how do I load the DataTable for a SQLDataSource?

    I think the first line of the function is the one which is wrong; I don't know what e.ReturnValue is.

    Protected Sub ds_Selected(sender As Object, e As ObjectDataSourceStatusEventArgs)
    	Dim dt As DataTable = DirectCast(e.ReturnValue, DataTable)
    	For j As Integer = 0 To dt.Rows.Count - 1
    		Dim r As DataRow = dt.Rows(j)
    		If r("SomeField") = specialCondition Then
    			dt.Rows.Remove(r)
    		End If
    	Next
    End Sub
    Thursday, December 13, 2012 1:40 PM

Answers

  • User3866881 posted

    I would like to filter out some rows, during the sqldatasource_selected event.

    Hi,

    SqlDataSource isn't used for you to select records from filtering. I think if you'd like to filter records, you can:

    1) Just use dynamic SQL select statement for SqlDataSource, something like:

    Select * from xxx where [Column1]=@Value1

    And then use ControlParameter, QueryStringParameter,……assing value to "@Value1".

    2) You can also try to download the demo:

    http://www.codeproject.com/Articles/26969/GridView-Multiple-Filter-AJAX-Control

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 14, 2012 4:11 AM
  • User3866881 posted

    and I don't think you can do that with dynamic sql. Can you?

    Yes, I can. Just try this:

    List<string> strings = new List<string>{"aa","bb","cc"};
    string s = "select * from xxx where fieldColumn in (')";
    
    s+=String.Join("','"strings.ToArray());
    s+="')";
    
    //Then use "s" for your SqlDataAdapter
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 14, 2012 8:13 PM

All replies

  • User1583469135 posted

    Try this

    Dim dv As New DataView   
    Dim dt As New DataTable 
      
    dv = mySQLDataSource.Select(DataSourceSelectArguments.Empty)   
    dt = dv.ToTable()

    Thursday, December 13, 2012 3:18 PM
  • User3866881 posted

    I would like to filter out some rows, during the sqldatasource_selected event.

    Hi,

    SqlDataSource isn't used for you to select records from filtering. I think if you'd like to filter records, you can:

    1) Just use dynamic SQL select statement for SqlDataSource, something like:

    Select * from xxx where [Column1]=@Value1

    And then use ControlParameter, QueryStringParameter,……assing value to "@Value1".

    2) You can also try to download the demo:

    http://www.codeproject.com/Articles/26969/GridView-Multiple-Filter-AJAX-Control

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 14, 2012 4:11 AM
  • User1279376247 posted

    Decker,

    Thanks for the reply.

    The problem is that the type of filtering I want to do is:

    Select * from xxx where [Column1] in (aaa,bbb,ccc,ddd)

    and I don't think you can do that with dynamic sql. Can you?

    Friday, December 14, 2012 6:58 AM
  • User3866881 posted

    and I don't think you can do that with dynamic sql. Can you?

    Yes, I can. Just try this:

    List<string> strings = new List<string>{"aa","bb","cc"};
    string s = "select * from xxx where fieldColumn in (')";
    
    s+=String.Join("','"strings.ToArray());
    s+="')";
    
    //Then use "s" for your SqlDataAdapter
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 14, 2012 8:13 PM
  • User-430413477 posted

    The only code you have to write is the filterexpression and then set the parameters and you are done

    Basically NO CODE

     

    FirstName
            <asp:TextBox ID="FirstNameTextBox" runat="server"></asp:TextBox>
            <br />
            LastName
            <asp:TextBox ID="LastNameTextBox" runat="server"></asp:TextBox>
            <br />
            <asp:Button ID="SearchButton" runat="server" Text="Search" />
            <br />
            <br />
            Filtered RowCount
            <asp:Label ID="RowCountFilteredLabel" runat="server"></asp:Label>
            <br />
            <br />
            <asp:GridView ID="GridView1" runat="server" AllowPaging="True"
                AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="CustomerID"
                DataSourceID="SqlDataSource1">
                <Columns>
                    <asp:BoundField DataField="CustomerID" HeaderText="ID" InsertVisible="False"
                        ReadOnly="True" SortExpression="CustomerID" />
                    <asp:BoundField DataField="LoginName" HeaderText="Login Name"
                        SortExpression="LoginName" />
                    <asp:BoundField DataField="FirstName" HeaderText="First Name"
                        SortExpression="FirstName" />
                    <asp:BoundField DataField="FirstNameNulls" HeaderText="First Name Nulls"
                        SortExpression="FirstNameNulls" />
                    <asp:BoundField DataField="LastName" HeaderText="Last Name"
                        SortExpression="LastName" />
                    <asp:BoundField DataField="Address1" HeaderText="Address1"
                        SortExpression="Address1" />
                    <asp:BoundField DataField="Address2" HeaderText="Address2"
                        SortExpression="Address2" />
                    <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
                    <asp:BoundField DataField="State" HeaderText="State" SortExpression="State" />
                    <asp:BoundField DataField="Zip" HeaderText="Zip" SortExpression="Zip" />
                    <asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" />
                    <asp:BoundField DataField="EmailAddress" HeaderText="Email Address"
                        SortExpression="EmailAddress" />
                </Columns>
            </asp:GridView>
              <asp:SqlDataSource ID="SqlDataSource1" runat="server"
                ConnectionString="<%$ ConnectionStrings:ASPDotNet35ExamplesConnectionString %>"
                FilterExpression="(FirstNameNulls Like '%{0}%') AND LastName Like '%{1}%'"
                
                SelectCommand="SELECT CustomerID, LoginName, FirstName, LastName, Address1, Address2, City, State, Zip, Phone, EmailAddress, ISNULL(FirstName, '') AS FirstNameNulls FROM Customers ORDER BY FirstName">
                <FilterParameters>
                    <asp:ControlParameter ControlID="FirstNameTextBox" Name="newparameter" PropertyName="Text"   DefaultValue="%"  />
                    <asp:ControlParameter ControlID="LastNameTextBox" Name="newparameter" PropertyName="Text"  DefaultValue="%"   />
                </FilterParameters>
            </asp:SqlDataSource>

     

    Saturday, August 3, 2013 11:05 AM