无法使用datagridview保存和更新mysql数据库

本文介绍了无法使用datagridview保存和更新mysql数据库的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我正在使用链接到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并显示错误。解决方案 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 initilalizeMy vba.net code is: Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles MyBase.ShowndaOrders = 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 eventOrderBindingSource.Position = OrderBindingSource.Find("MetricID", IIf(txtMetricID.Text = "", 0, txtMetricID.Text))End SubPrivate 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 SubPrivate 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 SubPrivate Sub BtnProductUpdate_Click(sender As Object, e As EventArgs) Handles BtnProductUpdate.Click UpdateOrders() DgvOrders() End SubPrivate 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. 解决方案 这篇关于无法使用datagridview保存和更新mysql数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！