T-SQL Search String From Right / Reverse Search

DECLARE @SomeText varchar(255);
DECLARE @TextToSearch varchar(10);

SET @SomeText = 'There are 3 spaces';
SET @TextToSearch = ' ';

Print CharIndex(@TextToSearch, @SomeText);
-- Following line will search @TextToSearch in @SomeText from right
Print
        CASE WHEN CharIndex(@TextToSearch, @SomeText) > 0 THEN
            (Len(@SomeText + '-') - 1) - 
                (CharIndex(Reverse(@TextToSearch), Reverse(@SomeText)) + (Len(@TextToSearch + '-')-1) - 1) + 1
        ELSE
            0
        END

-- Output:
-- -------------------------------------
-- 6
-- 12
-- -------------------------------------
-- Tips: 
--
-- Reverse() function reverses the string
--
-- Len(@SomeText + '-') - 1, returns the actual string length even if
-- @SomeText have space at the end. Note that Len() function ignores
-- the spaces at the end of string that is why we have placed a '-'
-- at the end of string then subtracted 1 from length so that Len()
-- function should return the correct length

Loading and Disposing Crystal Reports on ASP.net Page

Recently one of my friend mentioned strange Crystal Reports behavior on ASP.net page. The scenario was simple,

1. ASP.net web page
2. A Button
3. A CrystalReportViewer Control
4. A simple Crystal Report

What he doing was, on clicking of button he was loading the report in CrsytalReportViewer control. The report was being displayed correctly, but when he was trying to Zoom In, Zoom Out or Exporting the report he was getting error “No valid report source is available”.

Understanding Variable Scope in Anonymous Methods / Lambda Statements

There are 2 goals of this article. 1) to understand the variable scope in anonymous methods and 2) to look at a real world scenario where we have used the anonymous methods effectively.

DataGridView control change Selection on Mouse Up

Level: Intermediate

Knowledge Required:

• DataGridView Control
• Windows Forms

Description:
Recently, one of our friend "Luc" asked me,

Is there an "easy" way to change the selection behavior in a datagridview so that a click on a selected row does not toggle the selection as long as the mouse button is not released, without resorting to a user-defined control with inheritence and method overrides ?

So I was about to paste the whole code in comments but that was looking a mess. So I decided to publish a new post, after a very long time :).

The solution "is" simple. We just need to look what is the event sequence. So whenever we press mouse button on a cell or row of DataGridView control, events trigger in this way,

1. MouseDown
2. SelectionChanged
3. MouseUp

When MouseDown event occurs Selection does NOT change. So in this event handler we can save the current row in some variable and then in SelectionChanged event handler we can select previously saved row, in this way user will experience that selection never changed.

And here is the code,

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.DataGridView1.Columns.Add("Column1", "Column1")
        Me.DataGridView1.Columns.Add("Column2", "Column2")
        Me.DataGridView1.Columns.Add("Column3", "Column3")

        Me.DataGridView1.Rows.Add("laksjdf", "alksjfsdf", "aksldjf")
        Me.DataGridView1.Rows.Add("laksjdf", "alksjfsdf", "aksldjf")
        Me.DataGridView1.Rows.Add("laksjdf", "alksjfsdf", "aksldjf")
        Me.DataGridView1.Rows.Add("laksjdf", "alksjfsdf", "aksldjf")
        Me.DataGridView1.Rows.Add("laksjdf", "alksjfsdf", "aksldjf")
        Me.DataGridView1.Rows.Add("laksjdf", "alksjfsdf", "aksldjf")
        Me.DataGridView1.Rows.Add("laksjdf", "alksjfsdf", "aksldjf")

        ' ***************************************
        ' IMPORTANT: MultiSelect property of DataGridView control should be False
        '            The result maybe same but if we set it to true then you 
        '            may experience the flickering
        ' ***************************************
        Me.DataGridView1.MultiSelect = False
    End Sub

    Private _MouseDown As Boolean
    Private _PreviousSelectedCell As DataGridViewCell
    Private _NewSelectedCell As DataGridViewCell

    Private Sub DataGridView1_MouseDown(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles DataGridView1.MouseDown
        ' if left button is clicked
        If e.Button = Windows.Forms.MouseButtons.Left Then
            Me._MouseDown = True    ' we will mark a flag that mouse is down
            ' and will note the row before the selection changed
            Me._PreviousSelectedCell = Me.DataGridView1.CurrentCell
        End If
    End Sub

    Private Sub DataGridView1_SelectionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGridView1.SelectionChanged
        If Me._MouseDown Then       ' if this event is triggerd after the mouse down
            ' we will first note the new row on which mouse was clicked
            Me._NewSelectedCell = Me.DataGridView1.CurrentCell
            ' then change the selection back to the one before mouse was down
            Me.DataGridView1.CurrentCell = Me._PreviousSelectedCell
        End If
    End Sub

    Private Sub DataGridView1_MouseUp(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles DataGridView1.MouseUp
        If Me._MouseDown Then       ' if this event is triggered after the mouse down
            ' we will set the selection to the new row which was clicked
            Me.DataGridView1.CurrentCell = Me._NewSelectedCell
            Me._MouseDown = False   ' reset the flag
        End If
    End Sub

End Class