Saves updates to MySql database

  • (2 Pages)
  • +
  • 1
  • 2

19 Replies - 964 Views - Last Post: 29 January 2019 - 09:22 AM Rate Topic: -----

#16 Sheepings   User is offline

  • Senior Programmer
  • member icon

Reputation: 201
  • View blog
  • Posts: 1,122
  • Joined: 05-December 13

Re: Saves updates to MySql database

Posted 17 January 2019 - 07:51 AM

In order for EndEdit to work, you need to have pending changes in order for your if conditions to be met. My question is; how is your data been set, edited or changed before this button is clicked? Also, note; by default booleans are false. And you should only be setting them to true if there are pending changes to be made. So this ::
Private Sub SaveAll(Optional messages As Boolean = True)

Makes this redundant ::
If messages Then

Was This Post Helpful? 0
  • +
  • -

#17 andrewsw   User is online

  • Stealth IT
  • member icon

Reputation: 6737
  • View blog
  • Posts: 27,741
  • Joined: 12-December 12

Re: Saves updates to MySql database

Posted 17 January 2019 - 08:32 AM

That boolean is an optional argument. If it isn't provided then there will be no messages displayed in the UI. It is there as a simple way to turn off the messages if they aren't needed.
Was This Post Helpful? 0
  • +
  • -

#18 Sheepings   User is offline

  • Senior Programmer
  • member icon

Reputation: 201
  • View blog
  • Posts: 1,122
  • Joined: 05-December 13

Re: Saves updates to MySql database

Posted 17 January 2019 - 08:51 AM

Yea, I guessed as much. Since I don't know the original snipped and the context it was posted; and how it should have been used, I won't comment further on it. But for its usage above, my comment still stands as thus my question regarding how the editable Object is being set before the button executes...
Was This Post Helpful? 0
  • +
  • -

#19 wire_jp   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 43
  • Joined: 10-January 19

Re: Saves updates to MySql database

Posted 26 January 2019 - 07:22 PM

I changed the code for the Persons' save button as shown below: -

  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 BtnSavePersons_Click(sender As Object, e As EventArgs) Handles BtnSavePersons.Click
        Me.EndEditOnAllBindingSources()
        SavePersons()
    End Sub


    Private Sub SavePersons(Optional messages As Boolean = True)

        If Me.Validate Then
            Me.PersonBindingSource.EndEdit()
            Me.daPersons.Update(Me.dsPersons.Tables("Persons"))
            dsPersons.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

    End Sub



If I edit and update the data in the fields of the Persons' tab and press the Save button in the Persons' tab, no data is saved to the MySQL database. However, if I navigate to the next record, by pressing the "Next" icon in the Binding Navigator and then press the Save button, the updated data is now saved in the MySQL database. I do not know how to fix this problem. I assume that the problem has to do with setting the focus to stay on the current record and then save the changes. Any help will be greatly appreciated.

My next problem is regarding the Countries table (the parent table) which is linked to the Persons Table (i.e. the child table) by the Countries ID (foreign key). The Country Table contains three fields: CountryID, Country Code, and CountryName. CountryID is set to Autonumber, not null, and Auto Increment in the MySQL database. I want to make changes in the Countries Table or add new records in the Countries, but I am unable to save the updates or add new records. The vb.net code is is shown below:-
Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles MyBase.Shown

        Dim MysqlConn As MySqlConnection = NewMethod()
        MysqlConn.ConnectionString =
"server=localhost;Port=3306;database=database;userid=root;password=password;persist security info=True"
        daCountries = New MySqlDataAdapter("SELECT * From Countries", MysqlConn)
        daCountries.Fill(dtCountries)
        dsCountries.Tables.Add(dtCountries)
        cbCountries = New MySqlCommandBuilder(daCountries)

        dtCountries.Columns("CountryID").AutoIncrement = True
        dtCountries.Columns(0).AutoIncrementStep = 1

        'Bind the DataTable to the UI via a BindingSource.
        CountryBindingSource.DataSource = dtCountries
        CountryBindingNavigator.BindingSource = Me.CountryBindingSource

        txtCountryID.DataBindings.Add("Text", CountryBindingSource, "CountryID")
        txtCountryCode.DataBindings.Add("Text", CountryBindingSource, "CountryCode")
        txtCountryName.DataBindings.Add("Text", CountryBindingSource, "CountryName")

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

  Private Sub BtnSaveCountries_Click(sender As Object, e As EventArgs) Handles BtnSaveCountries.Click
        
        SaveCountries()
    End Sub

    Private Sub SaveCountries(Optional messages As Boolean = True)


        If Me.ValidateChildren Then
            Me.CountryBindingSource.Position = i
            Me.CountryBindingSource.EndEdit()
            Me.PersonBindingSource.EndEdit()
            Me.daCountries.Update(Me.dsCountries.Tables("Countries"))
        Else
            System.Windows.Forms.MessageBox.Show(Me, "Validation _
                                              errors occurred.",
              "Save", System.Windows.Forms.MessageBoxButtons.OK,
              System.Windows.Forms.MessageBoxIcon.Warning)
        End If

    End Sub




If I build a solution, I try to add a new record, I noticed that the Country ID shows "0" in the field (instead of auto-incrementing to the next number in the sequence). When I add a new record and I press the save button, I receive the following error message at the point - Me.daCountries.Update(Me.dsCountries.Tables("Countries")): -

Exception Unhandled System.ArgumentNullException: 'Value cannot be null.
Parameter name: dataTable'



Any help will be greatly appreciated.
Was This Post Helpful? 0
  • +
  • -

#20 wire_jp   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 43
  • Joined: 10-January 19

Re: Saves updates to MySql database

Posted 29 January 2019 - 09:22 AM

In order to fix the issue with the
Exception Unhandled System.ArgumentNullException: 'Value cannot be null.
Parameter name: dataTable'
when I make changes or try to add a new record in the parent table and click the save button, I found a Microsoft document guide called "Adding Existing Constraints to a DataSet" Adding Existing Constraints to a DataSet//) which explained this setting up of the MissingSchemaAction property of the adapter to AddWithKey.

I added the following vb.net code under the Form Show event and it fixed the problem:-

 Dim dtCountries As DataTable = New DataTable()
        daCountries.MissingSchemaAction = MissingSchemaAction.AddWithKey
        daCountries.Fill(dtCountries)


Yes, the problem is now resolved.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2