Binding DataGridView ComboBox Column

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

Another look at Exception Handling

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)

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

Draw Focus Rectangle using ControlPaint Class

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

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.

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

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

Bound TextBox Control Validation Issue

Level: Beginner

Knowledge Required:
Data Binding

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

How to Check Mouse Buttons State

Level: Beginner

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

System.Windows.Forms.Form.MouseButtons

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
    Else
        ' Selection is NOT Changed by mouse
    End If
End Sub

Using DateTimePicker Control to Take Year Input

Level: Beginner

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

Performing Aggregate Functions (MAX, MIN, SUM, COUNT) on DataTable

In this article we will discuss:

• How to get Maximum (MAX) or Minimum (MIN) value from DataTable
• How to SUM all the values of a column in DataTable
• How to Count all the values of a column in DataTable

Level: Beginner

Knowledge Required:
DataTable

Description:
We have used DataTable Class of .net framework lots of time to load the data from Physical Database. DataTable NOT only stores the Data but can also perform other things like performing calculation on a column, e.g. getting Max./Min. item, counting, etc. So in this article we will discuss how can we perform this type of calculation on DataTable.

DataTable has a public method called Compute. We can use the aggregate functions in this method as,

Dim iMaxRow_ID As Integer
iMaxRow_ID = StudentTable.Compute("MAX(Row_ID)", "")

The above example will get the Maximum Row_ID (Row_ID is a column in StudentTable) from StudentTable.

Dim iTotalOrders As Integer
iTotalOrders = OrderTable.Compute("Count(Order_ID)", "Create_Date > #15-Jan-2008#")

Above example will count number of Orders that were created after 15-Jan-2008

For detailed information:

MSDN DataTable.Compute Method

DataGridView control FAQs and Quick Tips

In this post we will discuss some FAQs regarding DataGridView control. I will be (inshaALLAH) updating this post, when I find new FAQ, if you have any question(s) you can contact me on my email address displayed on right side.

Level: Beginner

Introduction:

Q: What is DataGridView control?
Ans: DataGridView control is used to display the data from Data Source (e.g. DataTable) in a Tabular Form i.e. in Columns and Rows like Spreadsheet.

Q: How can I use DataGridView control?
Ans: DataGridView control can be used either by attaching it to some Data Source or without attaching it with any Data Source. You can simply drag and drop the control on form, add columns using Columns Properties or by using Smart Tags, run the program and start using the control just like you use the Excel.

Data Binding:

Q: How can I attach the DataGridView to some Data Source?
Ans: Attaching of DataGridView to some Data Source is called Data Binding. You can bind the DataGridView with DataTable directly or can use a BindingSource.

Q: How to Bind DataGridView to a DataTable Programmatically?
Ans: Use the following code

DataGridView1.DataSource = MyDataTable

Q: How to Bind DataGridView to a DataTable of a DataSet Programmatically?
Ans: Use the following code

DataGridView1.DataSource = MyDataSet
DataGridView1.DataMember = "MyTable"

Q: How to Bind DataGridView to a BindingSource Programmatically?
Ans: Use the following code

DataGridView1.DataSource = MyBindingSource

Q: When I Bind my DataGridView programmatically it automatically generates the Columns, how can I prevent it?
Ans: Set the AutoGenerateColumns Property = False before setting the DataSource

DataGridView1.AutoGenerateColumns = False
DataGridView1.DataSource = Me.BindingSource1

Q: I have added some columns in my DataGridView control and haven't bind it to anything, how can I add rows in it?
Ans: Use the following code

DataGridView1.Rows.Add(5) ' This will ad blank 5 Rows
OR
DataGridView1.Rows.Add("Item in 1st Column", "Item in 2nd Column")

Q: How to make DataGridView Read-only
Ans: Set the following properties,

AllowUserToAddRows = False
AllowUserToDeleteRows = False
ReadOnly = True

Selection:

Q: How can I select all the Columns and Rows (cells) of DataGridView control Programmatically?
Ans: Use the following code

DataGridView1.SelectAll()

Q: I want to change the selection style of DataGridView from single Cell to Full Row
Ans: Use the following code

DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect

Q: How can I copy the Selected Cells onto Clipboard Programmatically?
Ans: Use the following code

My.Computer.Clipboard.SetDataObject(DataGridView1.GetClipboardContent())

Q: How can I Paste the data from Clipboard to DataGridView control?
Ans: See Paste Text in DataGridView Control (Bound with BindingSource)

Q: How can I prevent user to select multiple Cells/Rows? How can I restrict user to select only one cell/row at a time?
Ans: Set the property

DataGridView1.MultiSelect = False

Q: How can I get the current row of DataGridView which is selected?
Ans: Use DataGridView.CurrentRow property. This will be Nothing if no row exists in DataGridView.

Q: How can I get all currently Selected Rows of DataGridView control?
Ans: Use DataGridView.SelectedRows property.

Q: How can I get all currently selected cells of DataGridView control?
Ans: User DataGridView.SelectedCells property.

Q: How can I select DataGridView row programmatically?
Ans: Use the following code:

DataGridView1.Rows(0).Selected = True

Q: How can I change the current row of DataGridView control?
Q: DataGridView control's CurrentRow property is read-only, how can I change the Current Row?
Q: I want to change the focus (dotted border) to some other cell in DataGridView control, programmatically.
Ans: Use CurrentCell property,

DataGridView1.CurrentCell = DataGridView1.Rows(0).Cells(0)

Layout:

Q: I there any way to make all the columns fit in the DataGridView control?
Ans: 2 Ways

i) Set the DataGridView's AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill

OR

ii) Set at least one column's AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill

Formatting:

Q: How can I change the Format of Numeric and Date Values in DataGridView?
Ans: Set the DefaultCellStyle.Format property

E.g.
Column1.DefaultCellStyle.Format = "dd-MMM-yyyy" ' display as 01-Jan-2008
Column2.DefaultCellStyle.Format = "N0" ' only numeric NO decimals

Definitions:

Q: What is Unbound Column in DataGridView control?
Ans: Unbound column is a column added in DataGridView control but NOT bound to any thing i.e. the Column's DataProperptyName Property has NOT been assigned any value.

Column1.DataPropertyName = ""

Q: What is Virtual Mode of DataGridView control?
Ans: When VirtualMode property of DataGridView Control is set to True, then the DataGridView Control is said to be in Virtual Mode. In this mode CellValueNeeded event triggers for Unbound Columns. In the event Handler of this event we provide some value (using e.Value) which then renders that value in cell.

Events:

Q: CellValueNeeded event is NOT being fired/triggered/executed!
Q: CellValueNeeded event is NOT working!
Ans: VirtualMode property of DataGridView control must be True, also CellValueNeeded is only triggered for Unbound Columns. See previous question.

Q: What event occurs when user Double clicks on a Cell in DataGridView control?
Ans: CellDoubleClick event

Q: What is the difference between CellContentClick and CellClick events in DataGridView control?
Ans:

CellContentClick is triggered for
*) DataGridViewLinkColumn
*) DataGridViewButtonColumn
*) DataGridViewCheckBoxColumn
when user click on link, button or checkbox in cell

CellClick event occurs when user clicks on Cell (NOT its contents i.e. link, button or checkbox)

Misc.:

Q: I have a Editable DataGridView control. I want to detect whether current row is the New Row (new row = last row in DataGridView which has a "*" sign in its row header)?
Ans: Use the IsNewRow property of DataGridViewRow

DataGridView1.Rows(0).IsNewRow

Q: When user presses TAB key in DataGridView Control, current cell changes to next cell, I want to suppress this behavior and want to change the Focus to next control, how can I do this?
Ans: Set the property of DataGridView

StandardTab = True

Q: How can I change the default Editing behavior of DataGridView Control?
Ans: Use the EditMode property, values can be:

EditOnEnter = Edit begins immediately when cell gets focus, no need to press F2 or any key
EditOnKeyStroke = Edit begins on Key stroke, NOT on F2
EditOnKeystrokeOrF2 = Edit begins on both F2 or Key stroke
EditOnF2 = Edit begins only on F2
EditProgrammatically = Edit programmatically i.e. you have to call BeginEdit() method

Q: I have bind my DataGridView to the DataTable, one column of this Table does NOT allow NULL, when user tries to enter a NULL value in this column through DataGridView control, an exception occurs but DataGridView automatically handles it and displays a big Dialog Box. How can I suppress it?
Ans: Use the DataError event of DataGridView control. In the handler of this event you can use e.Exception to see which exception is occurred, and can show your own Dialog Box.

Q: I started adding first row in DataGridView control, and then I pressed ESC (to cancel) an exception occurred which unfortunately cannot be handled, why?
Ans: This is a bug confirmed by Microsoft

See the following KB Article

FIX: Error message when you try to press ESC to cancel adding a new row to a DataGridView control in the .NET Framework 2.0: "An unhandled exception of type 'System.InvalidOperationException' occurred in System.Windows.Forms.dll"

How to check Column is Primary Key Column in DataTable

This article explains how to check a Column of DataTable whether it is a Primary Key column or NOT in a Un-Typed DataSet or Typed DataSet. Also we will discuss how to get the Primary Key columns of a DataTable.

Level: Beginner

Knowledge Required:

• Typed DataSet / Un-Typed DataSet
• DataTable
• Primary Key

Description:
To check a column whether it is a Primary Key or NOT use the following function:

Private Function IsPrimaryKeyColumn(ByRef Table As DataTable, ByRef Column As DataColumn) As Boolean
    Return Array.IndexOf(Table.PrimaryKey, Column) >= 0
End Function

DataTable exposes a property PrimaryKey() which is actually array of DataColumn, since a Primary Key can also be a composite key (key composed with more than one columns) that is why it is an Array. So we just search the given column in that array, if found then it means this column is a Primary Key.

Usage Typed DataSet:

Dim b As Boolean
b = IsPrimaryKeyColumn(OrderDataSet.Order, OrderDataSet.Order.Order_IDColumn)

Usage Un-Typed DataSet:

Dim b As Boolean
b = IsPrimaryKeyColumn(DS.Tables("Order"), DS.Tables("Order").Columns("Order_IDColumn"))

Working with Unicode

Level: Beginner

Knowledge Required:

• String

Description:
Recently I have developed a Small Project Urdu Text Editor, in which I have used the Unicodes to display Urdu in Rich Text Box Control.

Thanks to Unicode, it has made our life so easy. In Urdu we have several forms of a single character unlike English in which we only have 1 form for each character. Normally there are 4 forms for each character in Urdu, e.g. Bay

1) Isolated Form of Bay

2) Initial Form of Bay

3) Middle Form of Bay

4) Final Form of Bay


If we do this manually then we have to do lots of coding, but as I have told that Unicode has made things much easier. Therefore we simply have to put the isolated form in the String and rest of the forms will be automatically handled by the Operating System.

Dim sBay As String
Dim sAlif As String
Dim sBaa As String
' get bay
sBay = System.Text.Encoding.Unicode.GetString(New Byte() {&H28, &H6})
' get alif
sAlif = System.Text.Encoding.Unicode.GetString(New Byte() {&H27, &H6})
' combining bay and alif => baa
sBaa = sBay & sAlif
Debug.Print(sBaa)

See above code in action:
Urdu Text Editor

How to check a Bit whether it is 1 or 0 (zero) in an Integer (Bitwise Operations)

Level: Beginner

Knowledge Required:

• Bitwise Operators
• Enum

Description:
We usually get statuses from different resources, which contains one or more statuses together. So we require to check whether a particular Status is set or NOT.

Normally statuses are set using Enum

Example:

Public Enum EnumFontStyle
    Regular = 0
    Bold = 1
    Italic = 2
    Underline = 4
    StrikeThrough = 8
End Enum

Public Sub Test()
    Dim fs As EnumFontStyle
    fs = EnumFontStyle.Bold OR EnumFontStyle.Italic
    If fs And EnumFontStyle.Bold Then
        MsgBox("Font is Bold")
    Else
        MsgBox("Font is NOT Bold")
    End If
End Sub

To understand how it is working,

Suppose we have an integer = 3 => binary = 11, if we perform,

3 AND 2

This is in binary

11 AND 10

       11
AND    10
----------
       10 = 2

Hence bit number 2 is 1, because if we do,

4 AND 2 (binary = 100 AND 10)

       100
AND    010
----------
       000 = 0

This means bit number 2 = 0

See Also:
How to add/remove Attribute from File (Bitwise Operation)
How to Make a Bit Zero in an Integer (Bitwise Operation)

How to add/remove Attribute from File (Bitwise Operation)

This article explains how to add or remove Attributes (Read-only, Hidden, etc.) from a file using Bitwise Operators.

Level: Beginner

Knowledge Required:
Bitwise Operations

Description:
In my previous post:

How to Make a Bit Zero in an Integer (Bitwise Operation)

I explained a technique through which we can make a bit Zero in an Integer. In which we used the XOR bitwise operator. XOR actually reverts the bit i.e. if 0 then makes it 1 and if 1 then makes it 0. Therefore we can use XOR to make the bit 0 (Zero) if we are sure that bit is NOT already zero, otherwise we should use the technique described in this post.

For example, we are going to remove the Read-only Attribute from a file.

Remove Read-only Attribute from File:

Dim f As System.IO.FileInfo
f = New System.IO.FileInfo("C:\ABC.txt")
f.Attributes = f.Attributes And (Not IO.FileAttributes.ReadOnly)

Therefore to make the bit zero from an integer we use code

MainIntVar = MainIntVar AND (NOT IntBitToRemove)

Above code is useful to remove the Attribute (Read-only, Hidden, etc.) from a file. Similarly we can add attributes to file,

Add Read-only Attribute to File:

Dim f As System.IO.FileInfo
f = New System.IO.FileInfo("C:\ABC.txt")
f.Attributes = f.Attributes Or  IO.FileAttributes.ReadOnly

See Also:
How to check a Bit whether it is 1 or 0 (zero) in an Integer (Bitwise Operations)
How to Make a Bit Zero in an Integer (Bitwise Operation)

How to add Serial Number Column in DataGridView

This article explains how to add a Serial Number (Row Index) Column in DataGridView control without adding this column in Physical Database or in DataSet's DataTable.



Level: Beginner

Knowledge Required:

• DataGridView
• Data Binding

Description:
We use DataGridView control alot of times in Data Manipulation application. Sometimes we require to have a Serial Number (S.No.) Column in DataGridView Control in such a way that no matter how Sorting/Filtering is done, Serial Number should remain constant i.e. in a Sequence.

One way to accomplish this is to create a Column in DataSet's DataTable in which we can store the Serial Numbers, but this will make our job too complex if sorting/filtering is also done. Because we have to re-check the Serial Number column again and again each time the Sorting/Filtering is performed.

So the better way is to use the DataGridView control's Virtual Mode. Here are the steps that we will do,

1) Bind the DataGridView control to some BindingSource and setup its Columns
2) Add another column (Unbound Column) and make it the first column
3) Set its name = ColumnSNo
4) Set its ReadOnly = True
4) Set the DataGridView control's VirtualMode property to True
5) In CellValueNeeded event use the following code:

Private Sub DataGridView1_CellValueNeeded(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellValueEventArgs) Handles DataGridView1.CellValueNeeded
    If e.RowIndex >= 0 AndAlso e.ColumnIndex = Me.ColumnSNo.Index Then
        e.Value = e.RowIndex + 1
    End If
End Sub

Note that if we don't set the VirtualMode Property to True then CellValueNeeded event wouldn't fire

Summary:
To display the Serial Numbers we have added an unbound column and set the Virtual Mode Property of DataGridView control to True. Then in CellValueNeeded Event we just return the Row Index whose value is required

How to Convert String into Byte Array

Level: Beginner
Knowledge Required:

• Text Encoding
• String
• Array

Description:
As I have discussed in my earlier post

How to Convert Byte Array into String

Now we will see how we can reverse this process i.e. Convert the String into Byte Array. We will use Text Encoding as,

byte array() = System.Text.Encoding.ASCII.GetBytes(string variable)

Usage:

Dim StringVariable As String = "ABC"
Dim ByteArray() As Byte
ByteArray = System.Text.Encoding.ASCII.GetBytes(StringVariable)
For Each b As Byte In ByteArray
    Debug.WriteLine(b)
Next

Output will be:

65
66
67

See Also:

How to Convert Byte Array into String

How to Convert Byte Array into String

Level: Beginner

Knowlege Required:

• Text Encoding
• String
• Array

Description:
Sometimes it is required to convert the byte Array into String. For example if we have read the bytes from some Stream (like reading a file or getting data from port) then we may need to convert that Byte Array into String so we can display it somewhere.

For this purpose we use Text Encoding:

StringVariable = System.Text.Encoding.ASCII.GetString(byte array)

Usage:

Dim b(5) As Byte

b(0) = 65
b(1) = 66
b(2) = 67
b(3) = 68
b(4) = 69

Debug.Print(System.Text.Encoding.ASCII.GetString(b))

Output will be:

ABCDE

See Also:

How to Convert String into Byte Array

Comparing DateTime Column in SQL Server

Introduction: This article addresses the following issues:

1) Time also stores in DateTime column therefore Time is also required while comparing
2) How to extract only Date (excluding the Time) from DateTime column

Level: Beginner

Knowledge Required:

• T-SQL
• SQL Server 2005

Description:
While working with Database we usually face an issue where we need to compare the given Date with SQL Server DateTime column. For example:

SELECT * FROM Orders WHERE Order_Date = '5-May-2008'

As we can see in above example we are comparing our date i.e. '5-May-2008' with the DateTime column Order_Date. But it is NOT sure that all the Rows of 5-May-2008 will be returned. This is because Time also stores here. So if we look into the Table we will find that,

Order_Date = '5-May-2008 10:30'
Order_Date = '5-May-2008 11:00'
Order_Date = '5-May-2008 14:00'

So when we give '5-May-2008' to SQL Server then it automatically converts it into:

Order_Date = '5-May-2008 12:00'

Therefore this date will NOT be equal to any of the dates above.

There are several techniques to handle this issue, I will discuss some here:

1) Compare all the 3 parts (day, month, year)
2) Convert both (the given Date and the DateTime Column) into some predefined format using CONVERT function
3) Use Date Range

1) Compare all the 3 parts:
Example:

DECLARE @Given_Date DateTime;

SET @Given_Date = '5-May-2008 12:00';

SELECT *
FROM Orders 
WHERE   Day(Order_Date) = Day(@Given_Date) AND
        Month(Order_Date) = Month(@Given_Date) AND
        Year(Order_Date) = Year(@Given_Date);

2) Convert both (the given Date and the DateTime Column) into some predefined format using CONVERT function:
Example:

DECLARE @Given_Date DateTime;

SET @Given_Date = '5-May-2008 12:00';

SELECT *
FROM Orders 
WHERE   CONVERT(varchar(100), Order_Date, 112) = CONVERT(varchar(100), @Given_Date, 112);

Note that CONVERT function will work as:

Print CONVERT(varchar(100), CAST('5-May-2008' AS DateTime), 112);

Output:

20080505

But we have limitation i.e. cannot use '<' and '>' operators here.

3) Use Date Range
Example:

DECLARE @Given_Date DateTime;
DECLARE @Start_Date DateTime;
DECLARE @End_Date DateTime;

SET @Given_Date = '5-May-2008 12:00';
SET @Start_Date = CAST(CAST(Day(@Given_Date) As varchar(100)) + '-' + DateName(mm, @Given_Date) + '-' + CAST(Year(@Given_Date) As varchar(100)) As DateTime);
SET @End_Date = CAST(CAST(Day(@Given_Date) As varchar(100)) + '-' + DateName(mm, @Given_Date) + '-' + CAST(Year(@Given_Date) As varchar(100)) + ' 23:59:59' As DateTime);

SELECT *
FROM Order
WHERE Order_Date Between @Start_Date AND @End_Date;

In my opinion the last example is the fastest, since in all the previous examples SQL Server has to perform some extraction/conversion each time while extracting the Rows. But in the last method SQL Server will convert the Given Date only once, and in SELECT statement each time it is comparing the Dates, which is obviously faster than comparing a Date after converting it into VarChar or comparing each part of Date. Also in previous 2 methods we have limitation i.e. we cannot use the Range.

Creating Generic Range Class

Level: Beginner

Knowledge Required:
Generics in Visual Basic

Description:
In this article we will create a Generic Range Class, which can be further used in different types of Applications like Database Applications.

Generic Range Class:

Public Class GenericRange(Of T)
    Private _Start As T
    Private _End As T
    Private _HasStart As Boolean
    Private _HasEnd As Boolean

    Public Sub New()
        Call Me.SetStartNull()
        Call Me.SetEndNull()
    End Sub

    Public Sub New(ByVal Start As T, ByVal [End] As T)
        Me.Start = Start
        Me.End = [End]
    End Sub

    Public Property Start() As T
        Get
            If Me.HasStart Then
                Return Me._Start
            Else
                Throw New Exception("Start Value is NOT Set")
            End If
        End Get
        Set(ByVal value As T)
            If value Is Nothing Then
                Me.SetStartNull()
            Else
                Me._Start = value
                Me._HasStart = True
            End If
        End Set
    End Property

    Public Property [End]() As T
        Get
            If Me.HasEnd Then
                Return Me._End
            Else
                Throw New Exception("End Value is NOT Set")
            End If
        End Get
        Set(ByVal value As T)
            If value Is Nothing Then
                Me.SetEndNull()
            Else
                Me._End = value
                Me._HasEnd = True
            End If
        End Set
    End Property

    Public ReadOnly Property HasStart() As Boolean
        Get
            Return Me._HasStart
        End Get
    End Property

    Public ReadOnly Property HasEnd() As Boolean
        Get
            Return Me._HasEnd
        End Get
    End Property

    Public Sub SetStartNull()
        Me._HasStart = False
    End Sub

    Public Sub SetEndNull()
        Me._HasEnd = False
    End Sub
End Class

Usage:

Private Function GetOrderQueryByDate(ByRef GR As GenericRange(Of Date)) As String
    Dim sQuery As String
    Dim sWhere As String = ""

    sQuery = "SELECT * FROM Orders"

    If GR.HasStart Then
        sWhere = "OrderDate >= '" & GR.Start & "'"
    End If
    If GR.HasEnd Then
        If sWhere <> "" Then sWhere &= " AND "
        sWhere &= "OrderDate <= '" & GR.End & "'"
    End If
    If sWhere <> "" Then
        sQuery &= " WHERE " & sWhere
    End If
    Return sQuery
End Function