VB.Net/Database ADO.net/DataGrid

Bind data into a data grid and do the update

<source lang="vbnet"> Imports System Imports System.Collections Imports System.Data Imports System.IO Imports System.Xml.Serialization Imports System.Xml Imports System.Windows.Forms Imports System.Data.SqlClient Imports System.Data.OleDb Public Class MainClass

   Shared Sub Main()
       Dim form1 As Form = New Form1
       Application.Run(form1)
   End Sub

End Class

Public Class Form1

   Inherits System.Windows.Forms.Form

1. Region " Windows Form Designer generated code "

   Public Sub New()
       MyBase.New()
       "This call is required by the Windows Form Designer.
       InitializeComponent()
       "Add any initialization after the InitializeComponent() call
   End Sub
   "Form overrides dispose to clean up the component list.
   Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
       If disposing Then
           If Not (components Is Nothing) Then
               components.Dispose()
           End If
       End If
       MyBase.Dispose(disposing)
   End Sub
   "Required by the Windows Form Designer
   Private components As System.ruponentModel.IContainer
   "NOTE: The following procedure is required by the Windows Form Designer
   "It can be modified using the Windows Form Designer.  
   "Do not modify it using the code editor.
   Friend WithEvents dg As System.Windows.Forms.DataGrid
   <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
       Me.dg = New System.Windows.Forms.DataGrid()
       CType(Me.dg, System.ruponentModel.ISupportInitialize).BeginInit()
       Me.SuspendLayout()
       "
       "dg
       "
       Me.dg.AlternatingBackColor = System.Drawing.Color.LightGray
       Me.dg.BackColor = System.Drawing.Color.DarkGray
       Me.dg.CaptionBackColor = System.Drawing.Color.White
       Me.dg.CaptionFont = New System.Drawing.Font("Verdana", 10.0!)
       Me.dg.CaptionForeColor = System.Drawing.Color.Navy
       Me.dg.CaptionText = "Employee"
       Me.dg.DataMember = ""
       Me.dg.ForeColor = System.Drawing.Color.Black
       Me.dg.GridLineColor = System.Drawing.Color.Black
       Me.dg.GridLineStyle = System.Windows.Forms.DataGridLineStyle.None
       Me.dg.HeaderBackColor = System.Drawing.Color.Silver
       Me.dg.HeaderForeColor = System.Drawing.Color.Black
       Me.dg.LinkColor = System.Drawing.Color.Navy
       Me.dg.Location = New System.Drawing.Point(9, 7)
       Me.dg.Name = "dg"
       Me.dg.ParentRowsBackColor = System.Drawing.Color.White
       Me.dg.ParentRowsForeColor = System.Drawing.Color.Black
       Me.dg.SelectionBackColor = System.Drawing.Color.Navy
       Me.dg.SelectionForeColor = System.Drawing.Color.White
       Me.dg.Size = New System.Drawing.Size(352, 440)
       Me.dg.TabIndex = 1
       "
       "Form1
       "
       Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
       Me.ClientSize = New System.Drawing.Size(370, 455)
       Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.dg})
       Me.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle
       Me.MaximizeBox = False
       Me.MinimizeBox = False
       Me.Name = "Form1"
       Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen
       Me.Text = "Employee Editor"
       CType(Me.dg, System.ruponentModel.ISupportInitialize).EndInit()
       Me.ResumeLayout(False)
   End Sub

1. End Region

   Private da As SqlDataAdapter
   Private ds As DataSet
   Private Sub Form1_Load(ByVal sender As System.Object, _
           ByVal e As System.EventArgs) Handles MyBase.Load
       " Create a Connection object
       Dim dbConn As New SqlConnection("Server=(local)\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=SSPI")

       " Create the data adapter object pointing to the authors table
       da = New SqlDataAdapter( _
             "SELECT ID, FirstName, LastName FROM Employee", dbConn)
       " Fill the DataSet
       ds = New DataSet("Employee")
       da.Fill(ds)
       " Display the records in a DataGrid component
       dg.DataSource = ds.Tables(0)
   End Sub
   Private Sub Form1_Closing(ByVal sender As Object, _
     ByVal e As System.ruponentModel.CancelEventArgs) Handles MyBase.Closing
       " Message box to prompt the save request
       If (MessageBox.Show("Do you want save the changes?", _
                           "Update", _
                           MessageBoxButtons.YesNo) = DialogResult.Yes) Then
           Try
               " Create the insert, delete and update statements automatically
               Dim cb As New SqlCommandBuilder(da)
               " Retrieve just the changed rows
               Dim dsChanges As DataSet = ds.GetChanges()
               If Not dsChanges Is Nothing Then
                   " Update the database
                   da.Update(dsChanges)
                   " Accept the changes within the DataSet
                   ds.AcceptChanges()
               End If
           Catch ex As Exception
               " Error occurs, show the message
               MessageBox.Show(ex.Message)
           End Try
       End If
   End Sub

End Class

      </source>

Click button to Update DataGrid

<source lang="vbnet"> Imports System Imports System.Data Imports System.Windows.Forms Imports System.Data.SqlClient Imports System.IO public class MainClass

  Shared Sub Main()
     Dim form1 As Form = New Form1
     Application.Run(form1)
  End Sub

End Class Public Class Form1

  Inherits System.Windows.Forms.Form
  Private cb As SqlCommandBuilder
  Private da As SqlDataAdapter

1. Region " Windows Form Designer generated code "

  Public Sub New()
     MyBase.New()
     "This call is required by the Windows Form Designer.
     InitializeComponent()
     "Add any initialization after the InitializeComponent() call
  End Sub
  "Form overrides dispose to clean up the component list.
  Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
     If disposing Then
        If Not (components Is Nothing) Then
           components.Dispose()
        End If
     End If
     MyBase.Dispose(disposing)
  End Sub
  "Required by the Windows Form Designer
  Private components As System.ruponentModel.IContainer
  "NOTE: The following procedure is required by the Windows Form Designer
  "It can be modified using the Windows Form Designer.  
  "Do not modify it using the code editor.
  Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
  Friend WithEvents buttonUpdate As System.Windows.Forms.Button
  Friend WithEvents DataSet1 As System.Data.DataSet
  Friend WithEvents SqlCommand1 As System.Data.SqlClient.SqlCommand
  <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
     Me.DataGrid1 = New System.Windows.Forms.DataGrid
     Me.buttonUpdate = New System.Windows.Forms.Button
     Me.DataSet1 = New System.Data.DataSet
     Me.SqlCommand1 = New System.Data.SqlClient.SqlCommand
     CType(Me.DataGrid1, System.ruponentModel.ISupportInitialize).BeginInit()
     CType(Me.DataSet1, System.ruponentModel.ISupportInitialize).BeginInit()
     Me.SuspendLayout()
     "
     "DataGrid1
     "
     Me.DataGrid1.DataMember = ""
     Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
     Me.DataGrid1.Location = New System.Drawing.Point(8, 8)
     Me.DataGrid1.Name = "DataGrid1"
     Me.DataGrid1.Size = New System.Drawing.Size(400, 192)
     Me.DataGrid1.TabIndex = 0
     "
     "buttonUpdate
     "
     Me.buttonUpdate.Location = New System.Drawing.Point(171, 208)
     Me.buttonUpdate.Name = "buttonUpdate"
     Me.buttonUpdate.TabIndex = 1
     Me.buttonUpdate.Text = "Update"
     "
     "DataSet1
     "
     Me.DataSet1.DataSetName = "NewDataSet"
     Me.DataSet1.Locale = New System.Globalization.CultureInfo("en-GB")
     "
     "Form1
     "
     Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
     Me.ClientSize = New System.Drawing.Size(416, 245)
     Me.Controls.Add(Me.buttonUpdate)
     Me.Controls.Add(Me.DataGrid1)
     Me.Name = "Form1"
     Me.Text = "Form1"
     CType(Me.DataGrid1, System.ruponentModel.ISupportInitialize).EndInit()
     CType(Me.DataSet1, System.ruponentModel.ISupportInitialize).EndInit()
     Me.ResumeLayout(False)
  End Sub

1. End Region

  Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
     "Create Connection object
     Dim thisConnection As New SqlConnection("server=(local)\SQLEXPRESS;" & _
         "integrated security=sspi;database=MyDatabase")

     " Sql Query
     Dim sql As String = _
        "SELECT * FROM Employee "
     " Create a Command
     SqlCommand1 = New SqlCommand(sql, thisConnection)
     " Create SqlDataAdapter
     da = New SqlDataAdapter
     da.SelectCommand = SqlCommand1
     " Create SqlCommandBuilder object
     cb = New SqlCommandBuilder(da)
     " Fill Dataset
     da.Fill(DataSet1, "Employee")
     " Bind the data to the grid at runtime
     DataGrid1.SetDataBinding(DataSet1, "Employee")
  End Sub
  Private Sub buttonUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles buttonUpdate.Click
     da.Update(DataSet1, "Employee")
  End Sub

End Class

      </source>

DataGrid: Load data table with data reader

<source lang="vbnet"> Imports System Imports System.Data Imports System.Windows.Forms Imports System.Data.SqlClient Imports System.IO public class MainClass

  Shared Sub Main()
     Dim form1 As Form = New Form1
     Application.Run(form1)
  End Sub

End Class Public Class Form1

  Inherits System.Windows.Forms.Form

1. Region " Windows Form Designer generated code "

  Public Sub New()
     MyBase.New()
     "This call is required by the Windows Form Designer.
     InitializeComponent()
     "Add any initialization after the InitializeComponent() call
  End Sub
  "Form overrides dispose to clean up the component list.
  Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
     If disposing Then
        If Not (components Is Nothing) Then
           components.Dispose()
        End If
     End If
     MyBase.Dispose(disposing)
  End Sub
  "Required by the Windows Form Designer
  Private components As System.ruponentModel.IContainer
  "NOTE: The following procedure is required by the Windows Form Designer
  "It can be modified using the Windows Form Designer.  
  "Do not modify it using the code editor.
  Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
  <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
     Me.DataGrid1 = New System.Windows.Forms.DataGrid
     CType(Me.DataGrid1, System.ruponentModel.ISupportInitialize).BeginInit()
     Me.SuspendLayout()
     "
     "DataGrid1
     "
     Me.DataGrid1.DataMember = ""
     Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
     Me.DataGrid1.Location = New System.Drawing.Point(8, 0)
     Me.DataGrid1.Name = "DataGrid1"
     Me.DataGrid1.Size = New System.Drawing.Size(552, 280)
     Me.DataGrid1.TabIndex = 0
     "
     "Form1
     "
     Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
     Me.ClientSize = New System.Drawing.Size(568, 285)
     Me.Controls.Add(Me.DataGrid1)
     Me.Name = "Form1"
     Me.Text = "Form1"
     CType(Me.DataGrid1, System.ruponentModel.ISupportInitialize).EndInit()
     Me.ResumeLayout(False)
  End Sub

1. End Region

   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
       DataGrid1_Navigate(sender, Nothing)
   End Sub
   Private Sub DataGrid1_Navigate(ByVal sender As Object, ByVal ne As System.Windows.Forms.NavigateEventArgs) Handles DataGrid1.Navigate
       " Create Connection object
     Dim thisConnection As New SqlConnection("server=(local)\SQLEXPRESS;" & _
         "integrated security=sspi;database=MyDatabase")
       " Create Command Object
       Dim thisCommand As New SqlCommand _
          ("SELECT * FROM Employee", thisConnection)
       " Create Data Reader
       Dim thisReader As SqlDataReader
       Try
           " Open Connection
           thisConnection.Open()
           " Load data table with data reader
           thisReader = thisCommand.ExecuteReader()
           Dim dt As New DataTable("Employee")
           dt.Load(thisReader)
           " Bind data table to datagrid
           DataGrid1.DataSource = dt
           " Close Reader
           thisReader.Close()
       Catch ex As Exception
           MessageBox.Show(ex.Message)
       Finally
           " Close Connection
           thisConnection.Close()
       End Try
   End Sub

End Class

      </source>

Load data from database into DataGrid

<source lang="vbnet">

Imports System
Imports System.Drawing
Imports System.Collections
Imports System.ruponentModel
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.SqlClient

Public Class MainClass

   Shared Sub Main(  )
       Application.Run(New ADOForm1() )
   End Sub
  

End Class

 Public Class ADOForm1
    Inherits System.Windows.Forms.Form
    Private components As System.ruponentModel.Container
    Friend WithEvents EmployeeDataGrid As _
        System.Windows.Forms.DataGrid
    Public Sub New(  )
        InitializeComponent(  )
        Dim connectionString As String ="server=(local)\SQLEXPRESS;" & _
         "integrated security=sspi;database=MyDatabase"

        Dim commandString As String = _
          "Select FirstName, LastName from Employee"
        Dim myDataAdapter As _
        New SqlDataAdapter(commandString, connectionString)
        Dim myDataSet As New DataSet(  )
        myDataAdapter.Fill(myDataSet, "Employee")
        " bind the DataSet to the grid
        EmployeeDataGrid.DataSource = _
            myDataSet.Tables("Employee").DefaultView
    End Sub "New
    Private Sub InitializeComponent(  )
        Me.ruponents = New System.ruponentModel.Container(  )
        Me.EmployeeDataGrid = New System.Windows.Forms.DataGrid(  )
        EmployeeDataGrid.Location = New System.Drawing.Point(48, 24)
        EmployeeDataGrid.Size = New System.Drawing.Size(368, 160)
        EmployeeDataGrid.TabIndex = 0
        Me.Text = "ADOFrm1"
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(464, 273)
        Me.Controls.Add(EmployeeDataGrid)
    End Sub 
End Class 
          
      </source>

Load result set from "select" command into a DataGrid

<source lang="vbnet">

Imports System
Imports System.Drawing
Imports System.Collections
Imports System.ruponentModel
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.SqlClient

Public Class MainClass

   Shared Sub Main(  )
       Application.Run(New ADOForm1() )
   End Sub
  

End Class

 Public Class ADOForm1
    Inherits System.Windows.Forms.Form
    Private components As System.ruponentModel.Container
    Private dataGrid1 As System.Windows.Forms.DataGrid
    " private System.Data.ADO.ADOConnection myConnection;
    Private myConnection As System.Data.SqlClient.SqlConnection
    Private myDataSet As System.Data.DataSet
    Private myCommand As System.Data.SqlClient.SqlCommand
    Private myDataAdapter As System.Data.SqlClient.SqlDataAdapter
    Public Sub New(  )
        InitializeComponent(  )
        Dim connectionString As String ="server=(local)\SQLEXPRESS;" & _
         "integrated security=sspi;database=MyDatabase"

        myConnection = _
            New System.Data.SqlClient.SqlConnection(connectionString)
        myConnection.Open(  )
        " create the DataSet and set a property
        myDataSet = New System.Data.DataSet(  )
        myDataSet.CaseSensitive = True
        " create the SqlCommand  object and assign the
        " connection and the select statement
        myCommand = New System.Data.SqlClient.SqlCommand(  )
        myCommand.Connection = myConnection
        myCommand.rumandText = "Select * from Employee"
        " create the myDataAdapter object and pass in the
        " SQL Command object and establish the table mappings
        myDataAdapter = New System.Data.SqlClient.SqlDataAdapter(  )
        myDataAdapter.SelectCommand = myCommand
        myDataAdapter.TableMappings.Add("Table", "Employee")
        " Tell the myDataAdapter object to fill the DataSet
        myDataAdapter.Fill(myDataSet)
        " display it in the grid
        dataGrid1.DataSource = _
            myDataSet.Tables("Employee").DefaultView
    End Sub "New
    Private Sub InitializeComponent(  )
        Me.ruponents = New System.ruponentModel.Container(  )
        Me.dataGrid1 = New System.Windows.Forms.DataGrid(  )
        dataGrid1.Location = New System.Drawing.Point(48, 24)
        dataGrid1.Size = New System.Drawing.Size(368, 160)
        dataGrid1.TabIndex = 0
        Me.Text = "ADOFrm1"
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(464, 273)
        Me.Controls.Add(dataGrid1)
    End Sub 
End Class 
          
      </source>