Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET DataGridView
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
        connection.Open()
        dataadapter.Fill(ds, "[TM000238 variables]")
        aTable = ds.Tables("[TM000238 variables]")
        MsgBox(aTable.TableName)
        connection.Close()
        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
        connection.Open()
        dataadapter.Update(ds, "[TM000238 variables]")
        connection.Close()
    End Sub
End Class
Posted 23-Oct-12 6:33am
Rate this: bad
good
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
  Permalink  
Comments
Fred Andres at 24-Oct-12 9:41am
   
Thanks Lacy00
Lacy00 at 24-Oct-12 22:51pm
   
tell me whether it helps you, if you meet some problems?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

do you have your binding source attached?
if you do try this in button2.click
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
        Try
            Me.Validate()
            Me.dataBindingSource.EndEdit()
            Me.dataTableAdapter.Update(me.myWorksDS.DataTable1)
        Catch ex As Exception
            MsgBox(ex.Message & " - Failed update in form1button2click")
        End Try
End Sub
  Permalink  
v4
Comments
Fred Andres at 23-Oct-12 13:14pm
   
Thanks. How do I attach a binding source?
Fred Andres at 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
good
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"
        Try
            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"
                    Else
                        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 & """"
                    cn.Execute(sql)
                Next
            Next
            cn.Close()
        Catch ex As Exception
            MsgBox(ex.Message & " row " & row0 & " col " & col0)
        End Try
    End Sub
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 210
1 Richard MacCutchan 100
2 kbrandwijk 90
3 ProgramFOX 80
4 Mukesh Bhagat 65
0 Sergey Alexandrovich Kryukov 9,050
1 OriginalGriff 8,151
2 CPallini 2,613
3 Richard MacCutchan 2,221
4 Abhinav S 1,928


Advertise | Privacy | Mobile
Web03 | 2.8.140827.1 | Last Updated 24 Oct 2012
Copyright © CodeProject, 1999-2014
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