1 2 3 4 5 Previous Next 

DataGridView: Currency symbols in a DatagridView ( as well relations )


This sample is made in true North-Atlantic cooperation.
It shows how to get a currency-sign based on a country in a DataGridView.
It needs the SQL Northwind sample database and a VB 2005 project with a DataGridView on a Form.
To test it, you need only to click Debug. Imports System.Data.SqlClient
Public Class Form1
    Private Sub Form1_Load(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles MyBase.Load
        Dim ds As New DataSet
        Dim conn As New SqlConnection _
        ("Server = .\SQLExpress;Database = NorthWind; Integrated Security = SSPI;")
        Dim da As New SqlDataAdapter("Select * from [Order Details]", conn)
        da.Fill(ds, "Orderdetails")
        da = New SqlDataAdapter("Select OrderId, ShipCountry from Orders", conn)
        da.Fill(ds, "Orders")
        ds.Relations.Add("TheRelation", ds.Tables("Orders").Columns("OrderId"), _
                        ds.Tables("Orderdetails").Columns("OrderId"))
        ds.Tables("Orderdetails").Columns.Add("ShipCountry", _
                 GetType(String), "Parent.ShipCountry")
        ds.Tables("Orderdetails").DefaultView.RowFilter = _
        "ShipCountry = 'USA' OR ShipCountry = 'UK' OR ShipCountry = 'Belgium' OR " & _
        "ShipCountry = 'Poland' Or ShipCountry = 'Ireland'"
        DataGridView1.DataSource = ds.Tables("Orderdetails").DefaultView
        DataGridView1.Columns.Remove("ProductID")
    End Sub

    Private Sub DataGridView1_CellFormatting(ByVal sender As Object, _
    ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) _
    Handles DataGridView1.CellFormatting
        Dim dv As DataView = DirectCast(DataGridView1.DataSource, DataView)
        Dim ci As System.Globalization.CultureInfo
        If e.ColumnIndex = 1 Then
            Select Case dv(e.RowIndex)("ShipCountry").ToString
                Case "USA"
                    ci = New System.Globalization.CultureInfo("en-US")
                Case "UK"
                    ci = New System.Globalization.CultureInfo("en-GB")
                Case "Poland"
                    ci = New System.Globalization.CultureInfo("pl-PL")
                Case "Ireland"
                    ci = New System.Globalization.CultureInfo("en-IE")
                Case Else
                    ci = New System.Globalization.CultureInfo("nl-NL")
            End Select
            Dim cValue As Decimal = Convert.ToDecimal(e.Value)
            e.Value = cValue.ToString("c", ci)
        End If
    End Sub
End Class



DataGridView: Custom Sort


Someone asked me about doing a custom sort on a datagridview during my datagridview session at code camp. Unfortunately this will not work if the datagridview is bound to a datasource. This example adds numbers as string data to a datagridview. When you sort this column the values will not be in numeric order. This example converts the string to a number and sorts based on the number. Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, _
               ByVal e As System.EventArgs) Handles MyBase.Load
        DataGridView1.ColumnCount = 1
        DataGridView1.Columns(0).HeaderText = "String"

        For x As Integer = 0 To 100
            DataGridView1.Rows.Add(New String() {x.ToString})
        Next
    End Sub

    Private Sub DataGridView1_SortCompare(ByVal sender As Object, _
                ByVal e As System.Windows.Forms.DataGridViewSortCompareEventArgs) _
                 Handles DataGridView1.SortCompare
        Dim intValue1, intValue2 As Integer
        If Not Integer.TryParse(e.CellValue1.ToString, intValue1) Then Return
        If Not Integer.TryParse(e.CellValue2.ToString, intValue2) Then Return
        If intValue1 = intValue2 Then
            e.SortResult = 0
        ElseIf intValue2 > intValue1 Then
            e.SortResult = -1
        Else
            e.SortResult = 1
        End If
        e.Handled = True
    End Sub
End Class


DataGridView: Binding Navigator Confim Delete


In the designer set the bindingnavigators deleteitem to none. The delete button should still be there. Add the following code to the Bindingnavigators delete item click event.

Private Sub BindingNavigatorDeleteItem_Click(ByVal sender As System.Object,  _
    ByVal e As System.EventArgs) Handles BindingNavigatorDeleteItem.Click
        If MessageBox.Show _ 
            ("Are you sure?", "", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.Yes Then
                    ContactBindingSource.RemoveCurrent()
        End If
End Sub



DataGridView: Implement Single click navigation to a DataGridView Combo Box Cell


When you navigate to a DataGridView combobox column you need to click on the cell twice to get the drop down to show.  Valentine Khorzhevsky came up with an way to get the drop down to show on the first click using SendKeys.  Matthew Hicks added a second code sample which works if you have a DataGridViewCheckBoxColumn in the DataGridView.  The DataGridViewCheckBoxColumn does not have an edit type.

    Private Sub DataGridView1_CellEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellEnter
        Dim dgv As DataGridView = CType(sender, DataGridView)

        If dgv(e.ColumnIndex, e.RowIndex).EditType.ToString() = "System.Windows.Forms.DataGridViewComboBoxEditingControl" Then
            SendKeys.Send("{F4}")
        End If
    End Sub


 

    Private Sub dgvReceiptItems_CellEnter(ByVal sender As Object, _

        ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) _

        Handles dgvReceiptItems.CellEnter

        Dim dgv As DataGridView = CType(sender, DataGridView)

 

        If dgv(e.ColumnIndex, e.RowIndex).EditType IsNot Nothing Then

            If dgv(e.ColumnIndex, e.RowIndex).EditType.ToString() = "System.Windows.Forms.DataGridViewComboBoxEditingControl" Then

                SendKeys.Send("{F4}")

            End If

        End If

    End Sub

 



DataGridView: Filter Combobox Column Items


This example shows how to filter the items in a comboboxcolumn in a datagrid.  It is based on code found in the DataGridView Faq. Basically the code filters the items in the combobox in the cell begin edit event and removes the filter in the Cell end edit event.

Imports System.Data.SqlClient

Public Class Form1
    Dim dv As DataView
    Dim ds As New DataSet

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim dt As New DataTable
        Dim strConn As String
        Dim da, daProducts As SqlDataAdapter
        Dim conn As SqlConnection

        strConn = "Server = .;Database = NorthWind; Integrated Security = SSPI;"
        conn = New SqlConnection(strConn)

        da = New SqlDataAdapter("Select * from Categories", conn)
        daProducts = New SqlDataAdapter("Select * from Products", conn)

        da.Fill(ds, "Categories")
        daProducts.Fill(ds, "Products")
        dt.Columns.Add("Category", GetType(Integer))
        dt.Columns.Add("Product")
        DataGridView1.AutoGenerateColumns = False
        DataGridView1.DataSource = dt

        Dim dgvCombo As New DataGridViewComboBoxColumn
        With dgvCombo
            .Width = 150
            .DataSource = ds.Tables("Categories")
            .DisplayMember = "CategoryName"
            .DataPropertyName = "CategoryID"
            .ValueMember = "CategoryID"
            .HeaderText = "Category"
        End With

        dv = New DataView(ds.Tables("Products"))
        DataGridView1.Columns.Add(dgvCombo)
        Dim dgvFilter As New DataGridViewComboBoxColumn
        With dgvFilter
            .Width = 150
            .DisplayMember = "ProductName"
            .DataPropertyName = "ProductName"
            .HeaderText = "Product"
        End With

        DataGridView1.Columns.Add(dgvFilter)
    End Sub

    Private Sub DataGridView1_CellBeginEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellCancelEventArgs) Handles DataGridView1.CellBeginEdit
        If e.ColumnIndex = 1 Then
            Dim dgvCbo As DataGridViewComboBoxCell = TryCast(DataGridView1(e.ColumnIndex, e.RowIndex), DataGridViewComboBoxCell)
            If dgvCbo IsNot Nothing Then
                Dim str As String = DataGridView1.Item(0, DataGridView1.CurrentRow.Index).Value.ToString
                dv = New DataView(ds.Tables("Products"))
                dv.RowFilter = "CategoryID = " & str
                dgvCbo.DataSource = dv
            End If
        End If
    End Sub

    Private Sub DataGridView1_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
        If e.ColumnIndex = 2 Then
            Dim dgvCbo As DataGridViewComboBoxCell = TryCast(DataGridView1(e.ColumnIndex, e.RowIndex), DataGridViewComboBoxCell)
            If dgvCbo IsNot Nothing Then
                dv = New DataView(ds.Tables("Products"))
                dgvCbo.DataSource = dv
            End If
        End If
    End Sub

    Private Sub DataGridView1_DataError(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewDataErrorEventArgs) Handles DataGridView1.DataError
        e.Cancel = True
    End Sub

    Private Sub DataGridView1_DefaultValuesNeeded(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowEventArgs) Handles DataGridView1.DefaultValuesNeeded
        e.Row.Cells(0).Value = 1
    End Sub


End Class



1 2 3 4 5 Previous Next