保存datagridview以在Visual Studio 2010 VB.net中进行访问 [英] Saving a datagridview to access in Visual Studio 2010 VB.net

问题描述

当我尝试使用以下代码(button2)将数据保存在DataGridView中以进行访问时，出现错误在传递带有已修改行的DataRow集合时，更新需要有效的UpdateCommand".我做错了什么?

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

推荐答案

我无法重建您的问题，但是我建议您使用.net

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

是否附加了绑定源?
如果确实在button2.click中尝试此操作，请单击
如果没有，您可以考虑增加一个.它们非常适合处理从用户端到数据库的数据检索和编辑.

我不太确定如何一次保存整个DS.

我将每个表的第一个项目拖到.net设计器模式下的表单中，它将自动附加数据集，表适配器和绑定到每个Access表的链接源.

这是我用于分别保存每个表的代码.

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

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

这篇关于保存datagridview以在Visual Studio 2010 VB.net中进行访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！