I cannot get a filter and sort on data table to work - help please!

TheCalicoTree
TheCalicoTree used Ask the Experts™
on
I am reading through a CSV file, dumping it into a data table and then trying to filter the data based upon a users selection.
The data table it being created but not applying the filter.

Public Sub PageLoad(ByVal Sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    If Not Page.IsPostBack Then
        ReadCsv()
        lblSearch.Text = "Lettings Search"
    End If
End Sub
 
Private Sub ReadCsv()
    Dim dirInfo As New DirectoryInfo(Server.MapPath("~/ftp/"))
    Dim fileLocation As String = dirInfo.ToString & "data.txt"
 
    Try
        Dim csv As New CSVFile(fileLocation)
        Dim ds As DataSet = csv.ToDataSet("MyTable")
        If Not ds Is Nothing Then
            myDataRepeater.DataSource = ds
            myDataRepeater.DataMember = ds.Tables.Item(0).TableName
            myDataRepeater.DataBind()
        End If
        ds = Nothing
        csv = Nothing
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub
 
Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btnSubmit.Click
    Dim rowCount As Integer
 
    rowCount = QueryCsv()
    pnlSearch.Visible = False
    lblResults.Visible = True
    lblSearch.Text = "Search Results"
    lblResults.Text = "Your search returned " & rowCount.ToString & " results"
 
    If rowCount > 0 Then
        myDataRepeater.Visible = True
        pnlResults.Visible = True
        btnBack.Visible = True
    End If
 
End Sub
 
Protected Function QueryCsv() As Integer
 
    Dim dirInfo As New DirectoryInfo(Server.MapPath("~/ftp/"))
    Dim fileLocation As String = dirInfo.ToString & "data.txt"
    Dim numberofRows As Integer
 
    Try
        Dim csv As New CSVFile(fileLocation)
        Dim ds As DataSet = csv.ToDataSet("MyTable")
        If Not ds Is Nothing Then
 
 
            Dim strExpr As String = "PropertyID = 'P1005'"
            Dim strSort As String = "PropertyID DESC"
 
            Try
 
                ds.Tables.Item(0).DefaultView.RowFilter = strExpr
                ds.Tables.Item(0).DefaultView.Sort = strSort
                myDataRepeater.DataSource = ds.Tables.Item(0).DefaultView
 
            Catch ex As Exception
            End Try
 
        End If
    numberofRows = ds.Tables("MyTable").Rows.Count
    Catch ex As Exception
 
    End Try
    Return numberofRows
End Function
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try other way

Dim dtBound as new datatable
dtBound.rows.add(ds.Tables.Item(0).Select("PropertyID = 'P1005'",strSort))
dtBound.acceptchanges()
 myDataRepeater.DataSource = dtBound

Author

Commented:
Thanks niralshah, I tired this but got the following error:
ex = {"Input array is longer than the number of columns in this table."}
 on this row
dtBound.rows.add(ds.Tables.Item(0).Select("PropertyID = 'P1005'",strSort))
then try this one i thought it will directly add the rows that way but it won't so try following

Dim dtBound as new datatable
dtBound = ds.Tables.Item(0).Clone()
Dim intCnt as integer
Dim drSelect() as DataRow

drSelect = ds.Tables.Item(0).Select("PropertyID = 'P1005'",strSort)

For intCnt = 0 to drSelect.Count - 1
       dtBound.rows.ImportRows(drSelect(intCnt))
Next
dtBound.acceptchanges()

 myDataRepeater.DataSource = dtBound
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

You have to assign the DefaultView in DataView before proceed.

DataView DV = ds.Tables.Item(0).DefaultView
DV.RowFilter = strExpr
DV.Sort = strSort
myDataRepeater.DataSource = DV

Open in new window

Author

Commented:
rajapandian 81, This still returned a full set of results, again the filter was not applied.

niralshah, dtBound.Rows.ImportRows(drSelect(intCnt)) - Import rows is not a member of DataRow collection.

Thanks
So the correct line should be:
dtBound.ImportRow(drSelect(intCnt))
Which does apply the filter and dtBound contains correct number of rows.

Then needed to add
myDataRepeater.DataBind() to the end of the code.

So full working answer is:

Dim dtBound As New DataTable

dtBound = ds.Tables.Item(0).Clone()
Dim intCnt As Integer
Dim drSelect() As DataRow

drSelect = ds.Tables.Item(0).Select(strExpr, strSort)

For intCnt = 0 To drSelect.Count - 1
dtBound.ImportRow(drSelect(intCnt))

Next
dtBound.AcceptChanges()

myDataRepeater.DataSource = dtBound
myDataRepeater.DataBind()

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial