Saves updates to MySql database

  • (2 Pages)
  • +
  • 1
  • 2

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

#1 wire_jp   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 25
  • Joined: 10-January 19

Saves updates to MySql database

Posted 10 January 2019 - 11:11 AM

Hello All,

I am new to vb.net programming. My problem is that I am unable to save updates in the fields of the Form1.vb in visual studio back to MySQL database, once I click the save button in the Person tab of a tab menu. I have a MySQL database which is linked to a visual studio enterprise. In visual studio, I am using a tab menu with different tabs. In the first tab contains the Countries with the following fields: - CountryID, CountryCode and CountryName. The next tab contains the Person with the following fields: PersonID, CountryID_fk, PersonName. My vb.net code is shown below: -

Imports MySql
Imports MySql.Data.MySqlClient

Public Class Form1

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim Countrydt As New DataTable

        Dim MysqlConn = New MySqlConnection
        MysqlConn.ConnectionString =
           "server=localhost;Port=3306;database=database;userid=root;password=password;persistsecurityinfo=True"
        Using adapter As MySqlDataAdapter = New MySqlDataAdapter("SELECT * From Countries", MysqlConn)
            adapter.Fill(Countrydt)
        End Using

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

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

        Dim dtPerson As New DataTable

        Dim MysqlConn2 = New MySqlConnection
        Dim daPerson As New MySqlDataAdapter
        Dim daCountries As New MySqlDataAdapter
        Dim dtCountries As New DataTable
        MysqlConn2.ConnectionString =
           "server=localhost;Port=3306;database=database;userid=root;password=password;persistsecurityinfo=True"
        daPerson = New MySqlDataAdapter("SELECT * FROM Person", MysqlConn2)
        Dim dsPerson As New DataSet
        dtPerson = New DataTable("Person")
        daPerson.Fill(dtPerson)
        dsPerson.Tables.Add(dtPerson)
        Dim cbPerson As New MySqlCommandBuilder(daPerson)
        daCountries = New MySqlDataAdapter("SELECT * FROM Countries", MysqlConn2)
        dtCountries = New DataTable("Countries")
        daCountries.Fill(dtCountries)
        dsPerson.Tables.Add(dtCountries)
        cbPerson = New MySqlCommandBuilder(daCountries)

        dtPerson.Columns("PersonID").AutoIncrement = True
        dtCountries.Columns("CountryID").AutoIncrement = True

        dtPerson.Columns(0).AutoIncrementSeed = dtCountries.Rows.Cast(Of DataRow).Max(Function(dr As DataRow) CInt(dr.Item(0))) + 1
        dtCountries.Columns(0).AutoIncrementStep = 1

        dtCountries.Columns(0).AutoIncrementSeed = dtCountries.Rows.Cast(Of DataRow).Max(Function(dr) CInt(dr.Item(0))) + 1
        dtCountries.Columns(0).AutoIncrementStep = 1

        dsPerson.Relations.Add(New DataRelation("relation", dsPerson.Tables("Countries").Columns("CountryID"), dsPerson.Tables("Person").Columns("CountryID_fkey")))

        CountryBindingSource = New BindingSource(dsPerson, "Countries")

        cboCountryID_fkey.DisplayMember = "CountryName"
        cboCountryID_fkey.ValueMember = "CountryID"
        cboCountryID_fkey.DataSource = CountryBindingSource

        PersonBindingSource = New BindingSource(CountryBindingSource, "relation")

        'bind the Countries' foreign key to the combobox's "SelectedValue"
        cboCountryID_fkey.DataBindings.Add(New Binding("SelectedValue", PersonBindingSource, "CountryID_fkey", True))

        'Bind the DataTable to the UI via a BindingSource.
        PersonBindingSource.DataSource = dtPerson
        Me.PersonBindingNavigator.BindingSource = Me.PersonBindingSource

        Me.txtPersonID.DataBindings.Add("Text", Me.PersonBindingSource, "PersonID")
        Me.txtPersonName.DataBindings.Add("Text", Me.PersonBindingSource, "PersonName")

'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))
        PersonBindingSource.Position = PersonBindingSource.Find("PersonID", IIf(txtPersonID.Text = "", 0, txtPersonID.Text))
    End Sub

Private Sub btnSavePerson_Click(sender As Object, e As EventArgs) Handles btnSavePerson.Click


        Dim dtPerson As New DataTable
        Dim daPerson As New MySqlDataAdapter

        Try
            Validate()
            PersonBindingSource.EndEdit()
            daPerson.Update(dtPerson)
            MsgBox("Update successful")

        Catch ex As Exception
            MsgBox("Update failed")
        End Try


    End Sub
End Class




Thank you in advance for your help.

Is This A Good Question/Topic? 0
  • +

Replies To: Saves updates to MySql database

#2 Sheepings   User is offline

  • Senior Programmer
  • member icon

Reputation: 195
  • View blog
  • Posts: 1,089
  • Joined: 05-December 13

Re: Saves updates to MySql database

Posted 10 January 2019 - 11:34 AM

And????

Are we going to play guessing games next

You've failed to explain what the problem is...
Was This Post Helpful? 0
  • +
  • -

#3 wire_jp   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 25
  • Joined: 10-January 19

Re: Saves updates to MySql database

Posted 10 January 2019 - 11:45 AM

Sorry, the issue occurs when I make any update changes to the data in the Person tab and I then clicked the Save button in the Person tab, a message dialog pops up with the message "file saved successfully". However I check the MySQL database, no change in the data will occur.

Thanks.
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw   User is online

  • awks lol ffs
  • member icon

Reputation: 6695
  • View blog
  • Posts: 27,482
  • Joined: 12-December 12

Re: Saves updates to MySql database

Posted 10 January 2019 - 12:06 PM

Do you mean "Update successfull"?
Was This Post Helpful? 0
  • +
  • -

#5 wire_jp   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 25
  • Joined: 10-January 19

Re: Saves updates to MySql database

Posted 10 January 2019 - 12:16 PM

Hi Andrewsw,


Yes, I mean "Update successfull". But the issue is no updated data appear in the data of the MySQL database.


Thanks.
Was This Post Helpful? 0
  • +
  • -

#6 IronRazer   User is offline

  • Custom Control Freak
  • member icon

Reputation: 1532
  • View blog
  • Posts: 3,859
  • Joined: 01-February 13

Re: Saves updates to MySql database

Posted 10 January 2019 - 01:14 PM

The first thing I would do for at least the time being, is to move the code out of the Form Load event sub and put it into the Form Shown event. The Form Load event tends to hide a lot of exception and you would never even know one occurred. This is a common problem with the Load event. After you get everything working correctly you could move it back to the Load event.

Also, I see you are creating a new DataTable (dtPerson), and a new MySqlDataAdapter (daPerson) in the Save Person button sub, which means the DataTable is blank, and there is no data in it to update. See the msdn document for the MySqlDataAdapter Class which says the following about the MySqlDataAdapter.Update(DataTable) method...

"Calls the respective INSERT, UPDATE, or DELETE statements for each inserted, updated, or deleted row in the specified DataTable."

It also shows a very simple example of how to use the Update(DataTable) method about half way down the page. You need to Fill the DataTable, Add/Remove/Modify the data in the DataTable as needed, then call the Update method and pass the (filled & modified) DataTable to the MySqlDataAdapter Update method. I believe you might ??? also need to use the same MySqlDataAdapter instance as you created and used to fill the DataTable. I am not a database guy but, these are some things you can look at. 8)
Was This Post Helpful? 0
  • +
  • -

#7 wire_jp   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 25
  • Joined: 10-January 19

Re: Saves updates to MySql database

Posted 10 January 2019 - 02:53 PM

Hello,

I changed the vb.net for the Save button as follows: -

    Private Sub btnSavePerson_Click(sender As Object, e As EventArgs) Handles btnSavePerson.Click


         Dim daPerson As New MySqlDataAdapter
	 Dim dataSet As DataSet = New DataSet("Person")

        Try
            Validate()
            PersonBindingSource.EndEdit()
            daPerson.Update(dataSet)
            MsgBox("Update successful")

        Catch ex As Exception
            MsgBox("Update failed")
        End Try


    End Sub




However, when I make changes to the data in the fields of the "Person" tab and press the Save button, a pop-up dialog message box appears with the message "Update failed"
Was This Post Helpful? 0
  • +
  • -

#8 IronRazer   User is offline

  • Custom Control Freak
  • member icon

Reputation: 1532
  • View blog
  • Posts: 3,859
  • Joined: 01-February 13

Re: Saves updates to MySql database

Posted 10 January 2019 - 04:23 PM

Again, you are just creating a new empty DataSet and giving it a name of 'Person'. I'm not sure why you have switched from using a DataTable to using a DataSet in the new code but, as I said... you need to be working with the same DataTable or DataSet that you filled with data in the Load event, not a new blank one.

Another thing I said was, you might need to use the same instance of the MySqlDataAdapter that you used to fill the DataTable or DataSet, the one you created in the Load event.

However, as I said, I am no database guy so, other members that are more knowledgeable about MySql and using databases might have some better info on doing all of this.
Was This Post Helpful? 0
  • +
  • -

#9 wire_jp   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 25
  • Joined: 10-January 19

Re: Saves updates to MySql database

Posted 13 January 2019 - 07:02 PM

Hi,

I appreciate all of the help which you have given me so far. I have moved the code from the Form Load event to the Form Show event and it generated 17 messages which I have resolved. However, it shows 2 warnings which I have been unable to fix and the warning messages are shown below: -

Severity	Code	Description	Project	File	Line	Suppression State
Warning		The referenced component 'MySql.Data.Entity.EF6' could not be found.	Persons			
Warning		Could not resolve this reference. Could not locate the assembly "MySql.Data.Entity.EF6, Version=6.9.10.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d, processorArchitecture=MSIL". Check to make sure the assembly exists on disk. If this reference is required by your code, you may get compilation errors.	Persons			




I have attached my updated vb.net code:-

  Imports MySql
	Imports MySql.Data.MySqlClient
	 
	Public Class Form1
	      Dim conn As New MySqlConnection(My.Settings.Persons)
    Dim MysqlConn As MySqlConnection
    Dim cbPerson As New MySqlCommandBuilder(daPersons)
    Dim dsPersons As New DataSet
    Dim dtCountries As New DataTable
    Dim dtPersons As New DataTable
    Dim daCountries As New MySqlDataAdapter
    Dim daPersons As New MySqlDataAdapter
    Private BindingSource As New BindingSource
    Dim i = 0
	Private Sub Form1_Show(sender As Object, e As EventArgs) Handles MyBase.Load
	
	 
	        Dim MysqlConn As MySqlConnection = NewMethod()
	        MysqlConn.ConnectionString =
	           "server=localhost;Port=3306;database=database;userid=root;password=password;persistsecurityinfo=True"
	        Using adapter As MySqlDataAdapter = New MySqlDataAdapter("SELECT * From Countries", MysqlConn)
	            adapter.Fill(Countrydt)
	        End Using
	 
	        'Bind the DataTable to the UI via a BindingSource.
	        Me.CountryBindingSource.DataSource = Countrydt
	        Me.CountryBindingNavigator.BindingSource = Me.CountryBindingSource
	 
	        Me.txtCountryID.DataBindings.Add("Text", Me.CountryBindingSource, "CountryID")
	        Me.txtCountryCode.DataBindings.Add("Text", Me.CountryBindingSource, "CountryCode")
	        Me.txtCountryName.DataBindings.Add("Text", Me.CountryBindingSource, "CountryName")
	 
	        Dim dtPerson As New DataTable
	 
	        Dim MysqlConn2 = New MySqlConnection
	        Dim daPerson As New MySqlDataAdapter
	        Dim daCountries As New MySqlDataAdapter
	        Dim dtCountries As New DataTable
	        MysqlConn2.ConnectionString =
	           "server=localhost;Port=3306;database=database;userid=root;password=password;persistsecurityinfo=True"
	        daPerson = New MySqlDataAdapter("SELECT * FROM Person", MysqlConn2)
	        Dim dsPerson As New DataSet
	        dtPerson = New DataTable("Person")
	        daPerson.Fill(dtPerson)
	        dsPerson.Tables.Add(dtPerson)
	        Dim cbPerson As New MySqlCommandBuilder(daPerson)
	        daCountries = New MySqlDataAdapter("SELECT * FROM Countries", MysqlConn2)
	        dtCountries = New DataTable("Countries")
	        daCountries.Fill(dtCountries)
	        dsPerson.Tables.Add(dtCountries)
	        cbPerson = New MySqlCommandBuilder(daCountries)
	 
	        dtPerson.Columns("PersonID").AutoIncrement = True
	        dtCountries.Columns("CountryID").AutoIncrement = True
	 
	        dtPerson.Columns(0).AutoIncrementSeed = dtCountries.Rows.Cast(Of DataRow).Max(Function(dr As DataRow) CInt(dr.Item(0))) + 1
	        dtCountries.Columns(0).AutoIncrementStep = 1
	 
	        dtCountries.Columns(0).AutoIncrementSeed = dtCountries.Rows.Cast(Of DataRow).Max(Function(dr) CInt(dr.Item(0))) + 1
	        dtCountries.Columns(0).AutoIncrementStep = 1
	 
	        dsPerson.Relations.Add(New DataRelation("relation", dsPerson.Tables("Countries").Columns("CountryID"), dsPerson.Tables("Person").Columns("CountryID_fkey")))
	 
	        CountryBindingSource = New BindingSource(dsPerson, "Countries")
	 
	        cboCountryID_fkey.DisplayMember = "CountryName"
	        cboCountryID_fkey.ValueMember = "CountryID"
	        cboCountryID_fkey.DataSource = CountryBindingSource
	 
	        PersonBindingSource = New BindingSource(CountryBindingSource, "relation")
	 
	        'bind the Countries' foreign key to the combobox's "SelectedValue"
	        cboCountryID_fkey.DataBindings.Add(New Binding("SelectedValue", PersonBindingSource, "CountryID_fkey", True))
	 
	        'Bind the DataTable to the UI via a BindingSource.
	        PersonBindingSource.DataSource = dtPerson
	        Me.PersonBindingNavigator.BindingSource = Me.PersonBindingSource
	 
	        Me.txtPersonID.DataBindings.Add("Text", Me.PersonBindingSource, "PersonID")
	        Me.txtPersonName.DataBindings.Add("Text", Me.PersonBindingSource, "PersonName")
	 
	'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))
	        PersonBindingSource.Position = PersonBindingSource.Find("PersonID", IIf(txtPersonID.Text = "", 0, txtPersonID.Text))
	    End Sub
	 
	Private Sub btnSavePerson_Click(sender As Object, e As EventArgs) Handles btnSavePerson.Click
	 
  If i = -1 Then
            i = 0
        End If

        Try
            dtPerson.Rows(i).Item(0) = txtPersonID.Text.ToString
            dtPerson.Rows(i).Item(1) = cboPersonID_fky.Text.ToString
            dtPerson.Rows(i).Item(2) = txtPersonName.Text.ToString	 
     PersonBindingSource.EndEdit()
            daPersons.Update(dsPersons, "Persons")
            MsgBox("Update successful")

        Catch ex As Exception
            MsgBox("Update failed")
        End Try	 
	 
	    End Sub
	End Class






When I run & build the solution and then I updated the data in the fields of the "Growers" tab, a message dialog box pops with the message saying "Update failed.

Afterwards, I noticed that two exceptions occurs as shown in Events Window: -

Activated	Event	Time	Duration	Thread
	Exception thrown: 'System.IndexOutOfRangeException' in System.Data.dll ("There is no row at position 0.") Exception thrown: 'System.IndexOutOfRangeException' in System.Data.dll ("There is no row at position 0.") Hyperlink: Activate Historical Debugging	25.72s		[26196] Main Thread 

Activated	Event	Time	Duration	Thread
	Exception caught: 'System.IndexOutOfRangeException' in System.Data.dll ("There is no row at position 0.") Exception caught: 'System.IndexOutOfRangeException' in System.Data.dll ("There is no row at position 0.") Hyperlink: Activate Historical Debugging	25.72s		[26196] Main Thread 



Was This Post Helpful? 0
  • +
  • -

#10 andrewsw   User is online

  • awks lol ffs
  • member icon

Reputation: 6695
  • View blog
  • Posts: 27,482
  • Joined: 12-December 12

Re: Saves updates to MySql database

Posted 14 January 2019 - 01:55 AM

persistsecurityinfo should have spaces in it.

"Update failed" is your own custom message, display the Message from the ex Exception, or examine its InnerException(s).
Was This Post Helpful? 0
  • +
  • -

#11 wire_jp   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 25
  • Joined: 10-January 19

Re: Saves updates to MySql database

Posted 14 January 2019 - 03:55 AM

Hi Andrewsw,

Good catch! I made the change.

Kind regards.

I was wondering why my vb.net code do not save updates made to the data fields of the Persons tab back to the MySQL database.
Was This Post Helpful? 0
  • +
  • -

#12 andrewsw   User is online

  • awks lol ffs
  • member icon

Reputation: 6695
  • View blog
  • Posts: 27,482
  • Joined: 12-December 12

Re: Saves updates to MySql database

Posted 14 January 2019 - 05:51 AM

and you only need one connection string.
Was This Post Helpful? 0
  • +
  • -

#13 wire_jp   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 25
  • Joined: 10-January 19

Re: Saves updates to MySql database

Posted 15 January 2019 - 03:13 AM

View Postandrewsw, on 14 January 2019 - 05:51 AM, said:

and you only need one connection string.


Thanks
Was This Post Helpful? 0
  • +
  • -

#14 wire_jp   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 25
  • Joined: 10-January 19

Re: Saves updates to MySql database

Posted 16 January 2019 - 04:40 PM

Hi andrewsw,

I found one of your tutorials which has a snippet of vb.net code for saving updates back to the MySQL database: -


Exploring DataGridView (ComboBox, Checkbox, Increment, BindingSource)


So I modified this snippet of vb code for saving updates to the MySQL database as follows: -

   Private Sub BtnSavePersons_Click(sender As Object, e As EventArgs) Handles BtnSavePersons.Click
        SaveAll()
    End Sub

    Private Sub SaveAll(Optional messages As Boolean = True)
        Try
            PersonBindingSource.EndEdit()
            If dsPerson.HasChanges Then
                daPerson.Update(dsPerson.Tables("Persons"))
                If messages Then
                    MessageBox.Show("Database updated.", "Save")
                End If
            Else
                If messages Then
                    MessageBox.Show("Nothing to update.", "Save")
                End If
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Data Exception")
            Debug.Print(ex.Message)
        End Try
    End Sub




When I make changes to the data Persons tab in visual studio and press save, it will save the changes sporadically. By this term, I mean that if I click the save button, I get a message "Nothing to update" and if I check the MySQL database, there will be no updates. If I press the save button again, I get a message "Database updated" and If I refresh and check the MySQL database, the changes will be updated. If the field is originally blank and I enter a new data into the field and I press the Save button, the message will say "Database updated" and when I check the MySQL database, the data will be updated. Any thoughts on why the MySQL database is not being updated on each occasion when I press the Save button, once a change is made?


Thanks.
Was This Post Helpful? 0
  • +
  • -

#15 andrewsw   User is online

  • awks lol ffs
  • member icon

Reputation: 6695
  • View blog
  • Posts: 27,482
  • Joined: 12-December 12

Re: Saves updates to MySql database

Posted 17 January 2019 - 02:13 AM

Sorry, I haven't looked at any of this material in an age in any depth. Maybe someone else can assist you.

I would set a breakpoint and debug the code, probably trying to examine the RowState before and after the edit. I would want to know what happens before and after EndEdit.

It could be a number of things I suppose:

  • Maybe another related binding source is conflicting
  • Or a control event is interfering
  • Or the control is not correctly bound
  • The type of control could be an issue; that is, is the behaviour different for a textbox or combobox edit.

Good luck.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2