Wednesday, July 30, 2008

Another look at Exception Handling

Level: Beginner

Knowledge Required:
  • Exception Handling
As we have used the Exception Handling (Try...Catch) many times. But in this post we shall understand an important aspect. Consider the following code,

Private Sub InsertSomeData()
    Dim conn As SqlClient.SqlConnection = Nothing
    Dim cmd As SqlClient.SqlCommand
    Dim tran As SqlClient.SqlTransaction = Nothing
        ' initialize connection
        conn = New SqlClient.SqlConnection("Data Source=.;Initial Catalog=MyDatabase;Integrated Security=True")
        ' open connection
        ' begin transaction
        tran = conn.BeginTransaction

        ' create a Command
        cmd = conn.CreateCommand()

        ' setup command to insert data in 1st table
        cmd.CommandText = "INSERT INTO MyTable1(Column1) VALUES('Data in MyTable1.Column1')"

        ' setup command to insert data in 2nd table
        cmd.CommandText = "INSERT INTO MyTable2(Column1) VALUES('Data in MyTable2.Column1')"

        ' ok now commit the changes
    Catch ex As Exception
        ' an exception is occurred
        ' check if transaction has been initialized then
        ' rollback
        If tran IsNot Nothing Then
        End If
        ' again throw the exception
        ' this is because we want the caller to be notified
        Throw ex
        ' finally block will be executed anyway
        ' whether the exception is occurred or NOT
        ' ================---------------------
        ' Note: we are again throwing the exception in Catch block
        '       but still the following code will be executed
        If conn IsNot Nothing AndAlso _
           conn.State = ConnectionState.Open Then
            ' if connection was established then close it
        End If
        ' =====================-------------------------
    End Try
    ' this line however will NOT be executed if
    ' exception is occurred
    Debug.Print("Outside the Try..Catch block")
End Sub

As you can see this method will be inserting data in 2 tables. Our goal is,

Begin the Transaction
Try to Insert the Data in Tables
If no error occurs on Insertion then Commit the Transaction
If error occurred then
    Rollback the Transaction
    Again throw the Exception (so the caller is being notified)
End If
At the end (whether exception is occred or NOT) Close the connection

Note that if Exception occurs then in Catch block we are throwing the same exception again. At this point it seems that control will immediately exit from this method, but fortunately this does NOT happen in this way. Instead control first jumps to Finally block then after that it exits from method.

Enter Key to Set Focus in Next Control (for Data Entry Forms)

Level: Beginner

Knowledge Required:
Windows Forms

In a Windows Application, we use Tab key to move Focus from current control to next control, but in a Data Entry applications, usually Enter Key is used for this purpose.

There are different techniques to accomplish this. I think the following is the simplest one.

First set the Form's KeyPreview property to True. Then put the following code in the KeyPress Event Handler:

Private Sub Form1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles Me.KeyPress
    If e.KeyChar = Chr(Keys.Return) Then
        Me.SelectNextControl(Me.ActiveControl, True, True, True, True)
    End If
End Sub

Tuesday, July 29, 2008

Implement SQLCommand Cancel with Threading

Level: Advanced

Knowledge Required:
  • Threading
In a data driven application, sometimes we built queries which take time. In these type of scenarios usually developers put the query execution on different thread, so the User Interface keep responsive.

A more user friendly applications provide a flexibility to cancel the currently executing command. This can be achieved by using SQLCommand.Cancel() method.

So to implement it,
  • Create a Thread
  • Execute Query in that Thread
  • Meanwhile if user clicks the Cancel button we will call the SQLCommand.Cancel method
Cancel method actually tries to cancel the in-process query. When the attempt to cancel the query succeeds then an exception is occured at the same point where Command was executed i.e. SQLCommand.ExecuteReader (or other Execute method) was called. When the cancellation is failed then no exception occurs and command continues its execution. Therefore we also need to handle the Exception at the same point where we have executed the query. Here is an example:

Private Sub DoSomeWork()
    objCon = New SqlConnection("Data Source=.;Integrated Security=True")
    objCmd = New SqlCommand("WAITFOR DELAY '00:00:05';", objCon)

    Catch ex As SqlException
    End Try
End Sub

The above method just executes a WAITFOR DELAY query which simulates a very long query that takes 5 seconds to complete. Note that objCon and objCmd are Module Level variables. This method will be executed in a different thread using the following code,

Dim t As Threading.Thread
t = New Threading.Thread(AddressOf DoSomeWork)

While this query is being executed we will perform a query cancel on a button click event as,

Private Sub CancelButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
    If objCmd IsNot Nothing Then
    End If
End Sub

See Also:

Asynchronous Data Loading using TableAdapter with Cancel Feature

Monday, July 28, 2008

Draw Focus Rectangle using ControlPaint Class

Level: Beginner

Knowledge Required:

Sometimes while creating a User Control from scratch. It is required to display the Focus Rectangle (the dotted border). This can be easily achieved by using


Private Sub Form1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Me.Click
    Dim g As Graphics
    g = Me.CreateGraphics
    ControlPaint.DrawFocusRectangle(g, New Rectangle(10, 10, 100, 100))
End Sub

ControlPaint class contains other useful routines for control drawing you can explore the list using intellisense.

Saturday, July 26, 2008

Dynamically Loading Data in TreeView control

Level: Beginner

Knowledge Required:
  • Typed DataSet
  • TableAdapter
  • SQL Server 2005
In my post Dynamically Loading Data in TreeView Control, I have discussed how to load the data on runtime in TreeView control. This post is for Windows Application (Windows Forms) platform. Recently I was asked to have the same code for Web Application. I think there are lots of articles available on Internet about this kind of stuff. But I am also submitting my own.

This is a very simple project demonstrates how to dynamically load data when TreeView's Node is expanded.


Note: The zip file contains DynamicTreeViewDatabaseSetup.sql T-SQL Script which will,
1) Create a database "MyDatabase"
2) Create a table "tbl_Category" in that database
3) Create a Stored Procedure "CategorySelect"
4) Put some data in tbl_Category

Please execute this script before running the project.

Saturday, July 19, 2008

Function to Trim any Character/String from Left of varchar in SQL Server

An Extended Left Trim (LTRIM) Function for SQL Server which removes given string/character from Left of another varchar (String)

Level: Intermediate

Knowledge Required:
  • T-SQL
  • SQL Server 2005
  • SQL Server User Define Functions
The SQL Server's built-in LTrim() function removes only the blanks (spaces) from Left of a varchar (string). Here is a User Defined function which can remove any character.

CREATE FUNCTION [dbo].[LTrimString]
-- Add the parameters for the function here
@StringToTrim varchar(max),
@CharToTrim varchar(10)
RETURNS varchar(max)

-- Declare the return variable here
DECLARE @Result varchar(max);
DECLARE @i int;
DECLARE @l int;
DECLARE @sl int;

SET @i = 1;
SET @l = Len(@StringToTrim);
SET @sl = Len(@CharToTrim);
While @i < @l
SubString(@StringToTrim, @i, @sl) <> @CharToTrim
SET @Result = SubString(@StringToTrim, @i, @l - @i + 1);
@i = @i + @sl;

@Result = IsNull(@Result, @StringToTrim);

-- Return the result of the function
RETURN @Result;

Print dbo.LTrimString('00000005221', '0')


Thursday, July 17, 2008

Replace with Regular Expression

This article explains some outstanding usage of Regular Expression's Replace Method

Level: Intermediate

Knowledge Required:
Regular Expressions

Currently I am working on a Tool (Short Project) to convert the code into HTML. This is my requirement, since it takes time to convert the code into HTML format, before I can paste into Blogger.

So I will be heavily using Regular Expression (RegEx Class) in this project. Meanwhile I am sharing the core technique here.

In code we use Keywords, words that highlight with different color. For example "Dim", "Me", "As", "For", etc. I use "span" or "font" tag to change the text color. E.g.

<span style="color:blue;">Dim</span>

Usually keywords found to be separated with white-spaces,

Dim myVar As String

3 Keywords: Dim, As, String

All of them are separated with Space. But some keywords are used as Instances,

Me.TextBox1.Text = "ABC"

Here "Me" has "." (full stop)

As per above findings, I decided,

To Convert the Code into HTML replace the Keywords with the Tags

Me.TextBox1.Text = "ABC"

we can replace the above line as,

<span style="color:blue;">Me</span>.TextBox1.Text = "ABC"

So here we are using the Replace. Which gave me the idea to use the Regular Expression, because its Replace method is very flexible. First we need to decide the Pattern. For the above case the pattern will be,


In above statement () brackets are indicating Groups. Therefore in above pattern I have used 3 groups. Which we will be using in Replace String.

Next we need to decide what should be the Replace String.

$1<font color=blue>$2</font>$3

The above string means that while replacing,

1) Put Group #1
2) Put Font Tag
3) Put Group #2
4) Close the Font Tag
5) Put the Group #3

That's it, here is the complete code,

Dim sCode As String
Dim sHTML As String

sCode = "Me.TextBox1.Text=""Abc"""
sHTML = System. _
Text. _
RegularExpressions. _
Regex.Replace( _
sCode, _
"(\W|^)(Me)(\W|$)", _
"$1<font color=blue>$2</font>$3", _
System.Text.RegularExpressions.RegexOptions.IgnoreCase _

Wednesday, July 16, 2008

How to Pass Data Across Forms

In this post we will discuss how can we share different variables among Forms.

Level: Beginner

Knowledge Required:
Win Forms

While creating a Windows Forms Application, we usually face a requirement when we need to pass one or more variables from one Form to another. For example, we have created a Dialog Box which takes Date Range,

When we display this dialog box, we want that a default date range should be given, also this dialog box should return the new Date Range that is selected.

To pass the data to Form we can directly use Form's Control as,

In Form1.Button1.Click Event Handler:

DateRangeDialog.DateTimePicker1.Value = Now

As you can see the above code, I have used the DateRangeDialog's DateTimePicker control in Form1's Button Click event Handler, but it is better to create Public Properties on Form and use them. Also we can utilize the Constructor (New Method).

Public Class dlgDateRange
Private _DateStart As Date
Private _DateEnd As Date

'Public properties that will be used to get the selected dates
Public ReadOnly Property DateStart() As Date
Return Me
End Get
End Property

Public ReadOnly Property
DateEnd() As Date
Return Me.
End Get
End Property

' default constructor
Public Sub New
' This call is required by the Windows Form Designer.
' Add any initialization after the InitializeComponent() call.
Me._DateStart = Now
Me._DateEnd = Now
End Sub

' if dates are supplied on initializing
Public Sub New
(ByVal DateStart As Date, ByVal DateEnd As Date)
' Add any initialization after the InitializeComponent() call.
Me._DateStart = DateStart
Me._DateEnd = DateEnd
End Sub

' OK button is clicked
Private Sub
OK_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK_Button.Click
' set the selected values and close dialog
Me._DateStart = Me.DateTimePicker1.Value
Me._DateEnd = Me.DateTimePicker2.Value
Me.DialogResult = System.Windows.Forms.DialogResult.OK
End Sub

' cancel button is clicked
Private Sub
Cancel_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Cancel_Button.Click
Me.DialogResult = System.Windows.Forms.DialogResult.Cancel
End Sub

' on form load event we will set the dates which were supplied on initializing
Private Sub
dlgDateRange_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.DateTimePicker1.Value = Me._DateStart
Me.DateTimePicker2.Value = Me._DateEnd
End Sub
End Class

Dim dlgNew As dlgDateRange
dlgNew = New dlgDateRange(New Date(2008, 1, 1), New Date(2008, 6, 30))
If dlgNew.ShowDialog() = Windows.Forms.DialogResult.OK Then
Debug.Print("Date Start: " & dlgNew.DateStart)
Debug.Print("Date End: " & dlgNew.DateEnd)
End If

Monday, July 14, 2008

Custom Tab Control Layout

Level: Intermediate

Knowledge Required:
  • Tab Control
  • GDI+
In previous post,

Custom Tab Control

We have discussed how to change the Tab Control layout so that we can make it more attractive by changing the background color and tab buttons' color. As I have mentioned in that post that there are some other techniques too, so in this post we shall see a simple way.

By default Tab Control's Tab Buttons are Aligned Top,

So first we are going to change its alignment to Left, by setting the property:

Alignment = Left

If you have XP themes turned on then you may notice the weird layout of Tab Control. Don't worry we will make it fine.

As you may have noticed that Tabs are vertical, and our requirement is horizontal. So we can change the size of Tabs. But before we can do this we have to set the SizeMode property as,

SizeMode = Fixed

Now we can change the size by using the ItemSize property,

ItemSize = 30, 120

Width = 30 and Height = 120

After setting the Alignment = Left, Tab control rotates the Tabs which causes the Width and Height seem to be reversed. That is why when we increase Height, we see that width is increasing and when we increase width the height is effected.

Now Text will also be displaying, but vertically. Unfortunately there is no simple way to resolve this issue. For this purpose we have to write the Text by ourselves. To do this we will first set the DrawMode

DrawMode = OwnerDrawFixed

Now its our job to display the Text on Tabs. Since we have set the DrawMode property = OwnerDrawFixed, therefore we can use the DrawItem event as,

Private Sub TabControl1_DrawItem(ByVal sender As Object, ByVal e As System.Windows.Forms.DrawItemEventArgs) Handles TabControl1.DrawItem
Dim g As Graphics
Dim sText As String
Dim iX As Integer
Dim iY As Integer
Dim sizeText As SizeF
Dim ctlTab As TabControl

ctlTab = CType(sender, TabControl)

g = e.Graphics

sText = ctlTab.TabPages(e.Index).Text
sizeText = g.MeasureString(sText, ctlTab.Font)
iX = e.Bounds.Left + 6
iY = e.Bounds.Top + (e.Bounds.Height - sizeText.Height) / 2
g.DrawString(sText, ctlTab.Font, Brushes.Black, iX, iY)
End Sub

Download Source:

Customized Tab Control

Level: Intermediate

Knowledge Required:
  • Custom Controls
  • Tab Control
  • Inheritance
  • GDI+
In this post we will discuss how can we customize a Tab Control, as shown in above figure. There are different ways to customize a Tab Control. One way is to use Custom Painting by overriding the OnPaint method of Tab Control. We will use the same way here.

We will create a control, inherited from Tab Control. Then in the New() method (constructor), we will set style as,

Me.SetStyle(ControlStyles.AllPaintingInWmPaint, True)
Me.SetStyle(ControlStyles.UserPaint, True)

The 2nd line tells the Tab Control NOT to use its own Control Drawing, instead use the OnPaint method to draw the contents of control. Note that the Tab Pages will be rendered by themselves, we don't need to handle them.

In the OnPaint Method we will perform as,

1) Create the Background of Tab Control
2) Draw the Tab Buttons

For drawing the Tab Buttons we will use the GetTabRect() method, which returns the bounds of Tab Button to be displayed.

That is the core customization. Additionally I have added other properties,

BackColorControl's background color
HeaderWidthWidth of Tab Buttons
HeaderHeightHeight of Tab Buttons
HeaderAlignmentText Alignment in the Tab Button
HeaderPaddingPadding of Text in the Tab Button
HeaderBorderColorTab Button Border Color
HeaderFontFont of Tab Button
HeaderBackColorBackground Color of Tab Button
HeaderForeColorText Color of Tab Button
HeaderSelectedBackColorBackground color of Selected Tab Button
HeaderSelectedForeColorText Color of Selected Tab Button

Download the Source:

Tuesday, July 8, 2008

Bound TextBox Control Validation Issue

Level: Beginner

Knowledge Required:
Data Binding

Sometimes it happens that we bound TextBox with BindingSource for a Numeric Column, if we try to put a non-numeric value or a very large numeric value in that TextBox, the application stuck, i.e. cannot get out from TextBox, cannot close the Form. This is because internal validation is running.

Whenever we try to get out from TextBox control or try to close the Form, validation happens and if value (provided in TextBox) is NOT a legal value for that particular column then validation fails, which causes the focus to remain in the TextBox control.

To solve this issue just set the CausesValidation property of TextBox control to False. This will stop the automatic Validation.

Thursday, July 3, 2008

How to Check Mouse Buttons State

Level: Beginner

Sometimes it is required to check the Mouse Button state on a certain point. For this purpose we use MouseButtons shared property,


Example: in DataGridView SelectionChanged event handler we can determine whether the selection is changed through mouse or not.

Private Sub DataGridView1_SelectionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGridView1.SelectionChanged
If System.Windows.Forms.Form.MouseButtons = Windows.Forms.MouseButtons.Left Then
' Selection is Changed by mouse
' Selection is NOT Changed by mouse
End If
End Sub

Making Enter Key, Move to Next Cell/Column in DataGridView After Cell Edit

This article explains how we can customize the DataGridView control so that when Enter Key is pressed then cursor (current selection) move to next cell / column after Cell Edit.

Level: Intermediate

Knowledge Required:

By default in DataGridView control, when we press Enter Key to stop the editing in Current Cell, cursor moves to next Row and the current row is saved. This style is adopted from Excel, users working on Excel feel no problem with this. But users who worked in older applications do NOT like this type of editing. What they want is when Enter Key is pressed then Cursor should move to next cell / column. In DataGridView control we need to press TAB key to achieve this.

Now we are going to customize the DataGridView control, so that when User Stop the Editing in Current Cell by pressing Enter Key then cursor should move in next cell / column.

To understand the solution first note the followings,

1) When user presses enter key to stop the Editing, DataGridView CellEndEdit event occurs
2) Then cursor (current selection) moves to next row, on this point SelectionChanged event occurs

To achieve this I have used a logic,

1) When CellEndEdit event occurs, I note the Cell which was Edited
2) Then in SelectionChanged event, I first check if this event is occured after the Editing, then I set the cursor (current selection) in the same row but next column of last edited cell

Here I have created a customized control which is actually inherited from DataGridView control, and have added this functionality.

Public Class DataGridViewEnterMoveNext
Inherits DataGridView

Dim celWasEndEdit As DataGridViewCell
Private _EnterMoveNext As Boolean = True

<System.ComponentModel.DefaultValue(True)> _
Public Property OnEnterKeyMoveNext() As Boolean
Return Me
End Get
(ByVal value As Boolean)
Me._EnterMoveNext = value
End Set
End Property

Private Sub
DataGridView_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles Me.CellEndEdit
Me.celWasEndEdit = Me(e.ColumnIndex, e.RowIndex)
End Sub

Private Sub
DataGridView_SelectionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.SelectionChanged
' if Enter Move Next should work andalso
' mouse button was NOT down
' we are checking mouse buttons because if select was changed
' by Mouse then we will NOT do our Enter Move Next

If Me._EnterMoveNext AndAlso MouseButtons = 0 Then
' if selection is changed after Cell Editing
If Me.celWasEndEdit IsNot Nothing AndAlso _
Me.CurrentCell IsNot Nothing Then
' if we are currently in the next line of last edit cell
If Me.CurrentCell.RowIndex = Me.celWasEndEdit.RowIndex + 1 AndAlso _
Me.CurrentCell.ColumnIndex = Me.celWasEndEdit.ColumnIndex Then
Dim iColNew As Integer
Dim iRowNew As Integer
' if we at the last column
If Me.celWasEndEdit.ColumnIndex >= Me.ColumnCount - 1 Then
iColNew = 0 ' move to first column
iRowNew = Me.CurrentCell.RowIndex ' and move to next row
Else ' else it means we are NOT at the last column
' move to next column

iColNew = Me.celWasEndEdit.ColumnIndex + 1
' but row should remain same
iRowNew = Me.celWasEndEdit.RowIndex
End If
Me.CurrentCell = Me(iColNew, iRowNew) ' ok set the current column
End If
End If

Me.celWasEndEdit = Nothing ' reset the cell end edit
End If
End Sub
End Class

Note that I have added a property OnEnterKeyMoveNext if this property is True then our customization will work otherwise NOT.

As you can see when user presses enter key, the row will be first saved in DataGridView control (ultimately the Source of DataGridView is updated), at this point if one or more columns are NOT allowed to have NULL values then our customization will NOT work properly. Since exception will be thrown and the row will be deleted. To overcome this issue we can set the AllowDBNull = False in our DataTable (which is bind with DataGridView) and use the custom validation as discussed in my previous post.

How to add Column/Row Validation Using Typed DataTable

Download Source Code:

See Also:
DataGridView control FAQs and Quick Tips
Paste Text in DataGridView Control (Bound with BindingSource)

Wednesday, July 2, 2008

Changing Connection String in TableAdapter

This article describes a way to change a Connection String in TableAdapter

Level: Intermediate

Knowledge Required:
  • Typed DataSet
  • TableAdapter
  • Partial Class
  • Project Settings
As we have used the DataSet Designer from Visual Studio, to create a Typed DataSet. Usually our Typed DataSet contains TableAdapter which interacts with Physical Database. When we first time add Typed DataSet using DataSet Designer, it adds a Connection String in the Project Setting. This connection string is by default accessed by the TableAdapter. Note that the connection string in Project Setting is defined with Scope = Application, which makes it a read-only property, therefore cannot be changed from code.

But sometimes it is required to change the Connection String, for example if different users log in the software then connection string will be different for each user with respect to their Login IDs and Passwords.

So to resolve this issue we can directly access the Connection property of TableAdapter to set the ConnectionString.

MyTableAdapter.Connection.ConnectionString = "...."

But another approach is to modify the TableAdapter using Partial Class and add methods that shall allow us change the Connection String, example,

Namespace CityDataSetTableAdapters
Partial Class CityTableAdapter
Public Sub New(ByVal sConStr As String)
Me.Connection.ConnectionString = sConStr
End Sub

Public Sub SetNewConnectionString(ByVal sConStr As String)
Me.Connection.ConnectionString = sConStr
End Sub
End Class
End Namespace

Now we can create another Setting in our Project with Scope = User and then we will change this setting according to current login in our code.

Dim adpCity As CityDataSetTableAdapters.CityTableAdapter
Dim tblCity As CityDataSet.CityDataTable

My.Settings.MyConnectionString = "Data Source=.;Initial Catalog=MyDatabase;Persist Security Info=True;User ID=user;Password=password"
adpCity = New CityDataSetTableAdapters.CityTableAdapter(My.Settings.MyConnectionString)
tblCity = adpCity.GetData()

Tuesday, July 1, 2008

Using DateTimePicker Control to Take Year Input

Level: Beginner

While creating the User Interface (UI) we need to create a control which can take Year input. For this purpose we can use NumericUpDown control or can use just simple TextBox. But all these controls may require more coding to validate a Year.

DateTimePicker control reduces our work in this type of scenario. We can customize the look and feel of this control to fulfill our UI need. To setup change the following properties in Design View,

Format = Custom
CustomFormat = yyyy
ShowUpDown = True

Now we can check which Year is selected in our code as,

Dim intSelectedYear As Integer
intSelectedYear = Me.DateTimePicker1.Value.Year