如何从绑定的datagridview更新数据库 [英] How to update database from bound datagridview

问题描述

我正在尝试从绑定的datagridview更新数据库，但我运气不好。我想首先在datagridview中显示数据然后在更改datagridview之后，我想点击一个更新按钮来更新数据库。我已经创建了将数据库绑定到datagridview的代码，并且工作正常。我遇到的问题是如何在以后更新数据库。我创建了一个命令构建器来自动生成updatecommand。如果我以编程方式更新数据表，然后调用dataadapter更新，它可以正常工作。但是，如果我尝试在我的按钮中调用dataadapter更新，它会给我错误：

I am trying to update a database from a bound datagridview but I am not having very good luck. I want to first display the data in the datagridview then after making changes to the datagridview, I want to hit an update button to update the database. I have created the code that binds the database to the datagridview and that works fine. What I am having problems with is how to update the database afterwards. I have created a commandbuilder to automatically generate the updatecommand. If I update the datatable programmatically and then call the dataadapter update, it works fine. However, if I try to call the dataadapter update in my button, it gives me the error:

The ConnectionString property has not been initialized.

I认为我很接近，但我无法弄清楚为什么这不起作用。我是否需要在按钮中重新初始化某些内容？



我尝试了什么：

I think I am close, but I just can't figure out why this isn't working. Do I need to reinitialize something in my button?

What I have tried:

Dim con As System.Data.OleDb.OleDbConnection = New OleDb.OleDbConnection(connectionString)
    Dim da As OleDbDataAdapter
    Dim ds As DataSet
    Dim dt As DataTable


    Private Sub FormPlannedProductionTimes_Shown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shown

        Me.DateTimePickerStart.Value = Today

    End Sub


    Private Sub RefreshDGVMonday()

        con = New OleDb.OleDbConnection(connectionString)
        da = New OleDbDataAdapter
        ds = New DataSet
        dt = New DataTable

        Dim DGV As DataGridView = Me.DGVMonday


        Try

            'Create the select command
            da.SelectCommand = New OleDbCommand(Nothing, con)

           
            da.SelectCommand.CommandText = "SELECT MACHINE AS MACH, OEEDATE, SHIFT1 AS S1, SHIFT2 AS S2, SHIFT3 AS S3 " & _
                                            "FROM PLANNEDPRODUCTIONTIMES " & _
                                            "WHERE OEEDATE = TO_DATE('" & DateTimePickerStart.Value & "', 'MM/DD/YYYY HH:MI:SS AM') " & _
                                            "ORDER BY MACH"

            'Create a command builder to auto generate the update command
            Dim cb As New OleDbCommandBuilder(da)
            da.InsertCommand = cb.GetInsertCommand

            'Clear all columns
            DGV.Columns.Clear()

            'Fill the DataAdapter
            da.Fill(ds, "Monday")
            dt = ds.Tables("Monday")

            'Programatically change the values in the datatable
            'then update the data adapter
            'This works fine, however, if I remove these 2 lines
            'and call theupdate in my button below, I get an error
            dt.Rows(0)!s1 = 1
            da.Update(dt)

            'Set the datasource
            DGV.DataSource = dt

            'Set the width
            DGV.Columns("MACH").Width = 50
            DGV.Columns("OEEDATE").Width = 70
            DGV.Columns("S1").Width = 30
            DGV.Columns("S2").Width = 30
            DGV.Columns("S3").Width = 30

         

        Catch ex As Exception
            MsgBox("Could not refresh the production time data because: " & vbNewLine & vbNewLine & _
                  ex.Message & vbNewLine & _
                  "Contact Admin for more assistance.", MsgBoxStyle.Exclamation, "Error")

        Finally
            con.Close()
            con.Dispose()
            da.Dispose()

        End Try


    End Sub

    Private Sub DateTimePickerStart_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePickerStart.ValueChanged
        RefreshDGVMonday()
    End Sub



    Private Sub BtnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnUpdate.Click

        'This gives me an error
        da.Update(dt)

    End Sub

推荐答案

我自己想通了。我在检索数据后错误地在我的finally子句中处理我的数据库连接，因此我删除了该代码。我想这就是我得到连接字符串错误的原因。


我现在稍微修改了我的更新按钮代码，如果工作正常的话。

I figured it out myself. I was mistakenly disposing my database connection in my finally clause after retrieving the data so I removed that code. I guess that is the reason I was getting the connection string error.

I then modified my update button code slightly on now if works fine.

Private Sub BtnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnUpdate.Click


      If con.State = ConnectionState.Closed Then con.Open()
       Dim RowsUpdated As Integer = 0

       Try

           RowsUpdated = da.Update(dt) 'Update the database

       Catch ex As Exception

           MsgBox(ex.Message)

       Finally
           con.Close()
       End Try

       MsgBox("RowsUpdated = " & RowsUpdated)


   End Sub

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