How to filter rows out of a SQLDataSource in code-behind prior to loading GridView RSS

• • Reply
  

  DaveBF

  Member

  66 Points

  154 Posts

  How to filter rows out of a SQLDataSource in code-behind prior to loading GridView

  Dec 13, 2012 05:40 PM|LINK

  I have a sqldatasource hooked up to a Gridview.

  I would like to filter out some rows, during the sqldatasource_selected event.

  I cannot figure out how to get access to the datatable in the _selected event.  I found the following code which apparently works for an ObjectDataSource but how do I load the DataTable for a SQLDataSource?

  I think the first line of the function is the one which is wrong; I don't know what e.ReturnValue is.

  Protected Sub ds_Selected(sender As Object, e As ObjectDataSourceStatusEventArgs)
  	Dim dt As DataTable = DirectCast(e.ReturnValue, DataTable)
  	For j As Integer = 0 To dt.Rows.Count - 1
  		Dim r As DataRow = dt.Rows(j)
  		If r("SomeField") = specialCondition Then
  			dt.Rows.Remove(r)
  		End If
  	Next
  End Sub

• • Reply
  

  gregaDro

  Member

  318 Points

  65 Posts

  Re: How to filter rows out of a SQLDataSource in code-behind prior to loading GridView

  Dec 13, 2012 07:18 PM|LINK

  Try this

  Dim dv As New DataView
  Dim dt As New DataTable
    
  dv = mySQLDataSource.Select(DataSourceSelectArguments.Empty)
  dt = dv.ToTable()

• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: How to filter rows out of a SQLDataSource in code-behind prior to loading GridView

  Dec 14, 2012 08:11 AM|LINK

  DaveBF

  I would like to filter out some rows, during the sqldatasource_selected event.

  Hi,

  SqlDataSource isn't used for you to select records from filtering. I think if you'd like to filter records, you can:

  1) Just use dynamic SQL select statement for SqlDataSource, something like:

  Select * from xxx where [Column1]=@Value1

  And then use ControlParameter, QueryStringParameter,……assing value to "@Value1".

  2) You can also try to download the demo:

  http://www.codeproject.com/Articles/26969/GridView-Multiple-Filter-AJAX-Control

  • Marked as answer by decker dong - msft on Dec 19, 2012 04:16 AM
• • Reply
  

  DaveBF

  Member

  66 Points

  154 Posts

  Re: How to filter rows out of a SQLDataSource in code-behind prior to loading GridView

  Dec 14, 2012 10:58 AM|LINK

  Decker,

  Thanks for the reply.

  The problem is that the type of filtering I want to do is:

  Select * from xxx where [Column1] in (aaa,bbb,ccc,ddd)

  and I don't think you can do that with dynamic sql. Can you?

• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: How to filter rows out of a SQLDataSource in code-behind prior to loading GridView

  Dec 15, 2012 12:13 AM|LINK

  DaveBF

  and I don't think you can do that with dynamic sql. Can you?

  Yes, I can. Just try this:

  List<string> strings = new List<string>{"aa","bb","cc"};
  string s = "select * from xxx where fieldColumn in (')";
  
  s+=String.Join("','"strings.ToArray());
  s+="')";
  
  //Then use "s" for your SqlDataAdapter

  • Marked as answer by decker dong - msft on Dec 19, 2012 04:16 AM