Custom Paging With LinqDataSource & Formview

Question

I am trying to to use a LinqDataSource's Selecting event to filter data in for a FormView, however I can't seem to get it to page properly. I can manage to get only one record to show in the FormView but the paging controls do not show up. I have the following code in the LinqDataSource Selecting event:

e.Arguments.StartRowIndex = 0;
e.Arguments.MaximumRows = 1;

var result = db.Personnels.AsQueryable();

if (!String.IsNullOrEmpty(txtFirstName.Text))
{
    result = result.Where(r => r.First_Name.Contains(txtFirstName.Text));
}

if (!String.IsNullOrEmpty(txtLastName.Text))
{
    result = result.Where(r => r.Last_Name.Contains(txtLastName.Text));
}

e.Arguments.TotalRowCount = result.Count();

e.Result = result.Skip(fvMain.PageIndex).Take(1);

As mentioned above, this code works however only one record is displayed and the paging controls don't show up on the FormView. I have also tried to modify e.Result with the following, but I get an Object reference not set to an instance of an object. exception:

e.Result = result;

What is the correct way to page a FormView using the LinqDataSource's selecting event?

Edit 1

As requested, here is the FormView and LinqDataSource's markup:

<asp:FormView ID="fvMain" runat="server" CssClass="full" 
    DataKeyNames="Worker_ID" DataSourceID="ldsMain" DefaultMode="Edit" 
    AllowPaging="True" onitemupdating="fvMain_ItemUpdating">
    <EditItemTemplate>
        <table class="pad5 full">
            <tr>
                <td class="field-name" style="width: 100px">Worker ID:</td>
                <td style="width: 80px"><asp:TextBox ID="txtWorkerID" runat="server" Text='<%#Eval("Worker_ID") %>' ReadOnly="true" style="width: 75px" /></td>
                <td class="right"><input type="button" value="Injuries/LTA/WCB for this Person" onclick="openModalColorbox('Injuries.aspx?id='+$('#plcMain_fvMain_txtWorkerID').val(), 'Injuries')" /></td>
            </tr>
        </table>
        <table class="pad5 full">
            <tr>
                <td class="field-name">Type Of Person:</td>
                <td colspan="3">
                    <cc1:DataBindDropDownList ID="cboTypeOfPerson" runat="server" 
                        AppendDataBoundItems="True" DataSourceID="ldsPersonTypes" 
                        DataTextField="Type_of_Person" DataValueField="Type_of_Person" 
                        SelectedValue='<%#Bind("Type_Of_Person") %>'>
                        <asp:ListItem Text="" Value="" />
                    </cc1:DataBindDropDownList>
                    <asp:LinqDataSource ID="ldsPersonTypes" runat="server" 
                        ContextTypeName="PRIDE.PRIDEDataContext" EntityTypeName="" 
                        OrderBy="Type_of_Person" TableName="Personnel_Types">
                    </asp:LinqDataSource>
                </td>
            </tr>
            <tr>
                <td class="field-name">Employee Number:</td>
                <td><asp:TextBox ID="txtEmployeeNumber" runat="server" Text='<%#Bind("Employee_Number") %>' /></td>
                <td class="field-name">SIN:</td>
                <td><asp:TextBox ID="txtSIN" runat="server" Text='<%#Bind("SIN") %>' /></td>
            </tr>
            <tr>
                <td class="field-name">Last Name:</td>
                <td><asp:TextBox ID="txtLastName" runat="server" Text='<%#Bind("Last_Name") %>' /></td>
                <td class="field-name">Previous Last Name:</td>
                <td><asp:TextBox ID="txtPreviousLastName" runat="server" Text='<%#Bind("Previous_Last_Name") %>' /></td>
            </tr>
            <tr>
                <td class="field-name">First Name:</td>
                <td><asp:TextBox ID="txtFirstName" runat="server" Text='<%#Bind("First_Name") %>' /></td>
                <td class="field-name">Marital Status:</td>
                <td>
                    <cc1:DataBindDropDownList ID="DropDownList1" runat="server" 
                        AppendDataBoundItems="True" SelectedValue='<%# Bind("Marital_Status") %>' 
                        DataSourceID="ldsMaritalStatuses" DataTextField="Marital_Status" 
                        DataValueField="Marital_Status">
                        <asp:ListItem Text="" Value="" />
                    </cc1:DataBindDropDownList>
                    <asp:LinqDataSource ID="ldsMaritalStatuses" runat="server" 
                        ContextTypeName="PRIDE.PRIDEDataContext" EntityTypeName="" 
                        OrderBy="Marital_Status" TableName="List____Employee__Marital_Status">
                    </asp:LinqDataSource>
                </td>
            </tr>
            <tr>
                <td class="field-name">Division:</td>
                <td>
                    <cc1:DataBindDropDownList ID="cboDivision" runat="server" 
                        AppendDataBoundItems="True" SelectedValue='<%# Bind("Division") %>' 
                        DataSourceID="ldsDivisions" DataTextField="Division" 
                        DataValueField="Division">
                        <asp:ListItem Text="" Value="" />
                    </cc1:DataBindDropDownList>
                    <asp:LinqDataSource ID="ldsDivisions" runat="server" 
                        ContextTypeName="PRIDE.PRIDEDataContext" EntityTypeName="" GroupBy="Division" 
                        OrderBy="Division" Select="new (key as Division, it as Areas)" 
                        TableName="Areas">
                    </asp:LinqDataSource>
                </td>
                <td class="field-name">DOB:</td>
                <td>
                    <asp:TextBox ID="txtDOB" runat="server" Text='<%#Bind("DOB", "{0:dd MMM yyyy}") %>' />
                    <asp:CalendarExtender ID="CalendarExtender1" runat="server" TargetControlID="txtDOB" Format="dd MMM yyyy" />
                </td>
            </tr>
            <tr>
                <td class="field-name">Department:</td>
                <td>
                    <cc1:DataBindDropDownList ID="cboDepartment" runat="server" 
                        AppendDataBoundItems="True" SelectedValue='<%# Bind("Department") %>' 
                        DataSourceID="ldsDepartments" DataTextField="Department" 
                        DataValueField="Department">
                        <asp:ListItem Text="" Value="" />
                    </cc1:DataBindDropDownList>
                    <asp:LinqDataSource ID="ldsDepartments" runat="server" 
                        ContextTypeName="PRIDE.PRIDEDataContext" EntityTypeName="" GroupBy="Department" 
                        OrderBy="Department" Select="new (key as Department, it as Areas)" 
                        TableName="Areas">
                    </asp:LinqDataSource>
                </td>
                <td class="field-name">Terminated:</td>
                <td>
                    <cc1:DataBindDropDownList ID="cboTerminated" runat="server" 
                        SelectedValue='<%# Bind("Terminated") %>'>
                        <asp:ListItem Text="" Value="" />
                        <asp:ListItem Text="Yes" Value="Yes" />
                        <asp:ListItem Text="No" Value="No" />
                    </cc1:DataBindDropDownList>
                </td>
            </tr>
            <tr>
                <td class="field-name">Occupation:</td>
                <td>
                    <cc1:DataBindDropDownList ID="cboOccupation" runat="server" 
                        SelectedValue='<%# Bind("Occupation") %>' AppendDataBoundItems="True" 
                        DataSourceID="ldsOccupations" DataTextField="Occupation" 
                        DataValueField="Occupation">
                        <asp:ListItem Text="" Value="" />
                    </cc1:DataBindDropDownList>

                    <asp:LinqDataSource ID="ldsOccupations" runat="server" 
                        ContextTypeName="PRIDE.PRIDEDataContext" EntityTypeName="" OrderBy="Occupation" 
                        TableName="List____Employee__Occupations">
                    </asp:LinqDataSource>
                </td>
                <td class="field-name">Team:</td>
                <td>
                    <cc1:DataBindDropDownList ID="cboTeam" runat="server" 
                        SelectedValue='<%# Bind("Shift") %>' AppendDataBoundItems="True" 
                        DataSourceID="ldsShifts" DataTextField="Shift" 
                        DataValueField="Shift">
                        <asp:ListItem Text="" Value="" />
                    </cc1:DataBindDropDownList>

                    <asp:LinqDataSource ID="ldsShifts" runat="server" 
                        ContextTypeName="PRIDE.PRIDEDataContext" EntityTypeName="" OrderBy="Shift" 
                        TableName="List____Employee__Shifts">
                    </asp:LinqDataSource>
                </td>
            </tr>
            <tr>
                <td class="field-name">Lock Number:</td>
                <td><asp:TextBox ID="txtLockNumber" runat="server" Text='<%#Bind("Lock_Number") %>' /></td>
                <td class="field-name">Address:</td>
                <td><asp:TextBox ID="txtAddress" runat="server" Text='<%#Bind("Address") %>' /></td>
            </tr>
            <tr>
                <td class="field-name">City:</td>
                <td><asp:TextBox ID="txtCity" runat="server" Text='<%#Bind("City") %>' /></td>
                <td class="field-name">Company:</td>
                <td><asp:TextBox ID="txtCompany" runat="server" Text='<%#Bind("Company") %>' /></td>
            </tr>
            <tr>
                <td class="field-name">Province:</td>
                <td><asp:TextBox ID="txtProvince" runat="server" Text='<%#Bind("Province") %>' /></td>
                <td class="field-name">Company Contact:</td>
                <td><asp:TextBox ID="txtCompanyContact" runat="server" Text='<%#Bind("Company_Contact") %>' /></td>
            </tr>
            <tr>
                <td class="field-name">Postal:</td>
                <td><asp:TextBox ID="txtPostal" runat="server" Text='<%#Bind("Postal") %>' /></td>
                <td class="field-name">Phone:</td>
                <td><asp:TextBox ID="txtPhone" runat="server" Text='<%#Bind("Phone") %>' /></td>
            </tr>
            <tr>
                <td class="field-name">HCN:</td>
                <td><asp:TextBox ID="txtHCN" runat="server" Text='<%#Bind("HCN") %>' /></td>
                <td class="field-name">HCN Province:</td>
                <td><asp:TextBox ID="txtHCNProvince" runat="server" Text='<%#Bind("HCN_Province") %>' /></td>
            </tr>
            <tr>
                <td class="field-name">Comments:</td>
                <td colspan="3">
                    <asp:TextBox ID="txtComments" runat="server" Text='<%#Bind("Comments") %>' TextMode="MultiLine" Rows="3" />
                </td>
            </tr>
        </table>

        <h2>H.R.I.S.</h2>
        <table class="pad5 full">
            <tr>
                <td class="field-name">NOK Name:</td>
                <td><asp:TextBox ID="txtNOKName" runat="server" Text='<%#Bind("NOK_Name") %>' /></td>
                <td class="field-name">NOK Relation:</td>
                <td>
                    <cc1:DataBindDropDownList ID="cboNOKRelation" runat="server" 
                        SelectedValue='<%# Bind("NOK_Relation") %>' AppendDataBoundItems="True" 
                        DataSourceID="ldsNOKRelations" DataTextField="Relationship" 
                        DataValueField="Relationship">
                        <asp:ListItem Text="" Value="" />
                    </cc1:DataBindDropDownList>

                    <asp:LinqDataSource ID="ldsNOKRelations" runat="server" 
                        ContextTypeName="PRIDE.PRIDEDataContext" EntityTypeName="" 
                        OrderBy="Relationship" TableName="List____Employee__Relations">
                    </asp:LinqDataSource>
                </td>
            </tr>
            <tr>
                <td class="field-name">NOK Address:</td>
                <td><asp:TextBox ID="txtNOKAddress" runat="server" Text='<%#Bind("NOK_Address") %>' /></td>
                <td class="field-name">NOK City:</td>
                <td><asp:TextBox ID="txtNOKCity" runat="server" Text='<%#Bind("NOK_City") %>' /></td>
            </tr>
            <tr>
                <td class="field-name">NOK Province:</td>
                <td><asp:TextBox ID="txtNOKProvince" runat="server" Text='<%#Bind("NOK_Province") %>' /></td>
                <td class="field-name">NOK Postal:</td>
                <td><asp:TextBox ID="txtNOKPostal" runat="server" Text='<%#Bind("NOK_Postal") %>' /></td>
            </tr>
            <tr>
                <td class="field-name">NOK Phone:</td>
                <td><asp:TextBox ID="txtNOKPhone" runat="server" Text='<%#Bind("NOK_Phone") %>' /></td>
            </tr>
        </table>

        <div class="center">
            <asp:Button ID="btnSave" runat="server" Text="Save Changes" 
                onclick="btnSave_Click" />
        </div>
    </EditItemTemplate>
    <PagerSettings Mode="NextPreviousFirstLast" 
                FirstPageText="&amp;lt;&amp;lt; First" LastPageText="Last &amp;gt;&amp;gt;" 
                NextPageText="Next &amp;gt;" PreviousPageText="&amp;lt; Previous" 
                Position="TopAndBottom" />
            <PagerStyle CssClass="pager" />
</asp:FormView>
<asp:LinqDataSource ID="ldsMain" runat="server" 
    ContextTypeName="PRIDE.PRIDEDataContext" EnableDelete="True" 
    EnableInsert="True" EnableUpdate="True" EntityTypeName="" 
    TableName="Personnels" onselecting="ldsMain_Selecting">
    <UpdateParameters>
        <asp:Parameter ConvertEmptyStringToNull="true" Name="Employee_Number" />
    </UpdateParameters>
    <InsertParameters>
        <asp:Parameter ConvertEmptyStringToNull="true" Name="Employee_Number" />
    </InsertParameters>
</asp:LinqDataSource>

When you run that code, what is the value of `e.Arguments.TotalRowCount`? — nick_w, Feb 20 '13 at 05:18
@nick_w If I leave the textboxes empty (`txtFirstName` and `txtLastName`), `TotalRowCount` is 3,376 — Hoff, Feb 20 '13 at 14:01
@nick_w Done, I've stripped the templates from the FormView though since I don't think they're relevant. — Hoff, Feb 20 '13 at 21:26

score 0 · Answer 1 · edited May 23 '17 at 10:32

From MSDN we can see that the AutoPage property of LinqDataSource is set to true by default. From the docs:

When the AutoPage property is set to true, the LinqDataSource control retrieves only enough records for one page in the data-bound control. It uses the Skip<TSource> and Take<TSource> methods to retrieve the records for the current page.

The consequence of all this is that you will need to change a few things to get paging working.

Currently you are setting e.Arguments.StartRowIndex = 0;. You don't need to do this everytime as it will interfere with the paging.
Doing e.Result = result.Skip(fvMain.PageIndex).Take(1); is not necessary either. Just e.Result = result; should be fine.

I think the combination of those two things was causing your issue. However, we now need to handle the case where one of your filters kicks in.

The first problem is that we will need to persist the previously applied filter in the ViewState, otherwise we will be unable to tell when a filter has just been applied and when it is an existing one.

Inspired by After applying a filter to linqdatasource connected to GridView ,clicking edit bring me back to the old data, add a property to your page (you will need another one for Last Name):

public string FirstNameFilter 
{
    get
    {
        return (string)this.ViewState["FirstNameFilter"] ?? string.Empty;
    }

    set
    {
        this.ViewState["FirstNameFilter"] = value;
    }
}

Then in your Selecting event handler, run this code:

var result = db.Personnels.AsQueryable();

if (!string.IsNullOrEmpty(txtFirstName.Text))
{
    if (this.FirstNameFilter != txtFirstName.Text)
    {
        this.FirstNameFilter = txtFirstName.Text;
        e.Arguments.StartRowIndex = 0;
    }

    if (!string.IsNullOrEmpty(this.FirstNameFilter))
    {
        result = result.Where(r => r.First_Name.Contains(txtFirstName.Text));
    }
}

if (!string.IsNullOrEmpty(txtLastName.Text))
{
    if (this.LastNameFilter != txtLastName.Text)
    {
        this.LastNameFilter = txtLastName.Text;
        e.Arguments.StartRowIndex = 0;
    }

    if (!string.IsNullOrEmpty(this.LastNameFilter))
    {
        result = result.Where(r => r.First_Name.Contains(txtLastName.Text));
    }
}

e.Arguments.TotalRowCount = result.Count();

e.Result = result;

Thanks for the answer! Unfortunately I'm still getting the `Object reference not set to an instance of an object.` exception with the above code. It seems that even when I change `e.Result = result.Take(10)` it still works, it's just not liking when I pass the whole `result` object. This leads me to believe that maybe I have a binding problem in my FormView templates? I can't post all the code as it's like 800 lines long, but either way it looks like I have a different problem than I thought. I will award the bounty for your time and effort, if you have any other thoughts please let me know! — Hoff, Feb 21 '13 at 15:03
Ugh I just realized I pasted code from the wrong page yesterday. I have pasted the full code now in case it helps. — Hoff, Feb 21 '13 at 15:45
Ok I'm not sure why, but if I put `e.Result = result.Skip(0)` it works exactly as it should, paging and all. Problem solved, thanks for your help! — Hoff, Feb 21 '13 at 16:32

score 0 · Accepted Answer · answered Feb 21 '13 at 18:07

0

I am not sure why, but my problem was fixed by changing the line that reads e.Result = result... to e.Result = result.Skip(0). After doing that the paging works perfectly.

answered Feb 21 '13 at 18:07

Hoff

1,762
14
27

Custom Paging With LinqDataSource & Formview

2 Answers2