Better way? (DataView question)

[James]

Basically I have a DataGrid that I'm binding to the results of a stored
procedure call. The recordset is fairly small. Initially I'm creating a
DataSet from the results and binding it. There's a DropDownList on my page
that filters the records that are displayed in the grid.

How I'm currently handling this is when I initially bind, I create a
DataView from the table in the dataset. When the DropDownList changes
selection, I get the DataView from the Session variable (all of the original
data) and I create a DataTable that's a copy of the table within the
dataview. Then I loop through each DataRow in the DataTable and do
dr.Delete() on the records that don't meet the criteria, and rebind to the
DataTable. This seems really hokey. Is there a better way to do this
without having to jump through all of these hoops just to filter a dataset?

The reason I create the DataTable as a copy is because if I delete the
records from the DataView, it seems to affect the original dataset, which
I'm trying to keep intact.

::

Thanks

 

[Guest]

James,
Why don't you try dispensing with the creation of the dataTable, and just
set the filter property on the DataSource which will give you a DataView
consisting of the matching records. Each DataTable has a DefaultView. You can
set the filter and sort properties on this, and bind directly to the result.
Peter

 

[James]

Thanks for your reply! I tried this but it doesn't error and doesn't filter
out any records:

strSQL = "StoredProcHere"

Dim objCommand As New SqlCommand(strSQL, objConnection)
Dim ds As New DataSet
Dim da As New SqlDataAdapter(strSQL, objConnection)

da.Fill(ds, "Collections")

Dim dv As New DataView(ds.Tables("Collections"))
dv.RowFilter = "Collection_Period = 'Active'"
dgCollections.DataSource = dv.Table
dgCollections.DataBind()

....does this not work with Stored Procedures?

 

[Guest]

See here:

Dim objCommand As New SqlCommand(strSQL, objConnection)
Dim ds As New DataSet
Dim da As New SqlDataAdapter(strSQL, objConnection)
da.Fill(ds, "Collections")
Dim dv As Dataview =ds.Tables("Collections").DefaultView ' you can use
the default view if you want
dv.RowFilter = "Collection_Period = 'Active'"
dgCollections.DataSource = dv ' bind to the VIEW, not the table!
dgCollections.DataBind()


-- See the difference?
Peter
--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com

 

[James]

Aha! It seems to work when I set the datasource to just "dv" instead of
dv.Table. That's excellent. So should I just keep the DataSet in session
memory and then create a new DataView on SelectedIndexChanged form the
DataSet that's in session memory and create a filter? i.e.

InitialFunction()
Get Data In DataSet
Store Original DataSet in Session Memory
Bind to DataSet
End Function

On DropDownChange
Create DataView from DataSet in session memory
Create RowFilter
Bind to DataView
End Event