101 Ways to Manipulate the DataGridView Control (Bag 4)

Restricting Inputs
The previous section shows how you can validate the value for a cell after the user has finished typing it. However, in some situations this is not sufficient. A better way would be to prevent illegal characters from being entered in the first place. Using the same example from the previous section, you should prevent users from entering non-numeric characters in the Price field. For normal TextBox controls, this problem could be solved by servicing the KeyPress event. However, applying this technique to the DataGridView control requires some additional work.

First, service the EditingControlShowing event. This event is fired when the user tries to edit the content of a cell:

Private Sub DataGridView1_EditingControlShowing( _
ByVal sender As Object, _
ByVal e As System.Windows.Forms. _
DataGridViewEditingControlShowingEventArgs) _
Handles DataGridView1.EditingControlShowing
'---restrict inputs on the fourth field---
If Me.DataGridView1.CurrentCell.ColumnIndex = 3 And _
Not e.Control Is Nothing Then
Dim tb As TextBox = CType(e.Control, TextBox)
'---add an event handler to the TextBox control---
AddHandler tb.KeyPress, AddressOf TextBox_KeyPress
End If
End Sub

Here, you will add a KeyPress event handler to the TextBox control that you want to restrict. This KeyPress event handler will be invoked when the user types into the cell and it is defined as follows:

Private Sub TextBox_KeyPress( _
ByVal sender As System.Object, _
ByVal e As System.Windows.Forms.KeyPressEventArgs)
'---if textbox is empty and user pressed a decimal char---
If CType(sender, TextBox).Text = String.Empty And _
e.KeyChar = Chr(46) Then
e.Handled = True
Return
End If
'---if textbox already has a decimal point---
If CType(sender, TextBox).Text.Contains(Chr(46)) And _
e.KeyChar = Chr(46) Then
e.Handled = True
Return
End If
'---if the key pressed is not a valid decimal number---
If (Not (Char.IsDigit(e.KeyChar) Or _
Char.IsControl(e.KeyChar) Or _
(e.KeyChar = Chr(46)))) Then
e.Handled = True
End If
End Sub

The above code will restrict the user inputs to numeric digits (including ".")only. No other typed characters will appear in the cell.Deleting Rows
To programmatically delete a row in the DataGridView control, you can use the Remove() method. The following code snippet removes all the selected rows in the DataGridView control:

For Each row As DataGridViewRow In DataGridView1.SelectedRows
DataGridView1.Rows.Remove(row)
Next

The user can also delete rows by first selecting the rows and then pressing the Delete key. By default, the deletion is done automatically without any prompting. But you may want to confirm the deletion with the user before deleting them. You can do so via the UserDeletingRow event:

Private Sub DataGridView1_UserDeletingRow( _
ByVal sender As Object, _
ByVal e As System.Windows.Forms. _
DataGridViewRowCancelEventArgs) _
Handles DataGridView1.UserDeletingRow
If (Not e.Row.IsNewRow) Then
Dim response As DialogResult = _
MessageBox.Show( _
"Are you sure you want to delete this row?", _
"Delete row?", _
MessageBoxButtons.YesNo, _
MessageBoxIcon.Question, _
MessageBoxDefaultButton.Button2)
If (response = DialogResult.No) Then
e.Cancel = True
End If
End If
End Sub

This will prompt the user to confirm that they want to perform a row deletion (see Figure 15).

Figure 15. The UserDeletingRow event prompts the user before actual deletion takes place.

Saving Changes
When you data-bind a DataGridView control to a data source (from a database), all the changes made to the DataGridView control are not automatically updated on the database. For this, you need to manually push all the changes back to the database.Using the earlier example of binding the DataGridView control to a dataset, the following code loads a dataset with the Customers table:

Dim connStr As String = _
"Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;" & _
"Integrated Security=True"
Dim sql As String = "SELECT * FROM Customers"
Dim conn As SqlConnection = New SqlConnection(connStr)
Dim comm As SqlCommand = New SqlCommand(Sql, conn)
Dim dataadapter As SqlDataAdapter = New SqlDataAdapter(comm)
Dim ds As DataSet = New DataSet()
'---open the connection and fill the dataset---
conn.Open()
dataadapter.Fill(ds, "Customers_table")
conn.Close()
DataGridView1.DataSource = ds
DataGridView1.DataMember = "Customers_table"

To save the changes made on the DataGridView control back to the database, use the following code:

Dim sqlCmdBuilder As New SqlCommandBuilder(dataadapter)
sqlCmdBuilder.GetUpdateCommand()
dataadapter.Update(ds.Tables("Customers_table"))

The SqlCommandBuilder object will automatically formulate the SQL statement (through the GetUpdateCommand() method) to reflect the changes made to the DataGridView control and then let the SqlDataAdapter object update the table in the database.If you are binding the DataGridView control to a typed dataset, like this:

Dim adapter As New CustomersTableAdapter
Dim bindingSrc As New BindingSource
bindingSrc.DataSource = adapter.GetData
DataGridView1.DataSource = bindingSrc

Then, you can simply update the changes to the database using the following code:

bindingSrc.EndEdit()
adapter.Update(bindingSrc.DataSource)

Lastly, if you are saving the changes somewhere else (such as a string), you can simply loop through all the rows and columns in the DataGridView control:

Dim output As String = String.Empty
For Each row As DataGridViewRow In DataGridView1.Rows
For Each cell As DataGridViewCell In row.Cells
output += cell.Value & ":"
Next
output += vbCrLf
Next
MsgBox(output)

In this article, you have seen how to perform common tasks associated with the DataGridView control. As you can see, the DataGridView is very versatile. Hopefully this will serve as a helpful reference when you start using the DataGridView to display data from databases or any other data source
.