locked
How is code written for updating a DataGridView. RRS feed

  • Question

  • Hi Good People

    I have some textboxes on my form. also I have a DataGridView.

    When I select a row in the DataGridView I want to populate the textboxes with that row I selected in the DataGridView.

    Then, If any textbox value is changed, I then click a button to save the changes in the textboxes. But how is code written to Update the changes in the Textboxes to the DataGridView.

    Kind Regards

    Gary

    Here id an update, When I change text in any textbox and click a save button. my database is updated but the DataGridView isn't, But If I close the program and restart it the changes are made, Does anyone know how to update the DataGridView on the save button?


    Gary Simpson


    Thursday, May 28, 2020 11:39 AM

Answers

  • Hi Gary Simpson,

    Thank you for posting here.

    Every time you update the database, you can select data from your database and then update DataGridView.

    I make a sample on my side, and you can refer to the following code:

    My database:

    Code:

    Public Class Form1
        Dim cnn As SqlConnection = New SqlConnection()
        Dim intcID As Integer
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            cnn.ConnectionString = "your connection string"
        End Sub
        Private Sub showBtn_Click(sender As Object, e As EventArgs) Handles showBtn.Click
            RefreshData()
        End Sub
    
        Private Sub updateBtn_Click(sender As Object, e As EventArgs) Handles updateBtn.Click
            If Not cnn.State = ConnectionState.Open Then
                cnn.Open()
            End If
    
            Using cmd As SqlCommand = New SqlCommand()
                cmd.Connection = cnn
                cmd.CommandText = "UPDATE student SET Name = @name, Age = @age, Weight = @weight Where Id = " & intcID
                cmd.Parameters.AddWithValue("@name", Me.TextBox1.Text)
                cmd.Parameters.AddWithValue("@age", Me.TextBox2.Text)
                cmd.Parameters.AddWithValue("@weight", Me.TextBox3.Text)
                cmd.ExecuteNonQuery()
            End Using
            RefreshData()
            MsgBox("Update Data Successful", MsgBoxStyle.OkOnly, "Message")
        End Sub
        Private Sub RefreshData()
            If Not cnn.State = ConnectionState.Open Then
                cnn.Open()
            End If
            Dim da As New SqlDataAdapter("SELECT Id, Name, Age, Weight FROM student ORDER BY Id", cnn)
            Dim dt As New DataTable
    
            da.Fill(dt)
            Me.DataGridView1.DataSource = dt
            cnn.Close()
        End Sub
        Private Sub DataGridView1_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellClick
            If e.RowIndex >= 0 Then
                Dim row As DataGridViewRow = Me.DataGridView1.Rows(e.RowIndex)
                intcID = row.Cells("Id").Value
                TextBox1.Text = row.Cells("Name").Value.ToString
                TextBox2.Text = row.Cells("Age").Value.ToString
                TextBox3.Text = row.Cells("Weight").Value.ToString
            End If
        End Sub
    End Class

    Result of my test:

    Hope it could be helpful.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Gary Simpson Sunday, May 31, 2020 10:28 PM
    Friday, May 29, 2020 2:19 AM
  • Hello,

    Here is a simply example, data is loaded asynchronously, a DataTable contains data read from a database, identifier columns are hidden and the DataTable is set to a BindingSource (many don't learn about them, you should). Finally one column is data bound, FirstName column, repeat for each column needed in TextBox controls.

    backend class class

    Imports System.Data.SqlClient
    
    Public Class DataOperations
        Private Shared ReadOnly ConnectionString As String =
                           "Data Source=.\SQLEXPRESS;" &
                           "Initial Catalog=NorthWindAzureForInserts;" &
                           "Integrated Security=True"
    
        Public Shared Async Function ReadCustomers() As Task(Of DataTable)
    
            Return Await Task.Run(Async Function()
    
                                      Dim customersTable = New DataTable()
    
                                      Using cn = New SqlConnection(ConnectionString)
    
                                          Using cmd = New SqlCommand() With {.Connection = cn}
    
                                              cmd.CommandText = SelectStatement()
                                              Await cn.OpenAsync()
    
                                              customersTable.Load(Await cmd.ExecuteReaderAsync())
    
    
                                          End Using
    
                                      End Using
    
                                      customersTable.Columns.Cast(Of DataColumn).Where(Function(column) column.ColumnName.Contains("Id")).ToList().ForEach(
                                          Sub(column)
                                              column.ColumnMapping = MappingType.Hidden
                                          End Sub)
                                      Return customersTable
    
                                  End Function)
    
        End Function
        Private Shared Function SelectStatement() As String
            Return <SQL>
    SELECT Cust.CustomerIdentifier,
           Cust.CompanyName,
           Cust.ContactId,
           Contacts.FirstName,
           Contacts.LastName,
           Cust.ContactTypeIdentifier,
           CT.ContactTitle,
           Cust.Address AS Street,
           Cust.City,
           Cust.PostalCode,
           Cust.CountryIdentifier,
           Countries.Name AS CountryName
    FROM Customers AS Cust
         INNER JOIN ContactType AS CT ON Cust.ContactTypeIdentifier = CT.ContactTypeIdentifier
         INNER JOIN Contacts ON Cust.ContactId = Contacts.ContactId
         INNER JOIN Countries ON Cust.CountryIdentifier = Countries.CountryIdentifier
                   </SQL>.Value
        End Function
    
    End Class
    

    Form code

    Public Class Form1
        Private ReadOnly bindingSource As New BindingSource
        Private Async Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
            Dim result = Await DataOperations.ReadCustomers()
            bindingSource.DataSource = result
            DataGridView1.DataSource = bindingSource
    
            FirstNameTextBox.DataBindings.Add(
                "Text",
                bindingSource,
                "firstName")
    
        End Sub
    End Class
    

     


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Gary Simpson Sunday, May 31, 2020 10:28 PM
    Friday, May 29, 2020 2:08 PM

All replies

  • Hi Gary Simpson,

    Thank you for posting here.

    Every time you update the database, you can select data from your database and then update DataGridView.

    I make a sample on my side, and you can refer to the following code:

    My database:

    Code:

    Public Class Form1
        Dim cnn As SqlConnection = New SqlConnection()
        Dim intcID As Integer
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            cnn.ConnectionString = "your connection string"
        End Sub
        Private Sub showBtn_Click(sender As Object, e As EventArgs) Handles showBtn.Click
            RefreshData()
        End Sub
    
        Private Sub updateBtn_Click(sender As Object, e As EventArgs) Handles updateBtn.Click
            If Not cnn.State = ConnectionState.Open Then
                cnn.Open()
            End If
    
            Using cmd As SqlCommand = New SqlCommand()
                cmd.Connection = cnn
                cmd.CommandText = "UPDATE student SET Name = @name, Age = @age, Weight = @weight Where Id = " & intcID
                cmd.Parameters.AddWithValue("@name", Me.TextBox1.Text)
                cmd.Parameters.AddWithValue("@age", Me.TextBox2.Text)
                cmd.Parameters.AddWithValue("@weight", Me.TextBox3.Text)
                cmd.ExecuteNonQuery()
            End Using
            RefreshData()
            MsgBox("Update Data Successful", MsgBoxStyle.OkOnly, "Message")
        End Sub
        Private Sub RefreshData()
            If Not cnn.State = ConnectionState.Open Then
                cnn.Open()
            End If
            Dim da As New SqlDataAdapter("SELECT Id, Name, Age, Weight FROM student ORDER BY Id", cnn)
            Dim dt As New DataTable
    
            da.Fill(dt)
            Me.DataGridView1.DataSource = dt
            cnn.Close()
        End Sub
        Private Sub DataGridView1_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellClick
            If e.RowIndex >= 0 Then
                Dim row As DataGridViewRow = Me.DataGridView1.Rows(e.RowIndex)
                intcID = row.Cells("Id").Value
                TextBox1.Text = row.Cells("Name").Value.ToString
                TextBox2.Text = row.Cells("Age").Value.ToString
                TextBox3.Text = row.Cells("Weight").Value.ToString
            End If
        End Sub
    End Class

    Result of my test:

    Hope it could be helpful.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Gary Simpson Sunday, May 31, 2020 10:28 PM
    Friday, May 29, 2020 2:19 AM
  • Hello,

    Here is a simply example, data is loaded asynchronously, a DataTable contains data read from a database, identifier columns are hidden and the DataTable is set to a BindingSource (many don't learn about them, you should). Finally one column is data bound, FirstName column, repeat for each column needed in TextBox controls.

    backend class class

    Imports System.Data.SqlClient
    
    Public Class DataOperations
        Private Shared ReadOnly ConnectionString As String =
                           "Data Source=.\SQLEXPRESS;" &
                           "Initial Catalog=NorthWindAzureForInserts;" &
                           "Integrated Security=True"
    
        Public Shared Async Function ReadCustomers() As Task(Of DataTable)
    
            Return Await Task.Run(Async Function()
    
                                      Dim customersTable = New DataTable()
    
                                      Using cn = New SqlConnection(ConnectionString)
    
                                          Using cmd = New SqlCommand() With {.Connection = cn}
    
                                              cmd.CommandText = SelectStatement()
                                              Await cn.OpenAsync()
    
                                              customersTable.Load(Await cmd.ExecuteReaderAsync())
    
    
                                          End Using
    
                                      End Using
    
                                      customersTable.Columns.Cast(Of DataColumn).Where(Function(column) column.ColumnName.Contains("Id")).ToList().ForEach(
                                          Sub(column)
                                              column.ColumnMapping = MappingType.Hidden
                                          End Sub)
                                      Return customersTable
    
                                  End Function)
    
        End Function
        Private Shared Function SelectStatement() As String
            Return <SQL>
    SELECT Cust.CustomerIdentifier,
           Cust.CompanyName,
           Cust.ContactId,
           Contacts.FirstName,
           Contacts.LastName,
           Cust.ContactTypeIdentifier,
           CT.ContactTitle,
           Cust.Address AS Street,
           Cust.City,
           Cust.PostalCode,
           Cust.CountryIdentifier,
           Countries.Name AS CountryName
    FROM Customers AS Cust
         INNER JOIN ContactType AS CT ON Cust.ContactTypeIdentifier = CT.ContactTypeIdentifier
         INNER JOIN Contacts ON Cust.ContactId = Contacts.ContactId
         INNER JOIN Countries ON Cust.CountryIdentifier = Countries.CountryIdentifier
                   </SQL>.Value
        End Function
    
    End Class
    

    Form code

    Public Class Form1
        Private ReadOnly bindingSource As New BindingSource
        Private Async Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
            Dim result = Await DataOperations.ReadCustomers()
            bindingSource.DataSource = result
            DataGridView1.DataSource = bindingSource
    
            FirstNameTextBox.DataBindings.Add(
                "Text",
                bindingSource,
                "firstName")
    
        End Sub
    End Class
    

     


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Gary Simpson Sunday, May 31, 2020 10:28 PM
    Friday, May 29, 2020 2:08 PM