Filtering output in a datagridview VB 2008

[dougancil]

I am running a stored procedure and presenting the data back to users in a simple datagridview. I'm unable to figure out a way to filter my data through the SQL side, so I wanted to see if I could filter data through the datagridview.

Basically my sql query returns back values of "activity" that are either added or deleted, and I only want to present data back to users that are "added.' Is there a way to do this by filtering the contents of the datagridview?

This is what I have in my codebehind for the page for both the SQL connection string, and the stored procedure. I'm assuming that this would be where I would filter the information.

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles oncallsubmitButton.Click
        Dim dt As New DataTable
        Dim da As New SqlDataAdapter
        Dim cmd As New SqlCommand
        Dim connectionString As String = "Initial Catalog=xxxxx;Data Source=xxxxx;uid=xxxxx;password=xxxxx"
        Dim con As New SqlConnection(connectionString)
        con.Open()
        cmd.Connection = con
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "sp_owncalls"
        cmd.Parameters.AddWithValue("@schedname", schednameTextBox.Text)

        Try
            da.SelectCommand = cmd
            da.Fill(dt)
            GridView1.DataSource = Nothing
            GridView1.DataBind()
            con.Dispose()
        Catch ex As Exception
            Response.Write("Error:" & ex.ToString)
            If (Me.GridView1.Rows.Count <= 0) Then
                nodataLabel.Text = "No data available"
            End If
        End Try
    End Sub

Any help is appreciated.

Thank you

Doug

[Oxiegen]

You can use a DataView to filter, sort and search through a DataTable.
Here is an example of how to filter using a DataView: http://vb.net-informations.com/dataview/filter-dataview.htm

[adam_k]

I am curious why you can't filter your data in the SQL.
It's not a good idea to send useless data to the client and have the client filter them out.

[Oxiegen]

You can filter it directly through SQL, using a WHERE clause.
But because you are using a Stored Procedure, the reasoning becomes that you might wish to reuse that particular SP for more than one task. Am I right?
That's why it's better to just grab it all and filter it later using a DataView.

[dougancil]

Oxiegen,

Not only that, but the data that we're using is difficult to filter using the Where clause, and so far no ones been able to offer me a comprehensive way to do so. Secondly, this data is being used internally, so if I ever do decide to push this to outward facing clients, I of course will try to fix my sql query. here's the code after I read the page you recommended:

Imports System.Data.SqlClient
Imports System.Data
Partial Class _Default
    Inherits System.Web.UI.Page
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles oncallsubmitButton.Click
        Dim ds As New DataSet
        Dim dv As DataView
        Dim da As New SqlDataAdapter
        Dim cmd As New SqlCommand
        Dim connectionString As String = "Initial Catalog=xxxxx;Data Source=xxxxx;uid=xxxxx;password=xxxxx"
        Dim con As New SqlConnection(connectionString)
        con.Open()
        cmd.Connection = con
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "sp_owncalls"
        cmd.Parameters.AddWithValue("@schedname", schednameTextBox.Text)

        Try
            da.SelectCommand = cmd
            da.Fill(ds, "Filter Dataview")
           [B] dv = New DataView(ds.Tables(0), "Activity = Added", DataViewRowState.CurrentRows)[/B]
            GridView1.DataSource = dv
            con.Dispose()
        Catch ex As Exception
            Response.Write("Error:" & ex.ToString)
            If (Me.GridView1.Rows.Count <= 0) Then
                nodataLabel.Text = "No data available"
            End If
        End Try
    End Sub

and on the bold line, Intellisense is giving me the warning:
"Overload resolution failed because no accessible 'New' accepts this number of arguments."

Incidentally if I remove the word New on that line, and just use the code:

dv = DataView(ds.Tables(0), "Activity = Added", DataViewRowState.CurrentRows)

Intellisense warns me with this:
"'Dataview' is a type and cannot be used as an expression."

What am I missing?

Thank you

Doug

[Oxiegen]

Oh you should definitely use the New keyword, since it's a class.
And you forgot to add an argument between "Activity = Added" and DataViewRowState.CurrentRows.
You need a sorting argument, however if you don't need to sort just add an empty string: "".

Also, if "Added" is a value stored as string in the database, then you need to enclose it within single-quotes. Like so: "Activity = 'Added'".
That's the equivalent of the SQL Where clause.

One last thing.
On this line: da.Fill(ds, "Filter Dataview")
Do you really have a table called "Filter Dataview" in your database?
You should type in the actual name of the source table.