Visual Basic Development Issues

Useful Links for SQL Server

2009-09-24T12:24:00.003+06:00

Stuck while manually inserting values in an Identity column? Here is what you need to do How to Insert Values into an Identity Column in SQL Server

Here is a great topic for beginners to learn about the Execution Plans in SQL Server Beginners topic for understanding Execution Plans in SQL Server

Is your SQL Server eating your CPU too much? Don't know which Query or Stored Procedure is behind this? A must have tool for SQL Server Making the Most Out of the SQL Server 2005 Performance Dashboard

Are you an advanced SQL Server 2005 Programmer? Do you know about CROSS APPLY? Using CROSS APPLY in SQL Server 2005

Importing IIS Web Log into SQL Server 2005

2009-05-07T16:15:00.003+06:00

Today I was trying to import the IIS log of my Web Server into SQL Server 2005 Database (for further analyses). So first I started googling about it and found this link,

How To Use SQL Server to Analyze Web Logs

Very useful article BUT this one is old maybe because the table have different schema. By the way I have IIS 6.0 on my web server. So I did some workarounds and successfully imported the Log. Now I am posting here in case someone might be trying to do the same thing.

Step #1: Create a table in Database

CREATE TABLE [dbo].[IISLog](
 [date] [datetime] NULL,
 [time] [datetime] NULL,
 [site-name] [varchar](255) NULL,
 [s-computername] [varchar](255) NULL,
 [s-ip] [varchar](50) NULL,
 [cs-uri-stem] [varchar](255) NULL,
 [cs-uri-query] [varchar](2048) NULL,
 [c-ip] [varchar](50) NULL,
 [cs(User-Agent)] [varchar](2048) NULL,
 [cs(Cookie)] [varchar](2048) NULL,
 [cs(Referer)] [varchar](2048) NULL,
 [sc-status] [int] NULL,
 [sc-bytes] [int] NULL,
 [cs-bytes] [int] NULL,
 [time-taken] [int] NULL
)

Step #2: Prepare the Log file (since it contains some description lines on top)

So this is a tricky step. As the Log file contains some description lines on top starting with "#" sign. Therefore SQL Server will NOT be able to import it. One more thing is that these log files can be large (or very large). The Log file I had was of size aprox. 216 MB. So obviously we cannot open it in NotePad etc.

The same article provides a small utility which removes the line, but I think there is bug in this utility, cause it is limiting file upto 43 MB. So I decided to write my own version.

PrepIISLogFileForImport.zip

As I started to create this application for IIS Log import but then it ends up with a generic utility. Which actually displays the text file content and have an option to skip number of lines from start. Therefore we can use it as,

C:\>PrepIISLogFileForImport C:\LogFile.Log skip=4 >newlogfile.txt

Step #3: Bulk import the Log file into SQL Server Table

Hence the final step is to import the log file in the same table. Which can be done by,

BULK INSERT [dbo].[IISLog] FROM 'C:\newlogfile.txt'
WITH (
    FIELDTERMINATOR = ' ',
    ROWTERMINATOR = '\n'
)

SQL Server Date Time Useful Functions

2009-03-09T16:24:00.009+05:00

Here are some SQL Server Date Time Useful functions.

DateCreate - Creates Date through Year, Month and Day
DateToStrShort - Accepts Date and returns as '1-Jan-2009'
DateGetDaysInMonth - Accepts Month and Year and returns number of days in month
DateGetTimeOnly - Accepts date and returns time as '10:33 PM'
DateGetDateOnly - Accepts Date and convert it into Date only i.e. removes the Time Part
DateGetMonthFirstDate - Gets the first date of Month
DateGetMonthLastDate - Gets the last date of Month
DateGetMonthName - Gets the Month Name

Review the functions below or download the SQL file.

-- =============================================
-- Author:        Arsalan Tamiz
-- Description:   This function accepts Year, Month and Day
--                and returns Date
-- =============================================
CREATE FUNCTION [DateCreate]
(
    @Year int,
    @Month int,
    @Day int
)
RETURNS datetime
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result datetime;

    -- Add the T-SQL statements to compute the return value here
    SET @Result = CAST    (
                            CAST(@Year AS varchar(10)) + '-' +
                            CAST(@Month AS varchar(2)) + '-' + 
                            CAST(@Day AS varchar(2))
                            AS datetime
                          );

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

-- =============================================
-- Author:        Arsalan Tamiz
-- Description:   This function converts the given
--                date in to string. Useful function
--                for displaying date as '10-Jan-2009' for example
-- =============================================
CREATE FUNCTION [DateToStrShort] 
(
    -- Add the parameters for the function here
    @DateToConvert datetime
)
RETURNS varchar(50)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result varchar(50)

    SET @Result = CAST(Day(@DateToConvert) AS varchar(50)) + '-' + 
                    Left(DateName(m, @DateToConvert), 3) + '-' + 
                    CAST(Year(@DateToConvert) AS varchar(50))

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

-- =============================================
-- Author:        unknown (I copied it from internet)
-- Description:   Returns the Days in a Month
--                parameters = Year and Month
-- =============================================
CREATE FUNCTION [DateGetDaysInMonth] (@Year int, @Month int)
RETURNS INT
AS
BEGIN

    RETURN CASE WHEN @Month IN (1, 3, 5, 7, 8, 10, 12) THEN 31
                WHEN @Month IN (4, 6, 9, 11) THEN 30
                ELSE CASE WHEN (@Year % 4    = 0 AND
                                @Year % 100 != 0) OR
                               (@Year % 400  = 0)
                          THEN 29
                          ELSE 28
                     END
           END

END

-- =============================================
-- Author:  Arsalan Tamiz
-- Description: Gets the Time from Date
-- =============================================
CREATE FUNCTION [DateGetTimeOnly] 
(
 -- Add the parameters for the function here
 @DateToFormat datetime
)
RETURNS varchar(100)
AS
BEGIN
 -- Declare the return variable here
 DECLARE @Result varchar(100);
 
 DECLARE @H varchar(3);
 DECLARE @Hour int;
 DECLARE @M varchar(3); 

 SET @Hour = DatePart(hh, @DateToFormat);
 SET @M = DateName(mi, @DateToFormat);
 If @M = '0' SET @M = '00';

 -- Add the T-SQL statements to compute the return value here
 SET @H = CAST(CASE WHEN @Hour > 12 THEN @Hour - 12 ELSE CASE WHEN @Hour = 0 THEN 12 ELSE @Hour END END AS varchar);
 
 SET @Result = @H + ':' + @M + ' ' + CASE WHEN @Hour >= 12 THEN 'PM' ELSE 'AM' END;
 
 -- Return the result of the function
 RETURN @Result;
END

-- =============================================
-- Author:  Arsalan Tamiz
-- Description: Returns Date only from given Date
--    That is removes the Time Part
--    so that we can compare two dates
-- =============================================
CREATE FUNCTION [DateGetDateOnly] 
(
 -- Add the parameters for the function here
 @DateToConvert datetime
)
RETURNS datetime
AS
BEGIN
 -- Declare the return variable here
 DECLARE @Result datetime;

 -- Add the T-SQL statements to compute the return value here
 SET @Result = CAST(CONVERT(varchar(100), @DateToConvert, 112) AS datetime);

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

-- =============================================
-- Author:  Arsalan Tamiz
-- Description: Gets the first date of month
-- =============================================
CREATE FUNCTION [DateGetMonthFirstDate] 
(
 -- Add the parameters for the function here
 @Month int
)
RETURNS datetime
AS
BEGIN
 -- Declare the return variable here
 DECLARE @Result datetime;
 DECLARE @Year int;

 SET @Year = Year(GetDate());

 SET @Result = CAST (
    CAST(@Year AS varchar(10)) + '-' + 
    CAST(@Month AS varchar(2)) + '-1' AS datetime
    );

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

-- =============================================
-- Author:  Arsalan Tamiz
-- Description: Returns last date of month
-- ***********************************************
-- IMPORTANT: This function depends on [dbo].[DateGetDaysInMonth]() function
--    which can be found above
-- ***********************************************
-- =============================================
CREATE FUNCTION [DateGetMonthLastDate] 
(
 -- Add the parameters for the function here
 @Month int
)
RETURNS datetime
AS
BEGIN
 -- Declare the return variable here
 DECLARE @Result datetime;
 DECLARE @Year int;
 
 SET @Year = Year(GetDate());
 
 SET @Result = CAST (
    CAST(@Year AS varchar(10)) + '-' + 
    CAST(@Month AS varchar(2)) + '-' +
    CAST([dbo].[DateGetDaysInMonth](@Year, @Month) AS varchar(2)) +
    AS datetime
    );

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

-- =============================================
-- Author:  Arsalan Tamiz
-- Description: This function Retuns the name of month
-- =============================================
CREATE FUNCTION [DateGetMonthName] 
(
 -- Add the parameters for the function here
 @Month int
)
RETURNS varchar(100)
AS
BEGIN
 -- Declare the return variable here
 DECLARE @Result varchar(100)
 DECLARE @d datetime;

 SET @d = CAST('2009-' + CAST(@Month AS varchar(2)) + '-1' AS datetime);

 -- Add the T-SQL statements to compute the return value here
 SET @Result = DateName(mm, @d);

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

Notification Bar Control For Windows Forms (Win Forms)

2009-02-14T11:01:00.004+05:00

Here is simple Notification Bar control (like we see in Internet Explorer). Gives application a good look. Note that I haven't done much thing, you can customize is further if you like. It supports blinking too.

Usage:
ShowNotification() method to display the Notification.
BlinkTimes property to set the Number of Times to blink.

Download Source Code:
Notificationbar.zip

DataGridView Custom Cell ToolTip

2009-01-26T12:44:00.003+05:00

Level: Intermediate

Knowledge Required:

DataGridView Control
Windows Forms

Description:
We have a built-in ToolTip for each Cell in DataGridView control. Which is displayed when the text is long and cannot be displayed completely in Cell. But my requirement was to create a custom ToolTip (as displayed in above image).

There were 2 main requirements,

1) ToolTip should be displayed permanently that is, it should NOT be disappeared automatically after sometime.
2) I want to have a fancy look of my ToolTip (again see the image).

For this purpose we can also use the .net's built-in ToolTip control. Which can have a little fancy look by setting some of its properties like IsBubble, ToolTipIcon and ToolTipTitle. Also the ToolTip control can be more customized by using OwnerDraw mode. But still I would like to use my own ToolTip window. Because to me, this is more easier to do.

Simply I implemented it by creating a new window for ToolTip and I show this window when mouse enters in a cell. But to make it more user friendly, I have used a Timer control. So that whenever mouse enters in a Cell I start timer and in Timer's Tick Event Handler, I display the ToolTip window.

Few things to be considered here,

1) DataGridView's ShowCellToolTips property should be set to False
2) Decide where to display the ToolTip window

So in my case I decided to display the ToolTip window just on the Cell. OK this is NOT just straight. We first need to get the Cell's actual Coordinate in DataGridView by executing DataGridView's GetCellDisplayRectangle() function. Then we need to convert these coordinates into Screen's Coordinates by calling the DataGridView's PointToScreen() function.

Please note that, to display our own custom ToolTip window on our given position we need to set its property StartPosition = Manual, which I have discussed in my earlier post Setting Window / Form Position Programmatically

Another thing I have added in the ToolTip window is the Close Button. Which is actually a NON Focusable button as I have discussed my previous post NOT Focusable / NOT Selectable Button

Download Source:
DataGridViewCustomCellToolTip.zip

NOT Focusable / NOT Selectable Button

2009-01-23T15:10:00.006+05:00

Level: Intermediate

Knowledge Required:
User Controls

Description:
Few days back I was creating a user control like ComboBox. In ComboBox we have a Drop Down button at the right side, which cannot have focus. This button can only be clicked by mouse to open the Drop Down portion (Alt+Down is the shortcut).

Similarly my control also have a button on right. I decided to make that button just like the Drop Down button of ComboBox. For this purpose I added a plain Class in my project and then put the following Code,

Public Class NotSelectableButton
    Inherits Button

    Public Sub New()
        MyBase.New()
        ' following line will make this button Not Focusable
        SetStyle(ControlStyles.Selectable, False)
    End Sub
End Class

FTP File Upload Error

2009-01-17T12:28:00.005+05:00

It seems that our Network Administrator has changed some settings because my previous file uploading code is NOT working. And giving the following exception:

The server returned an address in response to the PASV command that is different than the address to which the FTP connection was made.

I was using the VB.net My namespace,

My.Computer.Network.UploadFile("C:\SomeFile.txt", "ftp://ftpsite/somefile.txt", "userid", "pwd")

So I Googled about that error and found some dicussion on MSDN forums.

Then I modified and created my own upload function as,

Private Sub UploadFile(ByVal sDestination As String, ByVal sSource As String, ByVal sUserID As String, ByVal sPassword As String)
    Dim i As System.Net.FtpWebRequest
    Dim us As System.IO.Stream
    Dim filebytes As Byte()

    ' create the FTP Web Request
    i = System.Net.WebRequest.Create(sDestination)
    ' set credentials
    i.Credentials = New System.Net.NetworkCredential(sUserID, sPassword)
    ' method = Upload File
    i.Method = System.Net.WebRequestMethods.Ftp.UploadFile
    i.UsePassive = False
    i.Proxy = Nothing
    ' get the Request Stream
    us = i.GetRequestStream()
    ' get the file bytes from source
    filebytes = My.Computer.FileSystem.ReadAllBytes(sSource)
    ' write the bytes in stream
    us.Write(filebytes, 0, filebytes.Length)
    ' close the stream
    us.Close()
End Sub

Warning! Above function will read all the bytes (in one go) from file. Which is NOT a good practice for larger files.

Another way of accessing a file on Web Server (Javascript/ASP.net)

2009-01-02T11:14:00.006+05:00

Few days back while working on back-end of a Web Portal. I was trying to create an Anchor Tag (<a>) in such a way that a log should also be created whenever that anchor tag is clicked by user.

So there are couple of techniques.

Approach #1:
On Click: Post back to server create the log and redirect to the URL.

Approach #2:
Use AJAX

So I used the 2nd approach. For this purpose I first created a Javascript function as,

First Try:

function createlog(param1, param2) {
 var xmlHttp;
 try
  {  // Firefox, Opera 8.0+, Safari
  xmlHttp=new XMLHttpRequest();  
 }
 catch (e)
 {  // Internet Explorer
  try
  {
   xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");    
  }
  catch (e)
      {
   try
   {
    xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
   }
       catch (e)
   {
    alert("Your browser does not support AJAX!");
    return false;
   }
  }
 }
 try
 {
  var u;
  u = "createlog.aspx?param1=" + param1 + "&param2=" + param2;
  xmlHttp.open("POST", u, false);
  xmlHttp.send(null);
  return true;
 }
 catch (e)
 {
  return false;
 }
}

So this is an example function. Which will create a log by executing a "createlog.aspx" file on server which accepts 2 parameters in QueryString. The above function is simply using the XMLHttpRequest() object for AJAX call.

Then we can use this function in an anchor tag as,

<a href="http://www.domain.com" onclick="return createlog(1, 2);">www.domain.com</a>

When user will click on the link the createlog() function will be executed first. Which then will create the log on server and then redirect to the link.

Second Try:
Secondly I looked in the Google's Result Page. Because they are doing this also. Whenever we click on a result, Google redirects to that URL directly (without posting back) but also keeps the track of clicking. I looked into their Javascript and found a very interesting thing. And hence modified my Javascript function to this,

function createlog(param1, param2) {
 var u = "createlog.aspx?param1=" + param1 + "&param2=" + param2;
 (new Image).src = u;
 return true;
}

Because my requirement is to just execute the "createlog.aspx" file on the server. I do NOT need the returned value. That is why XMLHttpRequest() is NOT critically required in this scenario. On the other hand (new Image).src approach is lot simpler.

What happens is that the browser creates an Image object and go to the "source" (in this case my createlog.aspx) which actually do NOT return any data (i.e. image bytes). But by going to that URL it actually is executed on server. And the Image object is then discarded at the end of function (I guess).

One more thing I found was Google is using "onmousedown" event instead of "onclick". I don't know exactly the benefits but I have experience that "onclick" event is NOT fired (on some browsers) when we right click and open the link in new window or tab. "onmousedown" event is triggered each time we click on link even if we right click on it. So it seems that this is NOT a good approach to use "onmousedown". But I have checked that whenever we right/left click on the link for the first time, the file "createlog.aspx" is executed on server. But it does NOT execute, if we click again. I think this is due to cache maybe.

Image to Byte Array and Byte Array to Image

2008-12-23T12:14:00.004+05:00

Level: Intermediate

Knowledge Required:
Image Class

Description:
Here are simple functions that convert an Image into Byte Array and Byte Array into Image. An image is required to be converted into bytes array when we save it in Database, hence on reading the bytes from database, it needs to be converted back into image.

Private Function BytesToImage(ByVal ImageBytes() As Byte) As Image
    Dim imgNew As Image
    Dim memImage As New System.IO.MemoryStream(ImageBytes)
    imgNew = Image.FromStream(memImage)
    Return imgNew
End Function

Private Function ImageToBytes(ByVal Image As Image) As Byte()
    Dim memImage As New System.IO.MemoryStream
    Dim bytImage() As Byte

    Image.Save(memImage, Image.RawFormat)
    bytImage = memImage.GetBuffer()

    Return bytImage
End Function

Java Script Function to Show Flash Banner

2008-12-18T22:44:00.003+05:00

Usually I develop Desktop Application. But sometimes I also get involve in Web Development. So we were developing a Website in which we were trying to create a Flash Banner rotator. To accomplish this there are several techniques, but I created the following Java Script function for our Web Developer.

function showflashbanner(htmlobjectid, flashfilename, width, height) {
    var script;
    script = "<object classid='clsid:D27CDB6E-AE6D-11cf-96B8-444553540000' " +
             "codebase='http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=9,0,28,0' " +
             "width='" + width + "' height='" + height + "' \>" +
             "<param name='movie' " +
             "value='" + flashfilename + "' /\>" +
             "<param name='quality' value='high' /\>" + 
             "<embed src='" + flashfilename + "' " + 
             "quality='high' " +
             "pluginspage='http://www.adobe.com/shockwave/download/download.cgi?P1_Prod_Version=ShockwaveFlash' " +
             "type='application/x-shockwave-flash'" +
             "width='" + width + "'" +
             "height='" + height + "'\>'" +
             "</embed\>" +
             "</object\>";
    document.getElementById(htmlobjectid).innerHTML = script;
}

Example:

<body onload="showflashbanner('bannercontainer', 'http://www.mywebsite.com/banners/banner1.swf', 720, 100)">
<div id="bannercontainer">Loading...</div>
</body>

As you can see the above example we have used "div" as a Banner container. Then we are passing this div's id in our function so the Flash File will be loaded in it.

The Web developer then used this function by creating an Array of Flash Files. He also created another function in which he used setTimeout function. So the function calls itself after some time with new Flash File.

Setting Window / Form Position Programmatically

2008-11-18T14:21:00.003+05:00

Level: Beginner

Knowledge Required:
Windows Forms

Description:
While working with Windows Forms we sometimes require to set the Window/Form Position manually before we show it. Consider the following code:

Dim frmNew as Form2
frmNew = New Form2()
frmNew.Location = New Point(10, 10)
frmNew.Show()

If we execute the above code, it is NOT sure that window will be displayed at 10, 10. Because we haven't set the StartPosition property. By default this property is equal to WindowsDefaultLocation that is why whenever we display the Form, Windows Operating system choose where to put it. And if we change Location after the frmNew.Show(), then our location will set.

To set the Location programmatically we should first set the StartPosition property as,

Dim frmNew as Form2
frmNew = New Form2()
frmNew.StartPosition = FormStartPosition.Manual
frmNew.Location = New Point(10, 10)
frmNew.Show()

HTML Anchor Tag ( Link) with NO Redirection

2008-10-16T11:52:00.002+06:00

Although this Blog is purely for Visual Basic Windows Forms Application Development. But I think we can discuss other stuff too.

So meanwhile working with HTML, I was trying to create an Anchor with a href defined but the link should NOT work. Click the following link,

Google

As you can see the above link should redirect this page to Google, but it is NOT redirecting it. OK now try to open the link in a new Tab (if you are using a borwser which has tabbed browsing support, i.e. Ctrl+Click). This time it should work. Here is the HTML,

<a href="http://www.google.com" onclick="return false;">Google</a>

As you can see in above "onclick" event we have executed a little javascript which returns false. This is the statement which is suppressing the default behaviour of anchor. Some sites handle it like this,

Google

<a href="javascript:void(0);" onclick="alert('You Clicked the Link');">Google</a>

Binding DataGridView ComboBox Column

2008-09-14T13:27:00.006+06:00

Level: Beginner

Knowledge Required:

Data Binding
DataGridView

Description:
We use DataGridView control lots of times while developing a Data Driven Application. We usually bind the DataGridView control with some BindingSource. Also we can bind the DataGridView's ComboBox Column to some other BindingSource. That is DataGridView has different DataSource and it's ComboBox column has a different. Consider the following scenario,

We have following Typed DataSet,

OrderDataSet

First we will create a simple User Interface,

As you can see in the above figure, the DataGridView control is bind with OrderDetail Table. Note that the Product_ID column is displaying ID which is NOT a friendly approach. Instead Product Name should be displayed here. We can use the DataGridView's ComboBox Column here, in which we will populate all the products.

The main point to be focused here is that, we will be binding the DataGridView control with the same OrderDetail DataTable, in which there is no Product_Name column. But on front-end the Product_ID column will be set to ComboBox Column in which we will populate the Products' Name.

Since we need to populate the ComboBox with Products' Name. Therefore we will add another Typed DataSet and BindingSource to our Form,

We have bind our ProductBindingSource with ProductDataSet which contains Product DataTable. We will fill this table in Form_Load event Handler.

IMPORTANT: This table needs to be filled before we fill the OrderDataSet OR at-least before the OrderDetail DataGridView is displayed

Now we need to setup the Product_ID column. First set the ColumnType of Product_ID column to DataGridViewComboBoxColumn and then set the properties as,

DataPropertyName = Product_ID
DataSource = ProductBindingSource
DisplayMember = Product_Name
ValueMember = Product_ID

DataPropertyName: tells that Product_ID column of OrderDetail DataTable should be updated
DataSource: is the source from where the list of Products will be taken, in this case the ProductBindingSource which is actually bind with Product DataTable
DisplayMember: Column of Product DataTable which should be used to display in DataGridView control
ValueMember: Column of Product DataTable which should be used to set the Value in DataGridView which ultemately will send the value in OrderDetail DataTable

Download:
BindingDataGridViewComboBox.zip

How to handle Overlapping of Large Text Fields in Crystal Reports

2008-09-11T10:36:00.003+06:00

Level: Intermediate

Knowledge Required:
Crystal Reports

Description:
While working in Crystal Reports, we sometimes face a scenario when we need to put a Large Text Field (which can grow upto multiple lines). The issue comes if we try to put another Field after this Text Field. Example:

As you can see in above I have put to Formula Fields, both will contain Large Text which may expand. So here is the preview,

Therefore to solve this issue we can create multiple sub-sections in the same section. This can be achieved by Section Expert.

Now we will put the Fields in different sub-sections as,

And here is the preview again,

Note that in Crystal Reports Sections automatically expand, that is why when the first Text Field expands, the section also expands itself, therefore ultimately the next field renders properly.

Using DataGridView CheckBox Column as RadioButton (OptionButton)

2008-09-04T13:18:00.004+06:00

Level: Beginner

Knowledge Required:

DataGridView Control
Data Binding

Description:
In this post we shall see that how we can transform DataGridView control’s CheckBox column into Radio Button (option button).

I have done 2 main things,

Created a back-end logic when user clicks on CheckBox Column so only one CheckBox should be checked at a time
Change the look of CheckBox column so it looks a Radio Button Column

So to understand the first one, consider a DataGridView control as shown in the above figure. This DataGridView control is actually bind with a DataSet for example,

As you can see there are 2 columns. The IsSelected column is actually a Boolean column which is normally rendered as CheckBox in DataGridView control. What we will be doing is that we first set our DataGridView to Read Only i.e.,

AllowUserToAddRows = False
AllowUserToDeleteRows = False
ReadOnly = True

We are making our DataGridView control Read Only because it is easier to set the CheckBox checked or unchecked programmatically otherwise DataGridView control itself will be interfering and will create problems and complexities for us.

OK now whenever user clicks on CheckBox we will be performing our custom operation. To do this we will use the DataGridView’s CellContentClick event. Here is the code,

Private Sub ShutDownOptionsDataGridView_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles ShutDownOptionsDataGridView.CellContentClick
    If e.ColumnIndex = Me.columnIsSelected.Index Then
        Dim drv As DataRowView
        Dim rowShutDownOption As ShutdownOptionDataSet.ShutDownOptionsRow
        ' in this event handler we know that which DataGridView's row is clicked
        ' so we are going to extract out the actual DataTable's row which is
        ' bind with this DataGridView's Row
        drv = CType(Me.ShutDownOptionsDataGridView.Rows(e.RowIndex).DataBoundItem, DataRowView)
        ' get the DataTable's row
        rowShutDownOption = CType(drv.Row, ShutdownOptionDataSet.ShutDownOptionsRow)

        ' get the row which is currently selected
        Dim rowCurrentlySelected() As ShutdownOptionDataSet.ShutDownOptionsRow
        rowCurrentlySelected = Me.ShutdownOptionDataSet.ShutDownOptions.Select("IsSelected=True")
        ' if some row found then make it de-selected
        If rowCurrentlySelected.Length > 0 Then
            rowCurrentlySelected(0).IsSelected = False
        End If
            ' ok now select the row which is clicked
        rowShutDownOption.IsSelected = True
    End If
End Sub

What we do is first get the row in which IsSelected=True and we make that row IsSelected=False. Then we set the row which is clicked as IsSelected=True.

Next thing is to change the look of CheckBox to OptionButton / RadioButton. For this purpose we will be using DataGridView’s CellPainting event. Here is the code,

Private Sub ShutDownOptionsDataGridView_CellPainting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellPaintingEventArgs) Handles ShutDownOptionsDataGridView.CellPainting
    If e.ColumnIndex = Me.columnIsSelected.Index AndAlso _
        e.RowIndex >= 0 Then
        e.PaintBackground(e.ClipBounds, True)

        Dim rectRadioButton As Rectangle

        rectRadioButton.Width = 14
        rectRadioButton.Height = 14
        rectRadioButton.X = e.CellBounds.X + (e.CellBounds.Width - rectRadioButton.Width) / 2
        rectRadioButton.Y = e.CellBounds.Y + (e.CellBounds.Height - rectRadioButton.Height) / 2

        If IsDBNull(e.Value) OrElse e.Value = False Then
            ControlPaint.DrawRadioButton(e.Graphics, rectRadioButton, ButtonState.Normal)
        Else
            ControlPaint.DrawRadioButton(e.Graphics, rectRadioButton, ButtonState.Checked)
        End If

        e.Paint(e.ClipBounds, DataGridViewPaintParts.Focus)

        e.Handled = True
    End If
End Sub

As you can see we have used the ControlPaint class to draw the RadioButton / OptionButton.

Download:
DGVCheckBoxAsRadioButton.zip

Asynchronous Data Loading using TableAdapter with Cancel Feature

2008-09-03T11:42:00.004+06:00

Level: Intermediate

Knowledge Required:

BackgroundWorker Control
DataGridView Control
Data Binding
TableAdapter

Description:
In my previous post

Implement SQLCommand Cancel with Threading

I have discussed how to implement the SQLCommand Cancel using Threading. In this post we shall see, how we can implement the same feature in a TableAdapter.

As we know that TableAdapter is used as a middle level man standing between Physical Database and Typed DataSet. Which gets the data from Physical Database and loads into Typed DataSet. Sometimes this loading of data takes time (for example due to complex query). Therefore we can provide the user an ability to cancel the on going command by clicking on Cancel Button.

I have created a Test Database (TestDB) and have added a table (tbl_Student). Also I have added a Stored Procedure (GetStudent). Take a look at this Stored Procedure:

CREATE PROCEDURE [dbo].[GetStudent] 
AS
BEGIN
    SET NOCOUNT ON;

    -- here we are simulating a very long query
    -- which takes 3 seconds to complete
    WAITFOR DELAY '00:00:03';

    SELECT *
    FROM tbl_Student;
END

In this procedure I have added a WAITFOR DELAY SQL Statement. Which simulates a long query that takes time 3 seconds. We will call this procedure from our TableAdapter to fill the DataTable.

Now we will add the SQLCommand Cancel stuff in our TableAdapter. This can be achieved by creating a Partial Class of TableAdapter,

Namespace TestDataSetTableAdapters
    Partial Class StudentTableAdapter
        Public Sub CancelSelect()
            Me.Adapter.SelectCommand.Cancel()
        End Sub
    End Class
End Namespace

As you see I have given the name of method CancelSelect which actually cancels the SelectCommand of Adapter. Note that I haven’t implemented any checking here (for example is command executing or NOT). This is upto the user of TableAdapter class. He/She will be responsible to check before executing this method.

Here we have done with our TableAdapter. Now next thing is to implement the loading in the different Thread. For this purpose I have used the BackgroundWorker control which is quite simple. The overall idea is,

Create 2 buttons

LoadButton
CancelButton

When LoadButton is clicked

Execute the BackgroundWorker

In BackgroundWorker DoWork Event Handler

Note somewhere that Execution is started
Execute the Fill() Method of TableAdapter

When CancelButton is clicked

Check if execution is started then

Execute the CancelSelect() method of TableAdapter

Update Sep-4-2008:
Here is the code of Form1

Public Class Form1

    ' ErrorText will contain the error information if occurred
    ' while loading data in different thread
    Private _ErrorText As String
    ' this boolean variable will be true when command is executed
    Private _CommandExecuted As Boolean

    Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click
        ' first we will disable the Load button so it should NOT be clicked again
        Me.btnLoad.Enabled = False
        ' then we will enable the Cancel button so the command can be canceled
        Me.btnCancel.Enabled = True
        ' set the status text
        Call Me.SetStatusText("Loading...")

        ' clear the Error Text
        Me._ErrorText = ""

        ' here we have first clear the currently loaded data
        ' from DataSet, because the Adapter will fill the DataSet
        ' in different thread, therefore it will create problems
        ' while displaying the same data (which is being modified
        ' in different thread) in DataGridView
        Me.TestDataSet.Clear()

        ' start the asynchronous task
        Me.BackgroundWorker1.RunWorkerAsync()
    End Sub

    Private Sub BackgroundWorker1_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
        Try
            ' first set the executed flag
            Me._CommandExecuted = True
            ' execute the command
            Me.StudentTableAdapter.Fill(Me.TestDataSet.Student)
        Catch ex As Exception
            _ErrorText = ex.Message
        End Try
    End Sub

    Private Sub BackgroundWorker1_RunWorkerCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles BackgroundWorker1.RunWorkerCompleted
        ' ok our data is loaded now
        ' so enable the Load button
        Me.btnLoad.Enabled = True
        ' disable the Cancel button
        Me.btnCancel.Enabled = False
        ' if NO error was occurred in thread
        If Me._ErrorText = "" Then
            Me.SetStatusText(Me.TestDataSet.Student.Count & " Records loaded")
        Else ' else (some error was occurred)
            Me.SetStatusText(Me._ErrorText)
        End If
        ' since data is changed in different thread
        ' therefore by reseting the Bindings the DataGridView
        ' will refresh itself and will display all the data
        Me.StudentBindingSource.ResetBindings(False)
    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        ' if command is executed then
        If Me._CommandExecuted Then
            ' cancel it
            Me.StudentTableAdapter.CancelSelect()
        End If
    End Sub


    Private Sub SetStatusText(ByVal sText As String)
        Me.lblStatus.Text = sText
    End Sub
End Class

Download:
TableAdapterWithCancel.zip

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

Please execute this script before running the project.

Query Timeout in ADO.Net but NOT in SQL Server Management Studio

2008-08-12T13:02:00.000+06:00

I was facing this issue for a while that I have created a Stored Procedure in SQL Server 2005 Database that performs a search on a table (according to the given parameters). It was happening that when this procedure was executed from ADO.net (my front-end Visual Basic Application) Timeout exception was occurring. But at the same time when we execute this procedure from SQL Server Management Studio it executes immediately.

The Timeout in Visual Basic Application was NOT coming immediately it was coming after some time when users execute the procedure many times. Whenever time out occurred, I went in the SQL Server Management Studio and just did ALTER (without changing a single thing in procedure). And the problem resolved for some time NOT permanently.

So I put this question on MSDN Forums and a member told me to put the WITH RECOMPILE in the procedure. He told me that

"I think the problem is with the cache. When you ALTER the procedure from Management Studio it is recompiled and the problem is fixed. this is my guess."

It sure fixed my problem and the Timeout never came afterwords. Here is the link to that post.

http://forums.microsoft.com/MSDN/ShowPost.aspx...

Another look at Exception Handling

2008-07-30T12:11:00.006+06:00

Level: Beginner

Knowledge Required:

Exception Handling
ADO.net

Description:
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
    Try
        ' initialize connection
        conn = New SqlClient.SqlConnection("Data Source=.;Initial Catalog=MyDatabase;Integrated Security=True")
        ' open connection
        conn.Open()
        ' 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')"
        cmd.ExecuteNonQuery()

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

        ' ok now commit the changes
        tran.Commit()
    Catch ex As Exception
        ' an exception is occurred
        ' check if transaction has been initialized then
        ' rollback
        If tran IsNot Nothing Then
            tran.Rollback()
        End If
        ' again throw the exception
        ' this is because we want the caller to be notified
        Throw ex
    Finally
        ' 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
            conn.Close()
        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)

2008-07-30T10:58:00.005+06:00

Level: Beginner

Knowledge Required:
Windows Forms

Description:
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

Implement SQLCommand Cancel with Threading

2008-07-29T13:30:00.010+06:00

Level: Advanced

Knowledge Required:

ADO.net
Threading

Description:
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)

    objCon.Open()
    Try
        objCmd.ExecuteReader()
    Catch ex As SqlException
        Debug.Print(ex.Message)
    End Try
    objCon.Close()
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)
t.Start()

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
        objCmd.Cancel()
    End If
End Sub

Draw Focus Rectangle using ControlPaint Class

2008-07-28T23:32:00.003+06:00

Level: Beginner

Knowledge Required:
GDI+

Description:
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

System.Windows.Forms.ControlPaint

E.g.:

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.

Dynamically Loading Data in TreeView control ASP.net

2008-07-26T16:47:00.002+06:00

Level: Beginner

Knowledge Required:

ASP.net
Typed DataSet
TableAdapter
SQL Server 2005

Description:
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 ASP.net 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.

Download:
DynamicTreeview.zip

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.

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

2008-07-19T15:08:00.007+06:00

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

Description:
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)
AS
BEGIN
    -- 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
    Begin  
            If SubString(@StringToTrim, @i, @sl) <> @CharToTrim
            Begin
                SET @Result = SubString(@StringToTrim, @i, @l - @i + 1);
                Break;
            End
        SET @i = @i + @sl;
    End

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

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

Usage:

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

Output:

Replace with Regular Expression

2008-07-17T00:07:00.007+06:00

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

Level: Intermediate

Knowledge Required:
Regular Expressions

Description:
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.

Dim

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,

Me.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,

(\W|^)(Me)(\W|$)

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$2$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 _
                                )
Debug.Print(sHTML)

How to Pass Data Across Forms

2008-07-16T12:08:00.014+06:00

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

Level: Beginner

Knowledge Required:
Win Forms

Description:
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
        Get
            Return Me._DateStart
        End Get
    End Property

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

    ' default constructor
    Public Sub New()
        ' This call is required by the Windows Form Designer.
        InitializeComponent()
        ' 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)
        Me.New()
        ' 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
        Me.Close()
    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
        Me.Close()
    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

Usage:

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