locked
Save All Data in Datagridview to Access DB RRS feed

  • Question

  • Hello, I am trying to save all data that are in a datagridview to my Access DB. The data are added via code:

    Me.dgvHistory.Rows.Add(Environment.UserName.ToLower, DateAndTime.Now, "Uploaded Raw Data")

    I am not sure how to use a datatable in this scenario because the data that will be put into the datagridview will come from 3 separate forms. Basically everytime a user clicks on some buttons, the action done will be saved into the datagridview.

    Any suggestions?

    Wednesday, July 29, 2020 5:22 PM

All replies

  • In general it's not wise to simple add rows to a DataGridView, instead at the very least is to set a DataTable with defined columns as the DataSource to the DataGridView. Then to add a new row

    CType(dgvHistory.DataSource,DataTable).Rows.Add(new Object() {...}) where each dot is data obtained from as you said different forms. If you want to add each row immediately, send the data first to a procedure with a SQL insert statement, if the insert works then add the row to the DataGridView as per above. If inserts will be done later then you can pass the DataTable to a procedure and add all rows at once.

    Another option is to use a class instead of a DataTable e.g. 

    CType(dgvHistory.DataSource,List(Of Customer)).Add(....

    Public Class Customer
        Public Property Id As Integer
        Public Property CompanyName As String
        Public Property ContactName As String
        Public Property EstablishedYear() As Integer
        Public Property Incorporated() As Date
    
    End Class

    You then pass that list to a function with a signature like this

    Public Function AddNewCustomerRecords(customersList As List(Of Customer)) As Boolean

    Which is responsible for adding each customer in the list to a Access table

    See this code for the backend data operations. For simplicity I use mocked data to demonstrate  

    Public Class MainForm
        Property customerBindingSource() As New BindingSource
        ''' <summary>
        ''' Add multiple records. Pressing this button multiple times will only show
        ''' the current batch added for confirmation rather than loading all records
        ''' which the next button does, loads all records.
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub addRecordButton_Click(sender As Object, e As EventArgs) Handles addRecordButton.Click
            Dim ops As New DataOperations
            Dim mockedList As New CustomerData
            Dim newCustomersInDatabase = mockedList.CustomerList()
            ops.AddNewCustomerRecords(newCustomersInDatabase)
            If ops.IsSuccessFul Then
                customerBindingSource.DataSource = newCustomersInDatabase
                DataGridView1.AutoGenerateColumns = False
                DataGridView1.DataSource = customerBindingSource
            End If
        End Sub
        ''' <summary>
        ''' Load all customer records added using the button click event above.
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub readAllCustomerRecordsButton_Click(sender As Object, e As EventArgs) Handles readAllCustomerRecordsButton.Click
            Dim ops As New DataOperations
            Dim customersList = ops.ReadCustomerTable()
            If customersList.Count > 0 Then
                customerBindingSource.DataSource = customersList
            Else
                MessageBox.Show("Please add some records first before reading the Customer table")
            End If
        End Sub
    End Class

    For more on working with VB.NET and MS-Access see my GitHub repository.

    https://github.com/karenpayneoregon/WorkingWithAccessDatabases

    In closing, sometimes a DataTable is better than a List as presented above and sometimes a List is better than a DataTable. I like List most of the time over DataTable as they are lighter weight.

    Also note with a DataTable you can use a DataAdapter which may be worth looking at


    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


    Wednesday, July 29, 2020 5:42 PM
  • Hi Karen, thank you for your response. I don't really need the datagridview, I was just thinking about consolidating data into it then send them altogether to the database.

    In this case, what would you suggest?

    Wednesday, July 29, 2020 6:07 PM
  • The following is to be considered a rough outline.

    Public Class Customer
        Public Property Id As Integer
        Public Property CompanyName As String
        Public Property ContactName As String
        Public Property EstablishedYear() As Integer
        Public Property Incorporated() As Date
    End Class

    Note I show how to do the guts of the operations in the link in my first reply.

    Public Class AccessOperations
        Public Shared CustomerList As New List(Of Customer)
        Public Shared Sub AddCustomers()
            For Each customero As Customer In CustomerList
                ' add each customer
            Next
            '
            ' Now if there needs to be more added clear the list first
            '
            CustomerList.Clear()
    
        End Sub
    End Class

    Here I used mocked data

    AccessOperations.CustomerList.Add(New Customer() With {
                                         .CompanyName = "ABC",
                                         .ContactName = "Jim Smith",
                                         .EstablishedYear = 1992,
                                         .Incorporated = #01/02/2000#})
    AccessOperations.CustomerList.Add(New Customer() With {
                                         .CompanyName = "BBB",
                                         .ContactName = "Mary Adams",
                                         .EstablishedYear = 2000,
                                         .Incorporated = #01/02/2001#})
    AccessOperations.CustomerList.Add(New Customer() With {
                                         .CompanyName = "CCC",
                                         .ContactName = "Mark Gallagher",
                                         .EstablishedYear = 2001,
                                         .Incorporated = #01/02/2002#})
    AccessOperations.AddCustomers()


    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

    Wednesday, July 29, 2020 7:11 PM
  • Hi Ash.22,

    As Karen suggested, you can bind the DataTable to the DataGridView and add rows to the DataTable to update the database.

    The following is an example of using a DataAdapter to update database from the DataTable bound in a DataGridView:

        Private da As OleDbDataAdapter
        Private bindingSource As BindingSource = Nothing
        Private oleCommandBuilder As OleDbCommandBuilder = Nothing
        Private dataTable As DataTable = New DataTable()
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            BindData()
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            DataGridView1.EndEdit()
    
            dataTable.Rows.Add(Environment.UserName.ToLower, DateAndTime.Now, "Uploaded Raw Data")
    
            da.Update(dataTable)
            MessageBox.Show("Updated")
            BindData()
        End Sub
    
        Private Sub BindData()
            DataGridView1.DataSource = Nothing
            dataTable.Clear()
            Dim connectionString As String = "your connection string"
            Dim queryString As String = "SELECT * FROM mytable"
            Dim connection As OleDbConnection = New OleDbConnection(connectionString)
            connection.Open()
            Dim command As OleDbCommand = connection.CreateCommand()
            command.CommandText = queryString
    
            Try
                da = New OleDbDataAdapter(queryString, connection)
                oleCommandBuilder = New OleDbCommandBuilder(da)
                oleCommandBuilder.QuotePrefix = "["
                oleCommandBuilder.QuoteSuffix = "]"
                da.Fill(dataTable)
                bindingSource = New BindingSource With {
                    .DataSource = dataTable
                }
                DataGridView1.DataSource = bindingSource
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
            End Try
        End Sub

    Hope it can help you.

    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.

    Friday, July 31, 2020 8:18 AM