Problem with paging with Linq and DataTable

Question

Hi,

I'm writing a small web app in VB.NET and I would like to do paging for my DataTable in the following function :

Public Shared Function Testing(ByVal KeyWord As String, ByVal CurrentPage As Integer, ByVal PageSize As Integer) As DataTable

        Dim db As New BibleDataClassesDataContext

        Dim dtDataTableOne = New DataTable("dtOne")
        dtDataTableOne.Columns.Add("bid", GetType(Integer))
        dtDataTableOne.Columns.Add("btitle", GetType(String))
        dtDataTableOne.Columns.Add("chid", GetType(Integer))
        dtDataTableOne.Columns.Add("chn", GetType(Integer))
        dtDataTableOne.Columns.Add("vid", GetType(Integer))
        dtDataTableOne.Columns.Add("vn", GetType(Integer))
        dtDataTableOne.Columns.Add("verse", GetType(String))

        Dim arrBook() As Integer = {5, 7, 8, 9}
        Dim j As Integer

        For i As Integer = 0 To arrBook.Length - 1 Step 1
            j = CInt(arrBook(i))
            Dim query = (From b In db.kham_books _
                        From ch In db.kham_chapters _
                        From v In db.kham_verses _
                        Where b.kh_b_id = j And ch.kh_book_id = j And v.kh_ch_id = ch.kh_ch_id _
                        Select b.kh_b_id, b.kh_b_title, ch.kh_ch_id, ch.kh_ch_number, v.kh_v_id, v.kh_v_number, v.kh_v_content)

            For Each r In query

                If r.kh_v_content.Contains(KeyWord) Then

                    dtDataTableOne.Rows.Add(New Object() {r.kh_b_id, r.kh_b_title, r.kh_ch_id, r.kh_ch_number, r.kh_v_id, r.kh_v_number, r.kh_v_content})

                End If

            Next
        Next

        Return dtDataTableOne

End Function

It works and returns the DataTable as expected. The problem is when I try to do paging with it with sth like this :

        Dim query = (From b In db.khmer_books _
                    From ch In db.khmer_chapters _
                    From v In db.khmer_verses _
                    Where b.kh_b_id = j And ch.kh_book_id = j And v.kh_ch_id = ch.kh_ch_id _
                    Select b.kh_b_id, b.kh_b_title, ch.kh_ch_id, ch.kh_ch_number, v.kh_v_id, v.kh_v_number, v.kh_v_content).Skip((CurrentPage - 1) * PageSize).Take(PageSize)

On my code behind of the page :

Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click

        Dim q As String
        q = txt_keyword.Text.Trim.ToLower

        dtDataTable = MCTC.DataAccess.blSearch.KhmerSearch(q, 1, 10)

        dlResult.DataSource = dtDataTable
        dlResult.DataBind()
End Sub

I have a button to go to the second page of DataTable :

Protected Sub lnk_next_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnk_next.Click



    dlResult.DataSource = MCTC.DataAccess.blSearch.KhmerSearch(keyword, 2, 10)
    dlResult.DataBind()


End Sub

But I got the blank page every time the lnk_next fired the event. There are over 200 records in the returned DataTable, that's why I need to do the paging.

Can anyone show me what the problem is with my code and the alternative way to do this ? I believe there's alwasys a better way to accomplish this.

Thanks.

Answer 1

See the following link from a previous stackoverflow answer:

• http://stackoverflow.com/questions/764364/datalist-paging-with-linq