从Datagrid MDI更新/保存信息到MySQL数据库

I've got a bit of an odd one, I've been looking online but cannot find anything specific to my issue. Hopefully someone could help me.

我有点奇怪,我一直在网上看,但找不到任何特定的问题。希望有人可以帮助我。

I have a VB.Net 2012 application that reads information from MySQL. The reading is fine and it populates correctly but I am having trouble working out how to save changes back.

我有一个从MySQL读取信息的VB.Net 2012应用程序。读数很好,它填充正确,但我无法弄清楚如何保存更改。

The information is displayed from a DataTable into a DataGridView. The application is an MDI application and at anygiven point there could be more than one TableEditor Form Open as a Child.

信息从DataTable显示到DataGridView中。该应用程序是一个MDI应用程序,在任何一点上,可能有多个TableEditor表单作为子项打开。

This is my information loading function:

这是我的信息加载功能:

    Public Function get_SQL(ByVal SQLquery As String) As DataTable
    Try
        Dim myCommand As New MySqlCommand
        Dim myAdapter As New MySqlDataAdapter
        Dim myData As New DataTable
        Dim MysqlConn As New MySqlConnection

        MysqlConn.ConnectionString = "server=" & sqlServer & ";" _
            & "user id=" & sqlUsername & ";" _
            & "password=" & sqlPassword & ";" _
            & "database=" & sqlDatabase

        MysqlConn.Open()
        myCommand.Connection = MysqlConn
        myCommand.CommandText = SQLquery
        myAdapter.SelectCommand = myCommand
        myAdapter.Fill(myData)
        MysqlConn.Close()
        MysqlConn.Dispose()
        Return (myData)
    Catch ex As Exception
        Main.lblStatus.Text = "SQL Connection Error"
        errMsg = ex.ToString
        errForm = "SQL Connection"
        errWrite()
    End Try
End Function

And the information is loaded into the child form on load like this:

并且信息在加载时加载到子表单中,如下所示:

    Private Sub SQLeditor_Load(sender As Object, e As EventArgs) Handles Me.Load
    ToolStrip1.Visible = False
    Select Case sqlclick
        Case Is = "Office Details"
            SQLdata = get_SQL(qryOffice)
        Case Is = "School Details"
            SQLdata = get_SQL(qrySchools)
        Case Is = "ICT Staff"
            SQLdata = get_SQL(qryITstaff)
        Case Else
            ToolStrip1.Visible = True
    End Select
    dgvEditor.DataSource = SQLdata
End Sub

SQLdata is a global variable "Public SQLdata As DataTable"

SQLdata是一个全局变量“Public SQLdata As DataTable”

So I want to create a new Function that will save any changes back to the SQL database. As I am using the same global variable to populate a single form with multiple instances that can exist simultaneously I need to work out how to pass the information from the DataGridView or something along those lines and I'm stuck.

所以我想创建一个新的函数,将任何更改保存回SQL数据库。因为我使用相同的全局变量来填充具有可以同时存在的多个实例的单个表单,所以我需要弄清楚如何从DataGridView传递信息或者沿着那些行传递信息并且我被卡住了。

Any help is much appreciated.

任何帮助深表感谢。

1 个解决方案

#1

I managed to find a solution in the end and thought I'd post it incase anyone else comes across the same issue.

我设法最终找到了一个解决方案,并认为我会发布它,因为任何人都会遇到同样的问题。

I eventually settled on only having a single editable window open at a time using code like this:

我最终决定只使用这样的代码打开一个可编辑的窗口:

Public sqlEditForm As SQLeditor
If issql = True Then
            If sqlEditForm Is Nothing OrElse sqlEditForm.IsDisposed Then
                sqlEditForm = New SQLeditor
                sqlEditForm.MdiParent = Main
                sqlEditForm.Show()
            Else
                Dim reply As DialogResult
                reply = MsgBox("An existing edit window is already active, do you want to close the existing instance and create a new one?" & vbCrLf & "Any unsaved changes will be lost?", MsgBoxStyle.YesNoCancel, "Existing Window Detected")
                Select Case reply
                    Case Is = vbYes
                        sqlEditForm.Close()
                        sqlEditForm = New SQLeditor
                        sqlEditForm.MdiParent = Main
                        sqlEditForm.Show()
                    Case Is = vbNo
                        sqlEditForm.Show()
                    Case Is = vbCancel

                End Select
            End If

And then just saving a single instance of the data from the data adapter.

然后只需从数据适配器保存单个数据实例。

    Public Sub save_SQL()
    Try
        Dim myAdapter As New MySqlDataAdapter
        Dim MysqlConn As New MySqlConnection
        Dim cmdb = New MySqlCommandBuilder(myAdapter)

        MysqlConn.ConnectionString = "server=" & sqlServer & ";" _
            & "user id=" & sqlUsername & ";" _
            & "password=" & sqlPassword & ";" _
            & "database=" & sqlDatabase

        MysqlConn.Open()

        Select Case sqlclick
            Case Is = "Office Details"
                myAdapter.SelectCommand = New MySqlCommand(qryOffice, MysqlConn)
            Case Is = "School Details"
                myAdapter.SelectCommand = New MySqlCommand(qrySchools, MysqlConn)
            Case Is = "ICT Staff"
                myAdapter.SelectCommand = New MySqlCommand(qryITstaff, MysqlConn)
            Case Is = "SQL Editor"
                myAdapter.SelectCommand = New MySqlCommand(qrycustom, MysqlConn)
            Case Else
        End Select
        myAdapter.Update(SQLdata)

        MysqlConn.Close()
        MysqlConn.Dispose()
    Catch ex As Exception
        Main.lblStatus.Text = "SQL Connection Error"
        errMsg = ex.ToString
        errForm = "SQL Connection"
        errWrite()
    End Try
End Sub

It shouldn't be that hard to adapt the same system into a multi-MDI model but I don't think I have the need for it.

将同一系统调整为多MDI模型应该不难,但我认为我不需要它。

#1