Saving a datagridview to access in Visual Studio 2010 VB.net

Question

0.00/5 (No votes)

See more:

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?

HTML

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

Fred Andres

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Lacy00 · Answer 1 · 2012-10-23T20:24:00

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.

VB

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

RavonX · Answer 2 · 2012-10-23T07:08:00

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.

VB

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

Fred Andres · Answer 3 · 2012-10-23T12:22:00

VB

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

Saving a datagridview to access in Visual Studio 2010 VB.net

3 solutions

Solution 3

Solution 1

Solution 2

Add your solution here

Preview 0