asked on

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

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

niralshah

Try other way

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

TheCalicoTree

ASKER

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))

niralshah

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

rajapandian_81

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

TheCalicoTree

ASKER

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

ASKER CERTIFIED SOLUTION

TheCalicoTree

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial