Custom Filter

There are many ways to filter data in a grid, here one of them...

All you need is to create a DataSet connected with a table and a little code!
(in the example I use "AdventureWorks" as database and "Person.CountryRegion" as table)

Let's start!

As usual, we have to write an object to connect a GridView with a source like a dataset/database.

Public Function GetCountryRegion(Optional ByVal CountryRegionCode As String = "%", _
                                   Optional ByVal Name As String = "%") As ds_CountryRegion.CountryRegionDataTable

      Dim cnn As SqlConnection = Nothing
      Dim cmd As SqlCommand = Nothing
      Dim da As SqlDataAdapter = Nothing
      Dim sqlStatement As String

      Try
          sqlStatement = "SELECT * FROM Person.CountryRegion where CountryRegionCode like '" & CountryRegionCode & "%' and Name like '" & Name & "%'"

          cnn = New SqlConnection(Me._connectionString)
          cmd = New SqlCommand(sqlStatement, cnn)
          da = New SqlDataAdapter(cmd)

          cnn.Open()

          _ds.CountryRegion.Rows.Clear()
          da.Fill(_ds, "CountryRegion")
      Catch ex As Exception
          'todo: code for exceptions!
      Finally
          GetCountryRegion = _ds.CountryRegion

          If Not (cnn Is Nothing) Then
              cnn.Close()
              cnn.Dispose()
              cnn = Nothing
          End If
          If Not (da Is Nothing) Then
              da.Dispose()
              da = Nothing
          End If
          If Not (cmd Is Nothing) Then
              cmd.Dispose()
              cmd = Nothing
          End If
      End Try

  End Function

As you can see, I use the keyword "Optional" for the parameters and I use "%" as default value.
In T-Sql language "%" stands for "all". If there are no parameters passed, the code reads "all" CountryRegionCode records and "all" Name records.

Another interesting thing is the use of the keyword "like" and the use of "%" at the end of the parameters in the sqlStatement string ("like 'Name%'" and "like 'CountryRegionCode%'").

In this example I can catch all records with the Name that starts with the parameter passed: all records with "Name%" starts with "I%" (Italy, Ireland, Iceland...) (ok I know, it's not easy for me to explain in eng... I'm sorry!)

sqlStatement = "SELECT * FROM Person.CountryRegion where CountryRegionCode like '" & CountryRegionCode & "%' and Name like '" & Name & "%'"

When our object is ready and connected with a GridView through the "DataSource" property, we have to use the "Databind" property to associate the data with the GridView.

In the Page_Load event, there are no parameters passed to the DataTable object (in fact the two TextBox values are empty ---> "")

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

      If Not IsPostBack Then
          GetGwCountryRegion()
      End If

  End Sub

But each time that we write something in the TextBox Name or CountryRegionCode and click on "Filter", the DataTable recives parameters and use them for filtering.

That's all!

You can download this my little example here.
You can download and install AdventureWorksDB.msi database here.

Please remember to change in the web.config file, the two values "Data Source=pc01;" with
your sqlserver instance name. (probably "computername" with Sql2005/2008, but if you are using SqlExpress edition your instance name is "computername\SQLExpress")

Enjoy!

GridLines style in Gridview

A fast way to change the style of the gridlines in a gridview:

Protected Sub gwMyGw_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles gwMyGw.PreRender

     'PreRender event!
     Dim tblStyle As New TableItemStyle()
     tblStyle.BorderColor = Drawing.Color.White
     tblStyle.BorderWidth = "2"

     Dim row As TableRow
     For Each row In Me.gwMyGw.Rows
         Dim cel As TableCell
         For Each cel In row.Cells
             cel.ApplyStyle(tblStyle)
         Next
     Next

 End Sub

Enjoy !

How to... use an Ajax ConfirmExtender in a GridView

A typical case to use an ajax-confirmbutton within a gridview is to delete a row on DB, let's take a look...

So, in our gridview, we have to create an "ItemTemplate", then add a LinkButton and the ajax ConfirmButtonExtender.
Set the property of our Extender like "TargetContolId" (= the LinkButton ID) and the "Confirm Text" (= Are You sure, continue?).
In this way we have connected the extender with the LinkButton !
Now we must connect the LinkButton with the data from the source (DataTable-method or a Control DataSource) in particular with an unic "row-ID"
Then go to Click-Event of the LinkButton to add the code to delete the row on DB...
something like:

Protected Sub LinkButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
       'delete after Ajax-ConfirmButtonExtender
       Dim lb As LinkButton = sender
       Dim objDel As New clSessionTest(("ConnectionString"))
       objDel.EliTempTest(lb.CommandArgument)
       objDel = Nothing
End Sub