none
Importing data to SQL client table from Data Grid View control RRS feed

  • Question

  • In the previous thread, I somebody showed me how to import its data, that is rows and columns to its data grid view control.

    Now what about importing data to a SQL client table from its data grid view control?

    Can anyone provide me some examples?

    Regards,

    JohnDBCTX


    jp

    Wednesday, February 5, 2020 7:37 AM

Answers

  • Hi John,
    you can use DataAdapters Update method like in follwing demo:

    Imports System.Data.SqlClient
    
    Public Class Form1
    
      Private ClassOneObject As New ClassOne
    
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        With ClassOneObject.Buttons(0)
          .Dock = DockStyle.Top
          .Text = "Save changed data"
          AddHandler ClassOneObject.Buttons(0).Click, AddressOf btn_Save
        End With
        ClassOneObject.DataGridViews(0).Dock = DockStyle.Fill
    
        Me.Controls.AddRange(New Control() {ClassOneObject.DataGridViews(0), ClassOneObject.Buttons(0)})
    
        ClassOneObject.DataTables(0).Clear()
        Using cn = ClassOneObject.Connections(0)
          cn.ConnectionString = My.Settings.cnSQL
          Using da As New SqlDataAdapter("SELECT * FROM Tab1", cn)
            da.Fill(ClassOneObject.DataTables(0))
          End Using
        End Using
        ClassOneObject.BindingSources(0).DataSource = ClassOneObject.DataTables(0)
        ClassOneObject.DataGridViews(0).DataSource = ClassOneObject.BindingSources(0)
      End Sub
    
      Private Sub btn_Save(sender As Object, e As EventArgs)
        Using cn = ClassOneObject.Connections(0)
          cn.ConnectionString = My.Settings.cnSQL
          Using da As New SqlDataAdapter("SELECT * FROM Tab1", cn)
            Dim cb As New SqlCommandBuilder(da)
            da.Update(ClassOneObject.DataTables(0))
          End Using
        End Using
      End Sub
    
      Public Class ClassOne
    
        Private Btn As New Button
        Private Connect As New SqlConnection
        Private Cmd As New SqlCommand
        Private dt1 As New DataTable
    
        Public ReadOnly Property Buttons() As Button()
          Get
            Return New Button() {Btn}
          End Get
        End Property
    
        Public ReadOnly Property Connections() As SqlConnection()
          Get
            Return New SqlConnection() {Connect}
          End Get
        End Property
    
        Public ReadOnly Property Commands() As SqlCommand()
          Get
            Return New SqlCommand() {Cmd}
          End Get
        End Property
    
        Public ReadOnly Property DataTables() As DataTable()
          Get
            Return New DataTable() {dt1}
          End Get
        End Property
    
        Private BndSrc As New BindingSource
        Private DatGrd As New DataGridView
    
        Public ReadOnly Property DataGridViews() As DataGridView()
          Get
            Return New DataGridView() {DatGrd}
          End Get
        End Property
    
        Public ReadOnly Property BindingSources() As BindingSource()
          Get
            Return New BindingSource() {BndSrc}
          End Get
        End Property
      End Class
    
    End Class


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    • Marked as answer by JohnDBCTX Wednesday, February 5, 2020 9:17 AM
    Wednesday, February 5, 2020 8:40 AM

All replies

  • Hi John,
    you can use DataAdapters Update method like in follwing demo:

    Imports System.Data.SqlClient
    
    Public Class Form1
    
      Private ClassOneObject As New ClassOne
    
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        With ClassOneObject.Buttons(0)
          .Dock = DockStyle.Top
          .Text = "Save changed data"
          AddHandler ClassOneObject.Buttons(0).Click, AddressOf btn_Save
        End With
        ClassOneObject.DataGridViews(0).Dock = DockStyle.Fill
    
        Me.Controls.AddRange(New Control() {ClassOneObject.DataGridViews(0), ClassOneObject.Buttons(0)})
    
        ClassOneObject.DataTables(0).Clear()
        Using cn = ClassOneObject.Connections(0)
          cn.ConnectionString = My.Settings.cnSQL
          Using da As New SqlDataAdapter("SELECT * FROM Tab1", cn)
            da.Fill(ClassOneObject.DataTables(0))
          End Using
        End Using
        ClassOneObject.BindingSources(0).DataSource = ClassOneObject.DataTables(0)
        ClassOneObject.DataGridViews(0).DataSource = ClassOneObject.BindingSources(0)
      End Sub
    
      Private Sub btn_Save(sender As Object, e As EventArgs)
        Using cn = ClassOneObject.Connections(0)
          cn.ConnectionString = My.Settings.cnSQL
          Using da As New SqlDataAdapter("SELECT * FROM Tab1", cn)
            Dim cb As New SqlCommandBuilder(da)
            da.Update(ClassOneObject.DataTables(0))
          End Using
        End Using
      End Sub
    
      Public Class ClassOne
    
        Private Btn As New Button
        Private Connect As New SqlConnection
        Private Cmd As New SqlCommand
        Private dt1 As New DataTable
    
        Public ReadOnly Property Buttons() As Button()
          Get
            Return New Button() {Btn}
          End Get
        End Property
    
        Public ReadOnly Property Connections() As SqlConnection()
          Get
            Return New SqlConnection() {Connect}
          End Get
        End Property
    
        Public ReadOnly Property Commands() As SqlCommand()
          Get
            Return New SqlCommand() {Cmd}
          End Get
        End Property
    
        Public ReadOnly Property DataTables() As DataTable()
          Get
            Return New DataTable() {dt1}
          End Get
        End Property
    
        Private BndSrc As New BindingSource
        Private DatGrd As New DataGridView
    
        Public ReadOnly Property DataGridViews() As DataGridView()
          Get
            Return New DataGridView() {DatGrd}
          End Get
        End Property
    
        Public ReadOnly Property BindingSources() As BindingSource()
          Get
            Return New BindingSource() {BndSrc}
          End Get
        End Property
      End Class
    
    End Class


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    • Marked as answer by JohnDBCTX Wednesday, February 5, 2020 9:17 AM
    Wednesday, February 5, 2020 8:40 AM
  • That is good. Now I have put it all together.

    Imports System.Data.SqlClient
    
    Public Class Form1
        Private ClassOneObject As New ClassOne
        Private DataObject As New Data
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Me.Controls.Add(ClassOneObject.DataGridViews(0))
            Dim dataList As New List(Of Data)
            For i = 1 To 1
                dataList.Add(New Data With {.NameID = i, .LastName = $"Row {i}", .FirstName = $"Row {i}"})
            Next
            ClassOneObject.BindingSources(0).DataSource = dataList
            ClassOneObject.DataGridViews(0).DataSource = ClassOneObject.BindingSources(0)
    
    
            For Each row As DataGridViewRow In ClassOneObject.DataGridViews(0).Rows
                ClassOneObject.Connections(0).ConnectionString = "Server=MYSERVER; Database=DBZ; Integrated Security=true"
                Using con As New SqlConnection(ClassOneObject.Connections(0).ConnectionString)
                    Using cmd As New SqlCommand("INSERT INTO dbo.Customers VALUES(@NameID, @LastName, @FirstName)", con)
                        cmd.Parameters.AddWithValue("@NameID", dataList(0).NameID)
                        cmd.Parameters.AddWithValue("@LastName", dataList(0).LastName)
                        cmd.Parameters.AddWithValue("@FirstName", dataList(0).FirstName)
                        con.Open()
                        cmd.ExecuteNonQuery()
                        con.Close()
                    End Using
                End Using
            Next
            MessageBox.Show("Records inserted.")
        End Sub
    End Class
    
    Imports System.Data.SqlClient
    
    Public Class Data
        Public Property NameID As Integer
        Public Property LastName As String
        Public Property FirstName As String
    
    
    End Class
    
    Imports System.Data.SqlClient
    
    Public Class ClassOne
        Public Connect As New SqlConnection
        Public Cmd As New SqlCommand
        Public BndSrc As New BindingSource
        Public DatGrd As New DataGridView
        Public ReadOnly Property Connections() As SqlConnection()
            Get
                Return New SqlConnection() {Connect}
            End Get
        End Property
    
        Public ReadOnly Property Commands() As SqlCommand()
            Get
                Return New SqlCommand() {Cmd}
            End Get
        End Property
    
    
    
        Public ReadOnly Property DataGridViews() As DataGridView()
            Get
                Return New DataGridView() {DatGrd}
            End Get
        End Property
    
        Public ReadOnly Property BindingSources() As BindingSource()
            Get
                Return New BindingSource() {BndSrc}
            End Get
        End Property
    End Class
    
    
    

    ...and everything worked.

    Users can look at my source code snippet and they can use this as a template.

    Regards,

    JohnDBCTX


    jp

    Wednesday, February 5, 2020 9:16 AM