Update datatable

8 posts, 0 answers
  1. neil
    neil avatar
    7 posts
    Member since:
    Nov 2016

    Posted 15 Oct Link to this post

    Hi

     

    I have got the following code in my form load function;

            da_products = New OleDb.OleDbDataAdapter(SQL_String, con)

            dt_Products = New DataTable

            'Create Datatable
            da_products.Fill(dt_Products)

            'Assign datatable to datagrid
            RadGridView1.DataSource = dt_Products
            RadGridView1.Columns(0).Width = 100
            RadGridView1.Columns(1).Width = 300

        End Sub

    This displays the database as expected (two text fields and around twenty tick boxes).

     

    I then have a button to allow me to save the RADGRIDVIEW back to my database, but this does not work and comes up with error

    System.InvalidOperationException: 'Update requires a valid UpdateCommand when passed DataRow collection with modified rows.'

     

    Code in update button;

     

        Private Sub CmdDatabaseSave_Click(sender As Object, e As EventArgs) Handles cmdDatabaseSave.Click
                    da_products.Update(dt_Products)

      End Sub

     

    da_products and dt_Products are defined as global

      Public da_products As OleDb.OleDbDataAdapter
        Public dt_Products As DataTable

     

    Thanks

    Neil

     

  2. Dess | Tech Support Engineer, Sr.
    Admin
    Dess | Tech Support Engineer, Sr.  avatar
    3424 posts

    Posted 15 Oct Link to this post

    Hello, Neil,      

    According to the provided information, it seems that you need to populate RadGridView with data coming from your database and once any changes occur in the grid, they should be stored to back to the database. I would recommend you to bind directly to the table from your local server. Then, please have a look at the following help article demonstrating how to bind the grid and save the changes:
    https://docs.telerik.com/devtools/winforms/controls/gridview/populating-with-data/updating-the-database-with-ado.net

    I hope this information helps. If you need any further assistance please don't hesitate to contact me. 

    Regards
    Dess | Tech Support Engineer, Sr.
    Progress Telerik

    Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
  3. neil
    neil avatar
    7 posts
    Member since:
    Nov 2016

    Posted 15 Oct in reply to Dess | Tech Support Engineer, Sr. Link to this post

    Thanks, I have copied the code but

     

    da_Products.Update(lastEditRow) is giving a error BC30518 - Overload resolution failed because no accessible 'Update' can be called with these arguments.

     

    Please help?

     

     

    Neil

     

     

  4. Dess | Tech Support Engineer, Sr.
    Admin
    Dess | Tech Support Engineer, Sr.  avatar
    3424 posts

    Posted 16 Oct Link to this post

    Hello, Neil,      

    According to the previously provided code snippet, it seems that you create a brand new DataTable and use it as a DataSource for RadGridView. The grid is not directly bound to the table from your local server which is expected in order to save the changes back to the local server as it is demonstrated in the help article. Otherwise, you need to execute the respective SQL queries (update, insert, delete) and update the database. Additional information about such SQL queries can be found in general programming forums like MSDN and StackOverflow.

    I have prepared a sample project for your reference which implemented behavior is illustrated in the attached gif file. Note that the grid is bound to the Northwind.Employees table from my local server. I have included a backup file of the zip as well. It is just necessary to adjust the connection string in the App.config file to point to your server. 

    I hope this information helps. 

     

    Regards,
    Dess | Tech Support Engineer, Sr.
    Progress Telerik

    Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
  5. neil
    neil avatar
    7 posts
    Member since:
    Nov 2016

    Posted 17 Oct in reply to Dess | Tech Support Engineer, Sr. Link to this post

    Thanks, I will try over the next few days and come back to you with any further questions.
  6. Dess | Tech Support Engineer, Sr.
    Admin
    Dess | Tech Support Engineer, Sr.  avatar
    3424 posts

    Posted 21 Oct Link to this post

    Hello, Neil,

    Feel free to take as much time as you need.

    If you need any further assistance please don't hesitate to contact me. 

    Regards,
    Dess | Tech Support Engineer, Sr.
    Progress Telerik

    Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
  7. neil
    neil avatar
    7 posts
    Member since:
    Nov 2016

    Posted 22 Oct in reply to Dess | Tech Support Engineer, Sr. Link to this post

    Hi

    I have copied your code and it still gives the same error, please see the screen shot.

    I am declaring data adapter like this;

    Public da_Products As OleDb.OleDbDataAdapter
        Public dt_Products As DataTable

    Dim SQL_String As String
            Database_Path_Name = Application.StartupPath & "\Product Tracking.accdb"
            DBpath = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Database_Path_Name
            con.ConnectionString = DBpath ' "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Product Tracking.accdb" '"PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\AddressBook.mdb"
            con.Open()
            'SQL String
            SQL_String = "SELECT * " &
                    "FROM Products2 " &
                    "ORDER BY Product_Code ASC"
            'Open Database
            da_products = New OleDb.OleDbDataAdapter(SQL_String, con)
            dt_Products = New DataTable

            'Create Datatable
            da_products.Fill(dt_Products)
            'Assign datatable to datagrid
            RadGridView1.DataSource = dt_Products

    what is my problem?

     

    Thanks

     

    Neil

     

     

  8. Dess | Tech Support Engineer, Sr.
    Admin
    Dess | Tech Support Engineer, Sr.  avatar
    3424 posts

    Posted 23 Oct Link to this post

    Hello, Neil,      

    To be honest, your question seems to be more related to general programming forums like StackOverflow and MSDN since updating the database is a common question. After some research, I have found the following threads which may be suitable for your case and help you address the problems you are facing:
    https://www.codeproject.com/Tips/1179568/Updating-Database-from-DataGridView-Using-a-Class
    https://social.msdn.microsoft.com/Forums/en-US/47ad49d3-ab5f-4713-b24a-4447ffb21a57/c-how-to-update-access-database-from-datagridview?forum=winformsdatacontrols
    https://stackoverflow.com/questions/8653523/update-database-from-datagridview 
     
    I hope this information helps.

    Regards,
    Dess | Tech Support Engineer, Sr.
    Progress Telerik

    Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
Back to Top