wire_jp Ответов: 1

Невозможно сохранить и обновить базу данных mysql с помощью datagridview

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'.'


Что я уже пробовал:

обновление dataadapters в событии update и оно показывает ошибку.

Gerry Schmitz

Вы хотите, чтобы мы угадали, "где" в этой куче кода произошла ошибка?

У вас есть несколько вариантов выбора. На каком из них нам следует сосредоточиться?

wire_jp

Привет,

Первая ошибка показана ниже:

[Код]
исключение, вызванное "system.invalidoperationexception" в system.data.dll (свойство SelectCommand не было инициализировано перед вызовом Fill vba.net [/код]


Эта первая ошибка произошла с этим разделом vba.net код: -
[Код]
Dim MySqldaOrders Как Новый MySqlDataAdapter
Dim OrdersBindingSource Как Новый BindingSource
Попробуй
против.Открыть()
Dim queryOrders As String = "select * from database.Заказы"
cmd = New MySqlCommand(queryOrders, con)
Mysqlda.Заполнение(dtOrders)[/code]


Вторая ошибка ниже: -

[Код]
Система.InvalidOperationException: 'Update не удалось найти табличное отображение['Orders'] или DataTable 'Orders'.'[/code]


и это второе сообщение об ошибке произошло в этой строке кода:-
[Код]
Меня.daOrders.Обновление(болезни, "заказы")[/код]

1 Ответов

Рейтинг:
1

wire_jp

Я решил свою проблему, когда использовал это vb.net код для сохранения обновлений данных текстового поля обратно в базу данных MySQL: -

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