如何在datagridview中更改数据后更新访问数据库 [英] How do I update access database after making changes to the data in datagridview

问题描述

如何在datagridview中更改数据后更新Access数据库。 datagridview中的每一列都是通过代码加载的。

这是加载datagridview的代码。

 私人  Sub  LoadData（）
  Dim  DGVVerticalScroll = dgv1.Controls.OfType （  VScrollBar）.SingleOrDefault 
 
 使用 dgv1 
 .Columns .Clear（）
 .AutoGenerateColumns =  False  
 .DataSource = dt 
 .ClearSelection（）
 .AutoGenerateColumns = < span class =code-keyword> False  
 .AllowUserToAddRows =  False  
 .RowsDefaultCellStyle.BackColor = Color.PapayaWhip 
 .AlternatingRowsDefaultCellStyle.BackColor = Color.White 
 .RowTemplate.Height =  18  
 .Visible =  True  
 .Columns.Add（  Column0，  TransactionID）
。列（ 0 ）。Visible =  False  
 .Columns（ 0 ）。DataPropertyName =   TransactionsID 
 
  Dim  CreditCardID  As  新 DataGridViewComboBoxColumn（） 
 strSQL =   SELECT * FROM tblCreditCard按CardName订购 
  Me  .da = 新 OleDbDataAdapter（strSQL，cn）
 我 .myTable = 新 DataTable 
 我 .da.Fill（myTable）
 CreditCardID.DataSource = myTable 
 使用 CreditCardID 
 .DisplayMember =   CardName 
 .ValueMember =   CreditCardID 
 .HeaderText =  信用卡 
 .DataPropertyName =   CreditCardID 
 .MaxDropDownItems =  6  
 .FlatStyle = FlatStyle.Flat 
 结束 使用 
 .Columns.Add（Cred itCardID）
 .Columns（ 1 ）。宽度=  96  
 
  Dim  MerchantTypeID 作为 新 DataGridViewComboBoxColumn（） 
 strSQL =   SELECT * FROM tblMerchantType ORDER by MerchantType 
  Me  .da = 新 OleDbDataAdapter（strSQL，cn）
 我 .myTable = 新 DataTable 
 我 .da.Fill（myTable） 
 MerchantTypeID.DataSource = myTable 
 使用 MerchantTypeID 
 .DisplayMember =   MerchantType 
 .ValueMember =   MerchantTypeID 
 .HeaderText =  商家类型 
 .DataPropertyName =   MerchantTypeID 
 .MaxDropDownItems =  16  
 .FlatStyle = FlatStyle.Flat 
 结束 使用 
 .Columns.Add（MerchantTypeID）
 .Columns（ 2 ）。Name =   MerchantTypeID 
。列（ 2 ）。宽度=  120  
 
 .Columns.Add（< span class =code-string>  Column3， 商家）
 .Columns（ 3 ） .DataPropertyName =  商家 
 .Columns（ 3 ）。宽度=  100  
 
  Dim  transdate < span class =code-keyword> As   New  DataGridViewDateControl 
 transdate.HeaderText =  交易日期 
 transdate.DataPropertyName =   TransDate 
 .Columns.Add（transdate）
 .Columns（ 4 ）。Width =  90  
 .Columns（ 4 ）。DefaultCellStyle.Alignment = DataGridViewContentA lignment.MiddleRight 
 
  Dim  postdate  As  新 DataGridViewDateControl 
 postdate.HeaderText =  发布日期 
 postdate.DataPropertyName =   Postdate 
 .Columns.Add（postdate）
 .Columns（ 5 ）。宽度=  90  
 .Columns（ 5 ）。DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight 
 
 .Columns.Add（  Column6， 金额）
 .Columns（ 6 ）。宽度=  72  
 .Columns（ 6 ）。DataPropertyName =  金额 
 .Columns（ 6 ）。DefaultCellStyle.Format =   $＃，###。00 
 .Columns（ 6 ）。DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight 
 
  Dim 已协调作为 新 DataGridViewCheckBoxColumn 
 使用核对
 .HeaderText =  已对帐 
 .DataPropertyName =  已对帐 
 结束 使用 
 .Columns.Add（已对帐）
 .Columns（ 7 ）。Width =  60  
 .Visible =  True  
 结束 使用 
 
 结束  Sub  

我的尝试：



这是我尝试更新数据库之一。

 私有  Sub  BuildDataAdapter（）
  Dim  delete  As  新 OleDbCommand（ ）
  Dim  myupdate  As  新 OleDbCommand（  UPDATE tblTransactions SET TransactionsID = ?, CreditCardsID = ?, MerchantTypeID = ？，& _ 
   Merchant = ?, TransDate = ?, Postdate = ?, Amount = ?, Reconciled =？ & _ 
   WHERE TransactionsID =？，cn）
 
 myupdate.Parameters.Add（  @ TransactionsID，OleDbType。字符， 5 ，  TransactionsID ）。SourceVersion = DataRowVersion.Original 
 myupdate.Parameters.Add（  @ CreditCardID，OleDbType。字符， 5 ，  CreditCardID）。SourceVersion = DataRowVersion.Current 
 myupdate.Parameters.Add（  @ MerchantTypeID，OleDbType。字符， 5 ，  MerchantTypeID）。SourceVersion = DataRowVersion.Current 
 myupdate.Parameters.Add（  @ Merchant，OleDbType。 Char ， 50 ， 商家）。SourceVersion = DataRowVersion。当前
 myupdate.Parameters.Add（  @ Transdate，OleDbType。日期， 10 ，  Transdate）。SourceVersion = DataRowVersion.Current 
 myupdate.Parameters.Add（  @Postdate，OleDbType。日期， 10 ，  Postdate）。SourceVersion = DataRowVersion.Current 
 myupdate.Parameters.Add（  @ Amount，OleDbType.Currency， 10 ， < span class =code-string> Amount）。SourceVersion = DataRowVersion.Current 
 myupdate.Parameters.Add（  @ Reconciled，OleDbType。布尔， 1 ，< span class =code-string> 已协调）。SourceVersion = DataRowVersion.Current 
 
 da.UpdateCommand = myupdate 
 
 da.MissingSchemaAction = MissingSchemaAction.AddWithKey 
 
 结束  Sub  

这里是要更新的按钮点击事件

 da.Update（dt）

解决方案

＃，###。00
.Columns（ 6 ）。DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

Dim 已协调正如 新 DataGridViewCheckBoxColumn
已核对
.HeaderText = 已对帐
.DataPropertyName = 已对帐
结束 带
.Columns.Add（已对帐）
.Columns（ 7 ）。宽度= 60
.Visible = True
结束 使用

结束 Sub

我的尝试：



这是我尝试更新数据库之一。

 私有  Sub  BuildDataAdapter（）
  Dim  delete < span class =code-keyword> As  新 OleDbCommand（ ）
  Dim  myupdate  As  新 OleDbCommand（  UPDATE tblTransactions SET TransactionsID = ?, CreditCardsID = ?,， MerchantTypeID =？，& _ 
   Merchant = ?, TransDate = ?, Postdate = ?, Amount = ?, Reconciled =？ & _ 
   WHERE TransactionsID =？，cn）
 
 myupdate.Parameters.Add（  @ TransactionsID，OleDbType。字符， 5 ，  TransactionsID ）。SourceVersion = DataRowVersion.Original 
 myupdate.Parameters.Add（  @ CreditCardID，OleDbType。字符， 5 ，  CreditCardID）。SourceVersion = DataRowVersion.Current 
 myupdate.Parameters.Add（  @ MerchantTypeID，OleDbType。字符， 5 ，  MerchantTypeID）。SourceVersion = DataRowVersion.Current 
 myupdate.Parameters.Add（  @ Merchant，OleDbType。 Char ， 50 ， 商家）。SourceVersion = DataRowVersion。当前
 myupdate.Parameters.Add（  @ Transdate，OleDbType。日期， 10 ，  Transdate）。SourceVersion = DataRowVersion.Current 
 myupdate.Parameters.Add（  @Postdate，OleDbType。日期， 10 ，  Postdate）。SourceVersion = DataRowVersion.Current 
 myupdate.Parameters.Add（  @ Amount，OleDbType.Currency， 10 ， < span class =code-string> Amount）。SourceVersion = DataRowVersion.Current 
 myupdate.Parameters.Add（  @ Reconciled，OleDbType。布尔， 1 ，< span class =code-string> 已协调）。SourceVersion = DataRowVersion.Current 
 
 da.UpdateCommand = myupdate 
 
 da.MissingSchemaAction = MissingSchemaAction.AddWithKey 
 
 结束  Sub  

这里是要更新的按钮点击事件

 da.Update（dt）<使用实体框架访问MS Access：
 
 
 /> 
 
 
  c＃4.0  - 如何使用MS Access数据库的实体框架 -  Stack Overflow  [ ^ ] 
 
 
 
  VB.NET的Windows框架6用C＃，VB.NET，XML for Visual Studio 2015形成解决方案示例 [ ^ ] 

我弄清楚了。问题是使用相同的（da）DataAdapter for Combo box列和DataGridView加载。为每个组合框列提供一个唯一的适配器后，DataGridView按计划加载并保存所有更改。还删除了不需要的Private Sub BuildDataAdapter。

How do I update Access database after making changes to the data in datagridview. Each column in datagridview is load via code.
Here’s the code for loading the datagridview.

Private Sub LoadData()
        Dim DGVVerticalScroll = dgv1.Controls.OfType(Of VScrollBar).SingleOrDefault

        With dgv1
            .Columns.Clear()
            .AutoGenerateColumns = False
            .DataSource = dt
            .ClearSelection()
            .AutoGenerateColumns = False
            .AllowUserToAddRows = False
            .RowsDefaultCellStyle.BackColor = Color.PapayaWhip
            .AlternatingRowsDefaultCellStyle.BackColor = Color.White
            .RowTemplate.Height = 18
            .Visible = True
            .Columns.Add("Column0", "TransactionID")
            .Columns(0).Visible = False
            .Columns(0).DataPropertyName = "TransactionsID"

            Dim CreditCardID As New DataGridViewComboBoxColumn()
            strSQL = "SELECT * FROM tblCreditCard ORDER by CardName "
            Me.da = New OleDbDataAdapter(strSQL, cn)
            Me.myTable = New DataTable
            Me.da.Fill(myTable)
            CreditCardID.DataSource = myTable
            With CreditCardID
                .DisplayMember = "CardName"
                .ValueMember = "CreditCardID"
                .HeaderText = "Credit Card"
                .DataPropertyName = "CreditCardID"
                .MaxDropDownItems = 6
                .FlatStyle = FlatStyle.Flat
            End With
            .Columns.Add(CreditCardID)
            .Columns(1).Width = 96

            Dim MerchantTypeID As New DataGridViewComboBoxColumn()
            strSQL = "SELECT * FROM tblMerchantType ORDER by MerchantType"
            Me.da = New OleDbDataAdapter(strSQL, cn)
            Me.myTable = New DataTable
            Me.da.Fill(myTable)
            MerchantTypeID.DataSource = myTable
            With MerchantTypeID
                .DisplayMember = "MerchantType"
                .ValueMember = "MerchantTypeID"
                .HeaderText = "Merchant Type"
                .DataPropertyName = "MerchantTypeID"
                .MaxDropDownItems = 16
                .FlatStyle = FlatStyle.Flat
            End With
            .Columns.Add(MerchantTypeID)
            .Columns(2).Name = "MerchantTypeID"
            .Columns(2).Width = 120

            .Columns.Add("Column3", "Merchant")
            .Columns(3).DataPropertyName = "Merchant"
            .Columns(3).Width = 100

            Dim transdate As New DataGridViewDateControl
            transdate.HeaderText = "Transaction Date"
            transdate.DataPropertyName = "TransDate"
            .Columns.Add(transdate)
            .Columns(4).Width = 90
            .Columns(4).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

            Dim postdate As New DataGridViewDateControl
            postdate.HeaderText = "Posting Date"
            postdate.DataPropertyName = "Postdate"
            .Columns.Add(postdate)
            .Columns(5).Width = 90
            .Columns(5).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

            .Columns.Add("Column6", "Amount")
            .Columns(6).Width = 72
            .Columns(6).DataPropertyName = "Amount"
            .Columns(6).DefaultCellStyle.Format = "$#,###.00"
            .Columns(6).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

            Dim reconciled As New DataGridViewCheckBoxColumn
            With reconciled
                .HeaderText = "Reconciled"
                .DataPropertyName = "Reconciled"
            End With
            .Columns.Add(reconciled)
            .Columns(7).Width = 60
            .Visible = True
        End With

    End Sub

What I have tried:

Here’s the one of my tries for updating the database.

Private Sub BuildDataAdapter()
        Dim delete As New OleDbCommand("")
        Dim myupdate As New OleDbCommand("UPDATE tblTransactions SET TransactionsID = ?, CreditCardsID = ?, MerchantTypeID = ?, " & _
               " Merchant = ? , TransDate = ? ,Postdate = ? , Amount = ? , Reconciled = ?" & _
               " WHERE TransactionsID = ?", cn)

        myupdate.Parameters.Add("@TransactionsID", OleDbType.Char, 5, "TransactionsID").SourceVersion = DataRowVersion.Original
        myupdate.Parameters.Add("@CreditCardID", OleDbType.Char, 5, "CreditCardID").SourceVersion = DataRowVersion.Current
        myupdate.Parameters.Add("@MerchantTypeID", OleDbType.Char, 5, "MerchantTypeID").SourceVersion = DataRowVersion.Current
        myupdate.Parameters.Add("@Merchant", OleDbType.Char, 50, "Merchant").SourceVersion = DataRowVersion.Current
        myupdate.Parameters.Add("@Transdate", OleDbType.Date, 10, "Transdate").SourceVersion = DataRowVersion.Current
        myupdate.Parameters.Add("@Postdate", OleDbType.Date, 10, "Postdate").SourceVersion = DataRowVersion.Current
        myupdate.Parameters.Add("@Amount", OleDbType.Currency, 10, "Amount").SourceVersion = DataRowVersion.Current
        myupdate.Parameters.Add("@Reconciled", OleDbType.Boolean, 1, "Reconciled").SourceVersion = DataRowVersion.Current

        da.UpdateCommand = myupdate

        da.MissingSchemaAction = MissingSchemaAction.AddWithKey

    End Sub

And here’s the button click event to update

da.Update(dt)

解决方案

#,###.00" .Columns(6).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight Dim reconciled As New DataGridViewCheckBoxColumn With reconciled .HeaderText = "Reconciled" .DataPropertyName = "Reconciled" End With .Columns.Add(reconciled) .Columns(7).Width = 60 .Visible = True End With End Sub

What I have tried:

Here’s the one of my tries for updating the database.

Private Sub BuildDataAdapter()
        Dim delete As New OleDbCommand("")
        Dim myupdate As New OleDbCommand("UPDATE tblTransactions SET TransactionsID = ?, CreditCardsID = ?, MerchantTypeID = ?, " & _
               " Merchant = ? , TransDate = ? ,Postdate = ? , Amount = ? , Reconciled = ?" & _
               " WHERE TransactionsID = ?", cn)

        myupdate.Parameters.Add("@TransactionsID", OleDbType.Char, 5, "TransactionsID").SourceVersion = DataRowVersion.Original
        myupdate.Parameters.Add("@CreditCardID", OleDbType.Char, 5, "CreditCardID").SourceVersion = DataRowVersion.Current
        myupdate.Parameters.Add("@MerchantTypeID", OleDbType.Char, 5, "MerchantTypeID").SourceVersion = DataRowVersion.Current
        myupdate.Parameters.Add("@Merchant", OleDbType.Char, 50, "Merchant").SourceVersion = DataRowVersion.Current
        myupdate.Parameters.Add("@Transdate", OleDbType.Date, 10, "Transdate").SourceVersion = DataRowVersion.Current
        myupdate.Parameters.Add("@Postdate", OleDbType.Date, 10, "Postdate").SourceVersion = DataRowVersion.Current
        myupdate.Parameters.Add("@Amount", OleDbType.Currency, 10, "Amount").SourceVersion = DataRowVersion.Current
        myupdate.Parameters.Add("@Reconciled", OleDbType.Boolean, 1, "Reconciled").SourceVersion = DataRowVersion.Current

        da.UpdateCommand = myupdate

        da.MissingSchemaAction = MissingSchemaAction.AddWithKey

    End Sub

And here’s the button click event to update

da.Update(dt)

Use Entity Framework to "access" MS Access:

(Live a little)

c# 4.0 - How to use Entity framework for MS Access database - Stack Overflow[^]

Entity Framework 6 for VB.NET Windows forms solutions sample in C#, VB.NET, XML for Visual Studio 2015[^]

I figured it out. The problem was using the same (da) DataAdapter for Combo box columns and DataGridView loading. After giving each Combo box column a unique adapter the DataGridView loads as planned and all changes are saved. Also deleted the Private sub BuildDataAdapter it was not needed.

这篇关于如何在datagridview中更改数据后更新访问数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！