Click here to Skip to main content
13,355,335 members (72,526 online)
Rate this:
Please Sign up or sign in to vote.
When I try to save the data in a DataGridView to Access using the following code (button2), I get the error "Update requires a valid UpdateCommand when passed DataRow collection with modified rows." What am I doing wrong?
Imports System.Data.OleDb
Imports System.Data.SqlClient
Public Class Form1
    Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Phred\Documents\PhredTek\AutoPreVis\variable property test.accdb"
    Dim sql As String = "SELECT * FROM [TM000238 variables]"
    Dim connection As New OleDbConnection(connectionString)
    Dim dataadapter As New OleDbDataAdapter(sql, connection)
    Dim ds As New DataSet()
    Dim aTable As DataTable

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        dataadapter.Fill(ds, "[TM000238 variables]")
        aTable = ds.Tables("[TM000238 variables]")
        DataGridView1.DataSource = ds
        DataGridView1.DataMember = "[TM000238 variables]"
        DataGridView1.Columns(0).ReadOnly = True
    End Sub

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        dataadapter.Update(ds, "[TM000238 variables]")
    End Sub
End Class
Posted 23-Oct-12 7:33am
Rate this: bad
Please Sign up or sign in to vote.

Solution 3

I cannot rebuild your problem, but I can suggest you a .net dataexport component [^]to do this, it can easily export your datagrid to access, I provide part of source code to you below.besides, it can export data from command, datatable components, listview to excel,pdf,html,xml,clipboard,text,csv,dif,dbf,sylk etc.

Dim connection As OleDbConnection = New OleDbConnection
connection.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1;Jet OL"& _
"EDB:Database Password=;Data Source="""".\..\database\demo.mdb"""";Jet OLEDB:Engine Type=5;Jet OLEDB:Glob"& _
"al Bulk Transactions=1;Provider=""""Microsoft.Jet.OLEDB.4.0"""";Jet OLEDB:System database=;Jet OLEDB:SFP"& _
"=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:New Database Password=;Jet OLEDB:Create S"& _
"ystem Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica "& _
"Repair=False;User ID=Admin;Jet OLEDB:Encrypt Database=False"
Dim command As OleDbCommand = New OleDbCommand
command.CommandText = "select * from parts"
command.Connection = connection
Dim accessExport1 As Spire.DataExport.Access.AccessExport = New Access.AccessExport
accessExport1.DatabaseName = "test.mdb"
accessExport1.TableName = "ExportData"
accessExport1.SQLCommand = command
Fred Andres 24-Oct-12 9:41am
Thanks Lacy00
Lacy00 24-Oct-12 22:51pm
tell me whether it helps you, if you meet some problems?
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

do you have your binding source attached?
if you do try this in
if not you may look into adding one. They work really well for handling data retrieval and editing from the user end to the database.

I'm not quite sure how to save the entire DS at once.

I dragged the first item each table into the form on the designer mode of .net and it would auto-attach a dataset, table adapter, and binding source linking to each Access Table.

This is the code I used for saving each table seperately.

Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        Catch ex As Exception
            MsgBox(ex.Message & " - Failed update in form1button2click")
        End Try
End Sub
Fred Andres 23-Oct-12 13:14pm
Thanks. How do I attach a binding source?
Fred Andres 23-Oct-12 18:21pm
I finally figured out how to attach a binding souce and it still wouldn't work. After searching on the error message I found about 6 responses that said I needed to have a primary key on my data table. I did and always have. so I gave up and explicitly wrote sql statments to save the table. The solution is below if anyone is interested.
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
    Dim row0 As Integer
    Dim col0 As Integer
    Dim cn As New ADODB.Connection
    Dim colNames(4) As String
    Dim storeValue As String
    colNames(1) = "leads"
    colNames(2) = "lags"
    colNames(3) = "lower_bound"
    colNames(4) = "upper_bound"
        With cn
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            .Open("C:\Users\Phred\Documents\PhredTek\AutoPreVis\variable property test.accdb")
        End With
        For row0 = 0 To DataGridView1.Rows.Count - 2
            For col0 = 1 To 4
                If IsDBNull(DataGridView1.Rows(row0).Cells(col0).Value) Then
                    storeValue = "Null"
                    storeValue = DataGridView1.Rows(row0).Cells(col0).Value
                End If
                sql = "update [TM000238 variables] set " & colNames(col0) & " = " & storeValue & _
                    " where var_name = """ & DataGridView1.Rows(row0).Cells(0).Value & """"
    Catch ex As Exception
        MsgBox(ex.Message & " row " & row0 & " col " & col0)
    End Try
End Sub

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web04 | 2.8.180111.1 | Last Updated 24 Oct 2012
Copyright © CodeProject, 1999-2018
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100