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



DataGridView Caputere the characters from a cell input.


IronRazerz from the forums has shown this code. If the cells in your 6th column are TextBox types then you can do it like this using a DataGridViewTextBoxEditingControl. You will notice if you look at the DataGridViewTextBoxEditingControl events that it has a KeyDown event too if you need that instead.
Public Class Form1
    Private WithEvents EditControl As DataGridViewTextBoxEditingControl
    'this event is raised when a cell enters the editing mode
    Private Sub DGV_EditingControlShowing(ByVal sender As ObjectByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgsHandles DataGridView1.EditingControlShowing
        'here you are just checking if the Edit Control is Nothing and that the current cell is in the 6th column
        If EditControl Is Nothing AndAlso DataGridView1.CurrentCell.ColumnIndex = 5 Then
            'this casts the EditControl to the current cell`s edit control. This way you can access its events (TextChanged, KeyDown, or whatever)
            EditControl = DirectCast(e.Control, DataGridViewTextBoxEditingControl)
        End If
    End Sub
    'this is the EditControl`s Leave event where you will set it to Nothing. Otherwise you will catch keys even if you edit another cell.
    Private Sub EditControl_Leave(sender As Object, e As EventArgsHandles EditControl.Leave
        EditControl = Nothing
    End Sub
    Private Sub EditControl_TextChanged(ByVal sender As ObjectByVal e As System.EventArgsHandles EditControl.TextChanged
        'cast the sender Object to a DataGridViewTextBoxEditingControl so that you can access the text of the cell that is in the editing mode
        Dim ec As DataGridViewTextBoxEditingControl = DirectCast(sender, DataGridViewTextBoxEditingControl)
        Label1.Text = ec.Text 'you will see the label change as the selected cell text is changing
    End Sub
End Class



DataGridView: Detect changed row


This sample shows how to use the currencymanager with a datagridview.
This sample needs only a form with two DataGridViews
Untitled 9
Public Class Form1
    Sub Form1_Load(ByVal sender As System.Object, _
     ByVal e As System.EventArgsHandles MyBase.Load
        Dim dt As New DataTable
        dt.Columns.Add("Name")
        dt.Columns.Add("Place")
        dt.LoadDataRow(New Object() {"Cor""Holland"}, True)
        dt.LoadDataRow(New Object() {"Ken""Florida"}, True)
        dt.LoadDataRow(New Object() {"Paul""Illinois"}, True)
        dt.LoadDataRow(New Object() {"Herfried""Austria"}, True)
        dt.LoadDataRow(New Object() {"Armin""Germany"}, True)
        dt.LoadDataRow(New Object() {"John""UK"}, True)
        dt.LoadDataRow(New Object() {"Mike""SjangHai"}, True)
        DataGridView1.DataSource = dt.DefaultView
        Dim cma As CurrencyManager = DirectCast _
        (BindingContext(dt.DefaultView), CurrencyManager)
        AddHandler cma.CurrentChanged, AddressOf rowchanging
        rowchanging(MeNothing)
    End Sub
    Public Sub rowchanging(ByVal sender As Object, _
        ByVal e As EventArgs)
        Dim dv1 = DirectCast(DataGridView1.DataSource, DataView)
        Dim dv2 As New DataView(dv1.Table)
        Dim cma = DirectCast(BindingContext(dv1), CurrencyManager)
        dv2.RowFilter = "Name = '" & dv1(cma.Position)("Name").ToString & "'"
        DataGridView2.DataSource = dv2
    End Sub
End Class
 



Notify that a Sort Event takes place


When you sort a DataGridView the dataview will fire a listchanged event. To be notified when the grid is sorted add a handler to the dataviews listchanged event. Here is some sample code.

Untitled 1

Imports System.Data.SqlClient

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object,

                           ByVal e As System.EventArgsHandles MyBase.Load

        Using da As New SqlDataAdapter("Select * From Products",

                                       "Server = YourServerName;Database" & _

                                       " = NorthWind;Integrated Security = SSPI;")

            Dim dt As New DataTable

            da.Fill(dt)

            DataGridView1.DataSource = dt.DefaultView

            AddHandler dt.DefaultView.ListChanged, AddressOf ListChanged

        End Using

    End Sub

 

    Private Sub ListChanged(ByVal sender As Object,

                            ByVal e As System.ComponentModel.ListChangedEventArgs)

        Dim dv = DirectCast(DataGridView1.DataSource, DataView)

        MessageBox.Show(String.Format("Sort on column {0}"dv.Sort))

    End Sub

End Class




Sort a string containing only values numeric


To try this sample, drag a datagrid or a datagridview on a form, past this code in and run
Untitled 9
Public Class Form1
 Private Sub Form1_Load(ByVal sender _
       As System.ObjectByVal e As System.EventArgs) _
       Handles MyBase.Load
        'Creating test table
        Dim dt As New DataTable
        dt.Columns.Add("string")
        For i As Integer = 0 To 100
            dt.Rows.Add(dt.NewRow)
            dt.Rows(i)(0) = i.ToString
        Next
 
        'Sample
        dt.Columns.Add("integer", _
                    GetType(System.Int32), "string")
        dt.DefaultView.Sort = "integer"
        dt.Columns("integer").ColumnMapping = _
                 MappingType.Hidden
        DataGridView1.DataSource = dt
    End Sub
End Class



1 2 3 4 5  ... Previous Next