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
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:
What I have tried:
updating the dataadapters in the update event and it shows an error.