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

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

Answer 1

User1583469135 posted

Try this

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

Answer 2

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

Answer 3

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?

Answer 4

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

Answer 5

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>