无法使用datagridview保存和更新mysql数据库 [英] Unable to save and update a mysql database using a datagridview

问题描述

我正在使用链接到MySQL数据库的winform。我已经更新了我的代码。我的表单有文本框，组合框，复选框和数据网格视图。最终目标是更新，添加新记录并将更改保存回MySQL数据库。 
 
当我点击datagridview中的一行（比如记录7）然后我更新记录7的数量文本框，然后我点击更新按钮，它会抛出一条异常消息： -  

抛出异常'  system.invalidoperationexception'（SelectCommand属性尚未初始化 

我的vba.net代码是：Private Sub Form1_Shown（sender As Object，e As EventArgs）Handles MyBase.Shown 
 daOrders = New MySqlDataAdapter（SELECT * FROM Orders，MysqlConn）
 daOrders.MissingSchemaAction = MissingSchemaAction.AddWithKey 
 Dim dsOrders As New DataSet 
 dtOrders = New DataTable（Orders）
 daOrders.Fill（dtOrders）
 dsOrders.Tables。添加（dtOrders）
 Dim cbOrders As New MySqlCommandBuilder（daOrders）
 daProducts = New MySqlDataAdapter（SELECT * FROM Products，MysqlConn）
 dtProducts = New DataTable（Products）
 daProducts.Fill（dtProducts）
 dsOrders.Tables.Add（dtProducts）
 cbOrders = New MySqlCommandBuilder（ daProducts）
 
 dtOrders.Columns（MetricID）。AutoIncrement = True 
 dtProducts.Columns（ProductID）。AutoIncrement = True 
 
 dtOrders.Columns（ 0）.AutoIncrementSeed = dtProducts.Rows.Cast（Of DataRow）.Max（Function（dr As DataRow）CInt（dr.Item（0）））+ 1 
 dtProducts.Columns（0）.AutoIncrementStep = 1 
 
 dtProducts.Columns（0）.AutoIncrementSeed = dtProducts.Rows.Cast（Of DataRow）.Max（Function（dr）CInt（dr.Item（0）））+ 1 
 dtProducts。 Columns（0）.AutoIncrementStep = 1 
 
 dsOrders.Relations.Add（New DataRelation（relation，dsOrders.Tables（Products）。Columns（ProductID），dsOrders.Tables（订单）。列（ProductID_fk）））
 
 ProductBindingSource = New BindingSource（dsOrders，Produc ts）
 
 CboProductID_fk.DisplayMember =Product
 CboProductID_fk.ValueMember =ProductID
 CboProductID_fk.DataSource = ProductBindingSource 
 
 OrderBindingSource = New BindingSource （ProductBindingSource，relation）
 
'将Product的外键绑定到组合框的SelectedValue
 Me.CboProductID_fk.DataBindings.Add（New System.Windows.Forms.Binding（SelectedValue） ，Me.OrderBindingSource，ProductID_fk，True））
 
'通过BindingSource将DataTable绑定到UI。 
 OrderBindingSource.DataSource = dtOrders 
 Me.OrderBindingNavigator.BindingSource = Me.OrderBindingSource 
 
 txtMetricID.DataBindings.Add（Text，Me.OrderBindingSource，MetricID）
 txtOrders.DataBindings.Add（Text，Me.OrderBindingSource，Orders）
 ChkCheckedStatus.DataBindings.Add（Text，Me.OrderBindingSource，CheckedStatus）
 txtOrderQuantity.DataBindings。添加（Text，Me.OrderBindingSource，Quantity）
 
'将DataGridView绑定到BindingSource 
'并从数据库加载数据。 
 
 OrderBindingSource.DataSource = OrderDataGridView.Rows 
 OrderBindingNavigator.BindingSource = OrderBindingSource 
 Me.OrderDataGridView.DataSource = Me.OrderBindingSource 
 GetOrderData（select * from Orders） 
 
'而不是为MoveFirst添加事件处理程序，MoveLast，MoveNext，MovePrevious 
'选择了这个。无论如何它都会开火
 AddHandler BindingNavigatorPositionItem.TextChanged，AddressOf bindingnavigator_Postionchanged 
 
 
'如果找不到钥匙，position = 1 
'你还可以尝试任何其他正确的事件
 OrderBindingSource.Position = OrderBindingSource.Find（MetricID，IIf（txtMetricID.Text =，0，txtMetricID.Text））
 End Sub 
 
 Private Sub UpdateOrders（）
 Dim conn As New MySqlConnection 
 conn.ConnectionString = 
server = localhost; Port = 3306; database = database; userid = root; password = password; persistsecurityinfo = True 
 Dim daOrders As MySqlDataAdapter = New MySqlDataAdapter（SELECT * From Orders，conn）
 Dim myBuilder = New MySqlCommandBuilder（daOrders）
 conn.Open（）
 daOrders.Fill（ dsOrders，Orders）
 daOrders.Update（dsOrders，Orders）
 MsgBox（Data Updated，MsgBoxStyle.OkOnly）
 
使用CboProductID_fk 
 .DisplayMember =Product
 .ValueMember =ProductID
 .DataSource = OrderBindingSource 
 End with 
 
 
 txtOrders.DataBindings.Clear（）
 ChkCheckedStatus.DataBindings.Clear（）
 txtOrderQuantity.DataBindings.Clear（）
 
 OrderBindingNavigator.BindingSource = OrderBindingSource 
 
 With Me 
 .txtOrders .DataBindings.Add（Text，OrderBindingSource，Orders，True，DataSourceUpdateMode.OnValidation，vbNullString）
 .ChkCheckedStatus.DataBindings.Add（CheckState，OrderBindingSource，CheckedStatus，True，DataSourceUpdateMode.OnValidation， CheckState.Unchecked）
 .txtOrderQuantity.DataBindings.Add（Text，OrderBindingSource，Quantity，True，DataSourceUpdateMode.OnValidation，vbNullString）
 End with 
 
 OrderBindingSource.EndEdit （）
 daOrders.Update（dtOrders）
 End Sub 
 Private Sub DgvOrders（）
 con = New MySqlConnection 
 con.ConnectionString = conString 
 Dim MySqldaOrders As New MySqlDataAdapter 
 Dim OrdersBindingSource As New BindingSource 
尝试
 con。 Open（）
 Dim queryOrders As String =select * from database.Orders
 cmd = New MySqlCommand（queryOrders，con）
 MySqldaOrders.Fill（dtOrders）
 OrdersBindingSource.DataSource = dtOrders 
 OrderDataGridView.DataSource = OrderBindingSource 
 MySqldaOrders.Update（dtOrders）
 
 con.Close（）
 
 Catch ex As Exception 
 
结束尝试
结束Sub 
 Private Sub BtnProductUpdate_Click（sender As Object，e As EventArgs）处理BtnProductUpdate.Click 
 
 UpdateOrders（）
 DgvOrders（） 
 
 
 End Sub 
 
 
 Private Sub BtnOrderSave_Click（sender As Object， e As EventArgs）处理BtnOrderSave.Click 
 Dim dsOrders As New DataSet 
 Me.Validate（）
 
 Me.OrderBindingSource.EndEdit（）
 
 Me .daOrders.Update（dsOrders，Orders）
 
 End Sub  -  

我遇到的另一个问题是如果我添加一条记录，使用绑定导航器新记录控件并填写新记录的详细信息并单击保存按钮，则会出现以下错误消息： -  

 System.InvalidOperationException：' 更新无法找到TableMapping ['Orders']或DataTable'Orders'。' 

我的尝试：



更新update事件中的dataadapters并显示错误。

解决方案

我得到了当我使用这个vb.net代码将文本框数据更新保存回MySQL时，我的问题得到了解决atabase： -

 进口 MySql 
  Imports  MySql.Data.MySqlClient 
  Imports  MySql.Data 
 
 公共 类 Form1 
 继承表单
 
 公共 ConnectionString 作为 字符串 =   
  Dim  conString 作为 字符串 =  < span class =code-string> Server = localhost; Port = 3306; Database = mydatabase; userid = root; password = mypassword; persist security info = True 
  Dim  con  As  MySqlConnection = 新 MySqlConnection（conString）
 
 
 私有  Sub  EndEditOnAllBindingSources（）
  Dim  BindingSourcesQuery = From bindingsources  In  我 .components.Components 
其中（ TypeOf  bindingsources 是 Windows.Forms.BindingSource）
 选择 bindingsources 
 
  For  每个 bindingSource  As  Windows.Forms.BindingSource 在 BindingSourcesQuery 
 bindingSource.EndEdit（）
 下一步 
 结束  Sub  
 
 
 私人  Sub  BtnOrderSave_Click（发件人作为 对象，e  As  EventArgs）句柄 BtnOrderSave.Click 
 
  Me  .EndEditOnAllBindingSources（）
 
 SaveOrders（）
 
 结束  Sub  
 
 私有  Sub  SaveOrders（可选消息作为 布尔 =  True ）
 
 con = 新 MySqlConnection 
 con .ConnectionString = conString 
  Dim  daOrders  As  新 MySqlDataAdapter（  select * from Orders，con）
  Dim  dsOrders  As  新 DataSet 
 
 如果 我 .Validate 那么 
  Me  .OrderBindingSource.EndEdit（）
  Me  .daOrders.Update（我 .dtOrders）
 dsOrders.EnforceConstraints =  False  
 其他 
 System.Windows.Forms.MessageBox.Show（ Me ， 验证_ 
发生错误。，
  保存，System.Windows.Forms.MessageBoxButtons.OK，
 System.Windows.Forms.MessageBoxIcon.Warning）
 结束 如果 
 
 尝试 
 dsOrders.EnforceConstraints =  True  
  Catch  e 作为系统。 Data.ConstraintException 
 ' 处理异常并返回。 
 Console.WriteLine（< span class =code-string> 类型{0}的异常发生。，
 e。 GetType （）。ToString（））
 结束 尝试 
 
 
 结束  Sub  
 
 
 私人 < span class =code-keyword> Sub  BtnOrderSave_Click（sender  As   Object ，e < span class =code-keyword> As  EventArgs）句柄 BtnOrderSave.Click 
 
 我 .EndEditOnAllBindingSources（）
 
 SaveOrders（）
 结束  Sub  
 
 私有  Sub  SaveOrders（可选消息作为 布尔 =  True ）
 
 con = 新 MySqlConnection 
 con.ConnectionString = conString 
  Dim  daOrders 作为 新 MySqlDataAdapter（  select * from Orders，con）
  Dim  dsOrders  As  新 DataSet 
 
 如果 我 .Validate 然后 
 我 .OrderBindingSource.EndEdit（）
  Me  .daOrders.Update（ Me  .dtOrders）
 dsOrders.EnforceConstraints = 错误 
 其他 
 System.Windows.Forms .MessageBox.Show（ Me ， 验证_ $ b发生了$ b错误。，
  保存，System.Windows .Forms.MessageBoxButtons.OK，
 System.Windows.Forms.MessageBoxIcon.Warning）
 结束 如果 
 
 尝试 
 dsOrders.EnforceConstraints =  True  
  Catch  e  As  System.Data.ConstraintException 
 ' 处理异常并返回。 
 Console.WriteLine（ 类型{0}的异常发生ed。，
 e。 GetType （）。ToString（））
 结束 尝试 
 
 
 结束  Sub  
 结束 类 

I am using a winform which is linked to a MySQL database. I have updated my code. My form has textboxes, a combobox, a checkbox and a datagridview. The end goal is to update, add new records and save changes back to MySQL database.

When I click on a row in the datagridview (say record 7) and I then make an update the quantity textbox for record 7, and I click the update button, it throws an exception message:-

exception thrown 'system.invalidoperationexception' in system.data.dll (the SelectCommand property has not been initilalize

My vba.net code is: Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles MyBase.Shown
daOrders = New MySqlDataAdapter("SELECT * FROM Orders", MysqlConn)
        daOrders.MissingSchemaAction = MissingSchemaAction.AddWithKey
        Dim dsOrders As New DataSet
        dtOrders = New DataTable("Orders")
        daOrders.Fill(dtOrders)
        dsOrders.Tables.Add(dtOrders)
        Dim cbOrders As New MySqlCommandBuilder(daOrders)
        daProducts = New MySqlDataAdapter("SELECT * FROM Products", MysqlConn)
        dtProducts = New DataTable("Products")
        daProducts.Fill(dtProducts)
        dsOrders.Tables.Add(dtProducts)
        cbOrders = New MySqlCommandBuilder(daProducts)

        dtOrders.Columns("MetricID").AutoIncrement = True
        dtProducts.Columns("ProductID").AutoIncrement = True

        dtOrders.Columns(0).AutoIncrementSeed = dtProducts.Rows.Cast(Of DataRow).Max(Function(dr As DataRow) CInt(dr.Item(0))) + 1
        dtProducts.Columns(0).AutoIncrementStep = 1

        dtProducts.Columns(0).AutoIncrementSeed = dtProducts.Rows.Cast(Of DataRow).Max(Function(dr) CInt(dr.Item(0))) + 1
        dtProducts.Columns(0).AutoIncrementStep = 1

        dsOrders.Relations.Add(New DataRelation("relation", dsOrders.Tables("Products").Columns("ProductID"), dsOrders.Tables("Orders").Columns("ProductID_fk")))

        ProductBindingSource = New BindingSource(dsOrders, "Products")

        CboProductID_fk.DisplayMember = "Product"
        CboProductID_fk.ValueMember = "ProductID"
        CboProductID_fk.DataSource = ProductBindingSource

        OrderBindingSource = New BindingSource(ProductBindingSource, "relation")

        'bind the Product's foreign key to the combobox's "SelectedValue"
        Me.CboProductID_fk.DataBindings.Add(New System.Windows.Forms.Binding("SelectedValue", Me.OrderBindingSource, "ProductID_fk", True))

        'Bind the DataTable to the UI via a BindingSource.
        OrderBindingSource.DataSource = dtOrders
        Me.OrderBindingNavigator.BindingSource = Me.OrderBindingSource

        txtMetricID.DataBindings.Add("Text", Me.OrderBindingSource, "MetricID")
        txtOrders.DataBindings.Add("Text", Me.OrderBindingSource, "Orders")
        ChkCheckedStatus.DataBindings.Add("Text", Me.OrderBindingSource, "CheckedStatus")
        txtOrderQuantity.DataBindings.Add("Text", Me.OrderBindingSource, "Quantity")

        ' Bind the DataGridView to the BindingSource
        ' and load the data from the database.

        OrderBindingSource.DataSource = OrderDataGridView.Rows
        OrderBindingNavigator.BindingSource = OrderBindingSource
        Me.OrderDataGridView.DataSource = Me.OrderBindingSource
        GetOrderData("select * from Orders")

        'instead of adding event handler for MoveFirst,MoveLast,MoveNext,MovePrevious
        'chose this one. it will fire anyway
        AddHandler BindingNavigatorPositionItem.TextChanged, AddressOf bindingnavigator_Postionchanged


  'if it didn't find the key, position = 1
        'you can also try any else proper event
OrderBindingSource.Position = OrderBindingSource.Find("MetricID", IIf(txtMetricID.Text = "", 0, txtMetricID.Text))
End Sub

Private Sub UpdateOrders()
        Dim conn As New MySqlConnection
        conn.ConnectionString =
       "server=localhost;Port=3306;database=database;userid=root;password=password;persistsecurityinfo=True"
        Dim daOrders As MySqlDataAdapter = New MySqlDataAdapter("SELECT * From Orders", conn)
        Dim myBuilder = New MySqlCommandBuilder(daOrders)
        conn.Open()
        daOrders.Fill(dsOrders, "Orders")
        daOrders.Update(dsOrders, "Orders")
        MsgBox("Data Updated", MsgBoxStyle.OkOnly)

        With CboProductID_fk
            .DisplayMember = "Product"
            .ValueMember = "ProductID"
            .DataSource = OrderBindingSource
        End With


        txtOrders.DataBindings.Clear()
        ChkCheckedStatus.DataBindings.Clear()
        txtOrderQuantity.DataBindings.Clear()

        OrderBindingNavigator.BindingSource = OrderBindingSource

        With Me
            .txtOrders.DataBindings.Add("Text", OrderBindingSource, "Orders", True, DataSourceUpdateMode.OnValidation, vbNullString)
            .ChkCheckedStatus.DataBindings.Add("CheckState", OrderBindingSource, "CheckedStatus", True, DataSourceUpdateMode.OnValidation, CheckState.Unchecked)
            .txtOrderQuantity.DataBindings.Add("Text", OrderBindingSource, "Quantity", True, DataSourceUpdateMode.OnValidation, vbNullString)
        End With

        OrderBindingSource.EndEdit()
        daOrders.Update(dtOrders)
End Sub
Private Sub DgvOrders()
        con = New MySqlConnection
        con.ConnectionString = conString
        Dim MySqldaOrders As New MySqlDataAdapter
        Dim OrdersBindingSource As New BindingSource
        Try
            con.Open()
            Dim queryOrders As String = "select * from database.Orders"
            cmd = New MySqlCommand(queryOrders, con)
            MySqldaOrders.Fill(dtOrders)
            OrdersBindingSource.DataSource = dtOrders
            OrderDataGridView.DataSource = OrderBindingSource
            MySqldaOrders.Update(dtOrders)

            con.Close()

        Catch ex As Exception

        End Try
    End Sub
Private Sub BtnProductUpdate_Click(sender As Object, e As EventArgs) Handles BtnProductUpdate.Click

        UpdateOrders()
        DgvOrders()

        
    End Sub


Private Sub BtnOrderSave_Click(sender As Object, e As EventArgs) Handles BtnOrderSave.Click
        Dim dsOrders As New DataSet
        Me.Validate()

        Me.OrderBindingSource.EndEdit()

        Me.daOrders.Update(dsOrders, "Orders")

End Sub-

Another problem which I am experiencing is if I add a record, using the binding navigator new record control and I fill in the details of the new record and click the save button, the following error message occurs: -

System.InvalidOperationException: 'Update unable to find TableMapping['Orders'] or DataTable 'Orders'.'

What I have tried:

updating the dataadapters in the update event and it shows an error.

解决方案

I got my issue resolved when I used this vb.net code to save textbox data updates back to the MySQL database: -

Imports MySql
Imports MySql.Data.MySqlClient
Imports MySql.Data

Public Class Form1
    Inherits Form

   Public ConnectionString As String = ""
    Dim conString As String = "Server=localhost;Port=3306;Database=mydatabase;userid=root;password=mypassword;persist security info=True"
    Dim con As MySqlConnection = New MySqlConnection(conString)


Private Sub EndEditOnAllBindingSources()
        Dim BindingSourcesQuery = From bindingsources In Me.components.Components
                                  Where (TypeOf bindingsources Is Windows.Forms.BindingSource)
                                  Select bindingsources

        For Each bindingSource As Windows.Forms.BindingSource In BindingSourcesQuery
            bindingSource.EndEdit()
        Next
    End Sub


Private Sub BtnOrderSave_Click(sender As Object, e As EventArgs) Handles BtnOrderSave.Click

        Me.EndEditOnAllBindingSources()

        SaveOrders()

    End Sub

    Private Sub SaveOrders(Optional messages As Boolean = True)

        con = New MySqlConnection
        con.ConnectionString = conString
        Dim daOrders As New MySqlDataAdapter("select * from Orders", con)
        Dim dsOrders As New DataSet

        If Me.Validate Then
            Me.OrderBindingSource.EndEdit()
            Me.daOrders.Update(Me.dtOrders)
            dsOrders.EnforceConstraints = False
        Else
            System.Windows.Forms.MessageBox.Show(Me, "Validation _
                                              errors occurred.",
              "Save", System.Windows.Forms.MessageBoxButtons.OK,
              System.Windows.Forms.MessageBoxIcon.Warning)
        End If

        Try
            dsOrders.EnforceConstraints = True
        Catch e As System.Data.ConstraintException
            ' Process exception and return.
            Console.WriteLine("Exception of type {0} occurred.",
            e.GetType().ToString())
        End Try


    End Sub


Private Sub BtnOrderSave_Click(sender As Object, e As EventArgs) Handles BtnOrderSave.Click

        Me.EndEditOnAllBindingSources()

       SaveOrders()
  End Sub

    Private Sub SaveOrders(Optional messages As Boolean = True)

        con = New MySqlConnection
        con.ConnectionString = conString
        Dim daOrders As New MySqlDataAdapter("select * from Orders", con)
        Dim dsOrders As New DataSet

        If Me.Validate Then
            Me.OrderBindingSource.EndEdit()
            Me.daOrders.Update(Me.dtOrders)
            dsOrders.EnforceConstraints = False
        Else
            System.Windows.Forms.MessageBox.Show(Me, "Validation _
                                              errors occurred.",
              "Save", System.Windows.Forms.MessageBoxButtons.OK,
              System.Windows.Forms.MessageBoxIcon.Warning)
        End If

        Try
            dsOrders.EnforceConstraints = True
        Catch e As System.Data.ConstraintException
            ' Process exception and return.
            Console.WriteLine("Exception of type {0} occurred.",
            e.GetType().ToString())
        End Try


    End Sub
End Class

这篇关于无法使用datagridview保存和更新mysql数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！