1 2 3 4 5 Previous Next 

DataGrid: Master Detail in one DataGrid (Using Binding to a DataRelation) (SQLClient)


Microsoft has not direct from the box a newer DataGrid that can show relational datatables in one Grid. For that the Windows Forms DataGrid can still be used in Framework 4.

You have than to add it to your toolbox.

This sample uses the NorthWind SQL server sample. After draging a Datagrid on the form , pasting in this code below and changing in the code below the Server name for yours.

Untitled 4
Imports System.Data.SqlClient
Public Class Form1
    'VB10SP1 style so watch the byval  
    Private Sub Form1_Load(sender As System.Object, _
    e As System.EventArgsHandles MyBase.Load
        Dim ds As New DataSet
        Dim conn As New SqlConnection("Server = " & "Your Server" & _
           "; Database = NorthWind; " & _
           "Integrated Security  = sspi;")
        'Replace in Your Server your servername or Ip address
        Dim daEmployees As New SqlDataAdapter("Select * from Employees", conn)
        Dim daOrders As New SqlDataAdapter("Select * from Orders", conn)
        Dim daOrderDetails As New SqlDataAdapter("Select * from [Order Details]", conn)
        daEmployees.Fill(ds, "Employees")
        daOrders.Fill(ds, "Orders")
        daOrderDetails.Fill(ds, "OrderDetails")
        ds.Relations.Add("EmployeeOrder"ds.Tables("Employees").Columns("EmployeeID"), _
            ds.Tables("Orders").Columns("EmployeeID"))
        ds.Relations.Add("Order2Details"ds.Tables("Orders").Columns("OrderID"), _
            ds.Tables("OrderDetails").Columns("OrderID"))
        DataGrid1.ReadOnly = True
        DataGrid1.DataSource = ds
        DataGrid1.Expand(-1)
    End Sub
End Class



DataGrid ColumnStyles: DataGridComboBox(Display Member)


Here is another datagrid combobox example that uses a display member.
The data is stored as a number but a word is displayed.


Be aware this is a Tip for a WindowsForms DataGrid not a DataGridView

   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim dt As New DataTable("Names")
        dt.Columns.Add("Name")
        dt.Columns.Add("State")
        dt.LoadDataRow(New Object() {"Ken Tucker", 4}, True)
        dt.LoadDataRow(New Object() {"Cor Ligthert", 2}, True)
        dt.LoadDataRow(New Object() {"Terry Burns", 3}, True)
        dt.LoadDataRow(New Object() {"Armin Zignler", 1}, True)
        dt.LoadDataRow(New Object() {"Herfried K. Wagner", 0}, True)
        dt.LoadDataRow(New Object() {"Jay B Harlow", 5}, True)

        'above only to build a sample datatable
        dt.DefaultView.AllowNew = False
        DataGrid1.DataSource = dt.DefaultView
        Dim ts As New DataGridTableStyle
        ts.MappingName = "Names"
        Dim textCol As New DataGridTextBoxColumn
        textCol.MappingName = "Name"
        textCol.HeaderText = "Name"
        textCol.Width = 120
        ts.GridColumnStyles.Add(textCol)
        Dim cmbTxtCol As New DataGridComboBoxColumn
        cmbTxtCol.MappingName = "State"
        cmbTxtCol.HeaderText = "States"
        cmbTxtCol.Width = 100
        ts.GridColumnStyles.Add(cmbTxtCol)
        ts.PreferredRowHeight = (cmbTxtCol.ColumnComboBox.Height + 3)
        cmbTxtCol.ColumnComboBox.Items.Add("Austria")
        cmbTxtCol.ColumnComboBox.Items.Add("Germany")
        cmbTxtCol.ColumnComboBox.Items.Add("Netherlands")
        cmbTxtCol.ColumnComboBox.Items.Add("United Kingdom")
        cmbTxtCol.ColumnComboBox.Items.Add("Florida")
        cmbTxtCol.ColumnComboBox.Items.Add("New York")
        cmbTxtCol.ColumnComboBox.DropDownStyle = ComboBoxStyle.DropDownList
        DataGrid1.TableStyles.Add(ts)

    End Sub
End Class

Public Class DataGridComboBoxColumn
    Inherits DataGridTextBoxColumn
    Public WithEvents ColumnComboBox As NoKeyUpCombo 'special class
    Private WithEvents cmSource As CurrencyManager
    Private mRowNum As Integer
    Private isEditing As Boolean

    Dim strValues() As String = {"Austria", "Germany", "Netherlands", "United Kingdom", "Florida", "New York"}
    Shared Sub New()
    End Sub
    Public Sub New()
        MyBase.New()
        ColumnComboBox = New NoKeyUpCombo
        AddHandler ColumnComboBox.SelectionChangeCommitted, _
        New EventHandler(AddressOf ComboStartEditing)
    End Sub
    Protected Overloads Overrides Sub Edit(ByVal source As CurrencyManager, _
    ByVal rowNum As Integer, ByVal bounds As Rectangle, ByVal readOnly1 As Boolean, _
    ByVal instantText As String, ByVal cellIsVisible As Boolean)
        MyBase.Edit(source, rowNum, bounds, readOnly1, instantText, cellIsVisible)
        mRowNum = rowNum
        cmSource = source
        ColumnComboBox.Parent = Me.TextBox.Parent
        ColumnComboBox.Location = Me.TextBox.Location
        ColumnComboBox.Size = New Size(Me.TextBox.Size.Width, ColumnComboBox.Size.Height)
        ColumnComboBox.Text = GetColumnValueAtRow(source, rowNum)
        TextBox.Visible = False
        ColumnComboBox.Visible = True
        ColumnComboBox.BringToFront()
        ColumnComboBox.Focus()
    End Sub
    Protected Overloads Overrides Function Commit(ByVal dataSource As _
    CurrencyManager, ByVal rowNum As Integer) As Boolean
        If isEditing Then
            isEditing = False
            SetColumnValueAtRow(dataSource, rowNum, ColumnComboBox.SelectedIndex)
        End If
        Return True
    End Function
    Private Sub ComboStartEditing(ByVal sender As Object, ByVal e As EventArgs)
        isEditing = True
        MyBase.ColumnStartedEditing(DirectCast(sender, Control))
    End Sub
    Private Sub LeaveComboBox(ByVal sender As Object, ByVal e As EventArgs) _
    Handles ColumnComboBox.Leave
        If isEditing Then
            SetColumnValueAtRow(cmSource, mRowNum, ColumnComboBox.SelectedIndex)
            isEditing = False
            Invalidate()
        End If
        ColumnComboBox.Hide()
    End Sub

    Protected Overrides Function GetColumnValueAtRow(ByVal source As System.Windows.Forms.CurrencyManager, ByVal rowNum As Integer) As Object
        Dim intValue As Integer

        intValue = CInt(MyBase.GetColumnValueAtRow(source, rowNum))
        Return strValues(intValue)
    End Function
End Class

Public Class NoKeyUpCombo
    Inherits ComboBox
    Protected Overrides Sub WndProc(ByRef m As System.Windows.Forms.Message)
        If m.Msg <> &H101 Then
            MyBase.WndProc(m)
        End If
    End Sub
End Class




DataGrid ColumnStyles: DataGridComboBox(Simple)


This is a (changed and optimized) version from the one which we saw in past made by a lot of persons for the Windows Forms DataGrid which has no standard combobox, this one works with Option Strict On.

At the end of the sample is showed how to use it

Be aware this is a Tip for a WindowsForms DataGrid not a DataGridView

Public Class DataGridComboBoxColumn
    Inherits DataGridTextBoxColumn
    Public WithEvents ColumnComboBox As NoKeyUpCombo 'special class
    Private WithEvents cmSource As CurrencyManager
    Private mRowNum As Integer
    Private isEditing As Boolean
    Shared Sub New()
    End Sub
    Public Sub New()
        MyBase.New()
        ColumnComboBox = New NoKeyUpCombo
        AddHandler ColumnComboBox.SelectionChangeCommitted, _
        New EventHandler(AddressOf ComboStartEditing)
    End Sub
    Protected Overloads Overrides Sub Edit(ByVal source As CurrencyManager, _
    ByVal rowNum As Integer, ByVal bounds As Rectangle, ByVal readOnly1 As Boolean, _
    ByVal instantText As String, ByVal cellIsVisible As Boolean)
        MyBase.Edit(source, rowNum, bounds, readOnly1, instantText, cellIsVisible)
        mRowNum = rowNum
        cmSource = source
        ColumnComboBox.Parent = Me.TextBox.Parent
        ColumnComboBox.Location = Me.TextBox.Location
        ColumnComboBox.Size = New Size(Me.TextBox.Size.Width, ColumnComboBox.Size.Height)
        ColumnComboBox.Text = Me.TextBox.Text
        TextBox.Visible = False
        ColumnComboBox.Visible = True
        ColumnComboBox.BringToFront()
        ColumnComboBox.Focus()
    End Sub
    Protected Overloads Overrides Function Commit(ByVal dataSource As _
    CurrencyManager, ByVal rowNum As Integer) As Boolean
        If isEditing Then
            isEditing = False
            SetColumnValueAtRow(dataSource, rowNum, ColumnComboBox.Text)
        End If
        Return True
    End Function
    Private Sub ComboStartEditing(ByVal sender As Object, ByVal e As EventArgs)
        isEditing = True
        MyBase.ColumnStartedEditing(DirectCast(sender, Control))
    End Sub
    Private Sub LeaveComboBox(ByVal sender As Object, ByVal e As EventArgs) _
    Handles ColumnComboBox.Leave
        If isEditing Then
            SetColumnValueAtRow(cmSource, mRowNum, ColumnComboBox.Text)
            isEditing = False
            Invalidate()
        End If
        ColumnComboBox.Hide()
    End Sub
End Class
Public Class NoKeyUpCombo
    Inherits ComboBox
    Protected Overrides Sub WndProc(ByRef m As System.Windows.Forms.Message)
        If m.Msg <> &H101 Then
            MyBase.WndProc(m)
        End If
End Sub
///


This is a sample how to use it
\\\Sample to use the simple datagrid combobox it needs a form with a datagrid.
  Private Sub Form1_Load(ByVal sender As Object, ByVal e _
    As System.EventArgs) Handles MyBase.Load
        Dim dt As New DataTable("Names")
        dt.Columns.Add("Name")
        dt.Columns.Add("State")
        dt.LoadDataRow(New Object() {"Ken Tucker", "Florida"}, True)
        dt.LoadDataRow(New Object() {"Cor Ligthert", "Netherlands"}, True)
        dt.LoadDataRow(New Object() {"Terry Burns", "United Kingdom"}, True)
        dt.LoadDataRow(New Object() {"Armin Zignler", "Germany"}, True)
        dt.LoadDataRow(New Object() {"Herfried K. Wagner", "Austria"}, True)
        dt.LoadDataRow(New Object() {"Jay B Harlow", "New York"}, True)

'above only to build a sample datatable
        dt.DefaultView.AllowNew = False
        DataGrid1.DataSource = dt.DefaultView
        Dim ts As New DataGridTableStyle
        ts.MappingName = "Names"
        Dim textCol As New DataGridTextBoxColumn
        textCol.MappingName = "Name"
        textCol.HeaderText = "Name"
        textCol.Width = 120
        ts.GridColumnStyles.Add(textCol)
        Dim cmbTxtCol As New DataGridComboBoxColumn
        cmbTxtCol.MappingName = "State"
        cmbTxtCol.HeaderText = "States"
        cmbTxtCol.Width = 100
        ts.GridColumnStyles.Add(cmbTxtCol)
        ts.PreferredRowHeight = (cmbTxtCol.ColumnComboBox.Height + 3)
        cmbTxtCol.ColumnComboBox.Items.Add("Austria")
        cmbTxtCol.ColumnComboBox.Items.Add("Germany")
        cmbTxtCol.ColumnComboBox.Items.Add("Netherlands")
        cmbTxtCol.ColumnComboBox.Items.Add("United Kingdom")
        cmbTxtCol.ColumnComboBox.Items.Add("Florida")
        cmbTxtCol.ColumnComboBox.Items.Add("NewYork")
        cmbTxtCol.ColumnComboBox.DropDownStyle = ComboBoxStyle.DropDownList
        DataGrid1.TableStyles.Add(ts)
    End Sub



DataGrid: Validate data entered into textboxcolumn


Be aware this tip is for the Windows Forms DataGrid, not for the DataGridView
To validate the text entered into a datagrid add a handler to the DatagridTextboxColumn's validating event. Here is some sample code.


    Dim ds As New DataSet

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

        strConn = "Server = (local);"
        strConn &= "Database = NorthWind;"
        strConn &= "Integrated Security = SSPI;"


        conn = New SqlConnection(strConn)
        da = New SqlDataAdapter("Select * From Products", conn)
        da.Fill(ds, "Products")


        Dim ts As New DataGridTableStyle
        ts.MappingName = ds.Tables("Products").TableName

        Dim colDiscontinued As New DataGridBoolColumn
        With colDiscontinued
            .MappingName = "Discontinued"
            .HeaderText = "Discontinued"
            .Width = 80
        End With

        Dim colName As New DataGridTextBoxColumn
        With colName
            .MappingName = "ProductName"
            .HeaderText = "Product Name"
            .Width = 180
        End With
        AddHandler colName.TextBox.Validating, AddressOf CellValidating


        ts.GridColumnStyles.Add(colName)
        ts.GridColumnStyles.Add(colDiscontinued)

        DataGrid1.TableStyles.Add(ts)

        ts = Nothing
        colDiscontinued = Nothing
        colName = Nothing


        DataGrid1.DataSource = ds.Tables("Products")
    End Sub

    Private Sub CellValidating(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs)
        Debug.WriteLine(DirectCast(sender, DataGridTextBox).Text)
    End Sub



DataGrid: Alert by a delete of rows using change row


Datagrid Alert by delete of rows

Be aware this tip is for the Windows Forms DataGrid, not for the DataGridView

AddHandler ds.Tables(0).RowDeleted, _
        New DataRowChangeEventHandler(AddressOf Row_Delete)

Private Sub Row_Delete(ByVal sender As Object, ByVal e _
    As DataRowChangeEventArgs)
        If e.Action = DataRowAction.Delete Then
            If MessageBox.Show("Delete row? ", _
            "", MessageBoxButtons.OKCancel) = DialogResult.Cancel Then
                e.Row.RejectChanges()
            End If
       End If
End Sub



1 2 3 4 5 Previous Next