subsonic collection

Question

I've written this code to generate a collection. I've tried to filter the collection using subsonic.where but its not working. Actually the where clause will change on user input so i cannot add the where clause to the sqlquery and also the datatable will be filled with different data from the collection based on the user input. How can I acheive this. Also i want the collection to be unchanged so that i use it further to filter with another where clause. Alo the I've selected only two columns but all columns are showing up. Please help.

Dim sq As SB.SqlQuery = New SB.Select("product.prodcode as 'Product Code'").From(DB.Product.Schema)
Dim wh As SB.Where = New SB.Where()
Dim prod As DB.ProductCollection = sq.ExecuteAsCollection(Of DB.ProductCollection)()

wh.ColumnName = DB.Product.ServiceColumn.PropertyName
wh.Comparison = SubSonic.Comparison.NotEquals
wh.ParameterValue = System.Decimal.One

Dim tab As DataTable = prod.Where(wh).Filter().ToDataTable()
Me.GridControl1.DataSource = tab

Answer 1

What you're doing doesn't make much sense - the where needs to go onto the query, then hit the DB - that's the way it should work. If you want to filter after the fact you can use Linq's Where(), which will filter the list for you.