Unable to save and update a mysql database using a datagridview

Question

1.00/5 (1 vote)

See more:

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:-

VB

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: -

VB

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.

Posted 17-Feb-19 16:35pm

wire_jp

Updated 19-Feb-19 15:52pm

Add a Solution

Comments

[no name] 18-Feb-19 12:22pm

Do you want us to guess "where" in that pile of code the error occurred?

You have more than one SELECT. Which one should we focus on?

wire_jp 19-Feb-19 17:51pm

Hello,

The first error is shown below:

[Code]
exception thrown 'system.invalidoperationexception' in system.data.dll (the SelectCommand property has not been initilalized before calling Fill vba.net [/code]

This first error occurred with this section of vba.net code: -
[Code]
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)[/code]

The second error below: -

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

and this second error message occurred at this line of code:-
[Code]
Me.daOrders.Update(dsOrders, "Orders")[/code]

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

wire_jp · Answer 1 · 2019-02-19T15:53:00

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

VB

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