VS 2010 [RESOLVED] Inserting Dataset to MySQL-VBForums
Results 1 to 16 of 16

Thread: [RESOLVED] Inserting Dataset to MySQL

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Jan 2012
    Location
    Florida
    Posts
    255

    Resolved [RESOLVED] Inserting Dataset to MySQL

    I know this is simple, but for some reason... this isnt working for me

    Im just simply trying to take my whole dataset and put it into my premade mysql tables.

    Code:
        Dim sqlConn As New MySqlConnection("server=192.168.0.109;User Id=****;password=****;database=taxcert")
        Dim taxDS As New DataSet("Tax")
        Dim commands As MySqlCommandBuilder
    
        Private Sub btnStartInput_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStartInput.Click
            Call inputorders()
        End Sub
        Public Sub inputorders()
            Dim ordersadapt As New MySqlDataAdapter("SELECT * FROM Orders", sqlConn)
            ordersadapt.InsertCommand = New MySqlCommand("INSERT INTO Orders (*) VALUES *", sqlConn)
            ordersadapt.Update(taxDS, "Orders")
        End Sub

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    87,639

    Re: Inserting Dataset to MySQL

    First things first, the whole point of using a command builder is to build the action commands (INSERT, UPDATE, DELETE) for you, so you need decide whether you're going to build the InsertCommand yourself or not. Either provide the SELECT statement and let the command builder build the InsertCommand based on that or else don't use a command builder, don't provide the SELECT statement and create the InsertCommand yourself.

    If you go with the second option then you have to create a valid INSERT statement. There are no wildcards in INSERT statements. You have to provide all the values and, unless you provide a value for every column in the same order they appear in the table definition, you have to provide all the columns too. I suggest that you follow the CodeBank link in my signature and check out my Retrieving & Saving Data thread. One of the code examples shows how to insert multiple records into a database using a DataTable and data adapter. It shows how to build an appropriate INSERT statement with a parameter for each column.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Jan 2012
    Location
    Florida
    Posts
    255

    Re: Inserting Dataset to MySQL

    I'm trying to avoid the full INSERT statement because my table has about 150+ columns, as for not using it and just the SELECT and Update command, I can't get this to work either.

    Code:
        Public Sub inputorders()
            Dim ordersadapt As New MySqlDataAdapter("SELECT * FROM Orders", sqlConn)
            ordersadapt.Update(taxDS, "Orders")
            MsgBox(ordersadapt.Update(taxDS, "Orders"))
    
        End Sub
    the MsgBox shows "0" ? So its not importing any rows?
    Last edited by thebuffalo; Feb 3rd, 2012 at 10:04 AM.

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    87,639

    Re: Inserting Dataset to MySQL

    Why are you calling Update twice?

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Jan 2012
    Location
    Florida
    Posts
    255

    Re: Inserting Dataset to MySQL

    Quote Originally Posted by jmcilhinney View Post
    Why are you calling Update twice?
    I'm not, i was using the msgbox to see the changes of the update, which didn't work.

  6. #6
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    87,639

    Re: Inserting Dataset to MySQL

    Um, yes you are. Did you look at you code?
    Code:
        Public Sub inputorders()
            Dim ordersadapt As New MySqlDataAdapter("SELECT * FROM Orders", sqlConn)
            ordersadapt.Update(taxDS, "Orders")
            MsgBox(ordersadapt.Update(taxDS, "Orders"))
    
        End Sub
    I've highlighted the first call in red and the second call in blue. You are displaying the result of the second call but not the result of the first call. You should be calling Update once and once only and, if you want to display the result of that first call, display the result of that first call.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Jan 2012
    Location
    Florida
    Posts
    255

    Re: Inserting Dataset to MySQL

    So using the msgbox to show the results calls it too? I didn't know that, either way I still cant get this to work for some reason.

  8. #8
    PowerPoster Jenner's Avatar
    Join Date
    Jan 2008
    Location
    Mentor, OH
    Posts
    3,444

    Re: Inserting Dataset to MySQL

    Yes, every time you use Update, it "resolves" it. That actually works for everything... if you call myString.Length for example, it processes the string length EVERY time you call it. If you need to use the string length multiple times, it's best to set it to a variable and from that point on, call the variable since it doesn't process it every time.

    Does taxDS have the same schema as your "Orders" table; i.e. was it generated from a similar data adapter and then edited?
    My CodeBank Submissions: TETRIS using VB.NET2010 and XNA4.0, Strong Encryption Class, Hardware ID Information Class, Generic .NET Data Provider Class, Lambda Function Example, Lat/Long to UTM Conversion Class, Audio Class using BASS.DLL

    Remember to RATE the people who helped you and mark your forum RESOLVED when you're done!

    "Two things are infinite: the universe and human stupidity; and I'm not sure about the universe. "
    - Albert Einstein

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Jan 2012
    Location
    Florida
    Posts
    255

    Re: Inserting Dataset to MySQL

    Yeah taxDS was generated by the dataadapter i used to pull the "Orders" table

  10. #10
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    87,639

    Re: Inserting Dataset to MySQL

    I've already told you what to do. Call Update once. If you want to display the result then display the result of that one call. The critical point is whether that result is zero or not.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  11. #11

    Thread Starter
    Addicted Member
    Join Date
    Jan 2012
    Location
    Florida
    Posts
    255

    Re: Inserting Dataset to MySQL

    I only called it once and it still doesn't affect the database

    My current SQL table is empty, i dont know if this affects anything but here is my code again

    Code:
    Imports System.Data.OleDb
    Imports MySql.Data.MySqlClient
    Public Class Backup
    
    
        Dim accConn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Aj\Desktop\TaxCert_be.accdb")
        Dim sqlConn As New MySqlConnection("server=192.168.0.109;User Id=ar***n;password=t****;database=taxcert")
        Dim ordersadapter As New OleDbDataAdapter("SELECT * FROM Orders Where Status = 'Waiting Payment' OR Status = 'Cancelled'", accConn)
        Dim taxDS As New DataSet("Tax")
        Dim commands As MySqlCommandBuilder
    
    
        Private Sub Backup_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    
    
        End Sub
        Public Sub loadevents()
            Dim eventsadapter As New OleDbDataAdapter("SELECT * FROM Events INNER JOIN Orders On Events.Control_Number = Orders.Control_Number WHERE Orders.Status = 'Waiting Payment' OR Orders.Status = 'Cancelled'", accConn)
            eventsadapter.Fill(taxDS, "Events")
        End Sub
        Public Sub loadfnf()
            Dim fnfadapter As New OleDbDataAdapter("SELECT * FROM [FNF Taxes] INNER JOIN Orders On [FNF Taxes].ControlNumber = Orders.Control_Number WHERE Orders.Status = 'Waiting Payment' OR Orders.Status = 'Cancelled'", accConn)
            fnfadapter.Fill(taxDS, "FNFTaxes")
        End Sub
        Public Sub inputorders()
            Dim ordersadapt As New MySqlDataAdapter("SELECT * FROM Orders", sqlConn)
            ordersadapt.Update(taxDS, "Orders")
           
    
        End Sub
    
    
        Private Sub btnFillData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFillData.Click
            Try
                ordersadapter.Fill(taxDS, "Orders")
                Call loadevents()
                Call loadfnf()
            Catch er As Exception
            End Try
            If taxDS.Tables("Events").Rows.Count > 10 And taxDS.Tables("FNFTaxes").Rows.Count > 10 Then _
                    MsgBox("Ready for Input.", MsgBoxStyle.OkOnly) _
            Else MsgBox("Fail.", MsgBoxStyle.OkOnly)
        End Sub
    
        Private Sub btnStartInput_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStartInput.Click
            Call inputorders()
        End Sub
    End Class
    Last edited by thebuffalo; Feb 7th, 2012 at 01:39 PM.

  12. #12

    Thread Starter
    Addicted Member
    Join Date
    Jan 2012
    Location
    Florida
    Posts
    255

    Re: Inserting Dataset to MySQL

    Bump.. why isn't my update command working?

  13. #13
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    87,639

    Re: Inserting Dataset to MySQL

    Consider this. Let's say that you have a DataAdapter. You call Fill on that to retrieve records into a DataTable. Now, if you were to call Update on that same DataAdapter and pass that same DataTable, what would you expect to happen? Would you expect all the records to be inserted into the database? Of course not, because they are not new records.

    The same applies if you use a different DataAdapter. None of the rows in the DataTable are new so none of them will be inserted. The DataAdapter does NOT look at the database to determine whether the records are new; it looks at the DataTable. The rows in the DataTable need to be flagged as new in order to be inserted when you call Update. That means that their RowState needs to be Added, but in your case it is Unchanged.

    When you call Fill on a DataAdapter, it first retrieves all the data and adds rows to the DataTable. Those rows all have their RowState set to Added, as do all rows just added to the DataTable. These rows do not represent new data though, so the adapter calls AcceptChanges on the DataTable. That sets all RowStates to Unchanged, to indicate that the DataTable is currently in the same state as the database. As you then add, edit and delete records, those rows will have a RowState of Added, Modified and Deleted. It's those rows that will be acted upon when you call Update.

    So, what you need is for AcceptChanges not to be called when you call Fill so that all rows have a RowState of Added, indicating that they are new rows, so that they can be inserted when you call Update. To do that, you set the AcceptChangesDuringFill property of the first DataAdapter to False.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  14. #14

    Thread Starter
    Addicted Member
    Join Date
    Jan 2012
    Location
    Florida
    Posts
    255

    Re: Inserting Dataset to MySQL

    Okay that worked for getting it to run the update but, I get an exception saying "Update requires a valid InsertCommand when passed DataRow collection with new rows.". So in the end I still have to make a InsertCommand with all of the column names and values?

    Thanks for the help

    Can I use the MysqlCommandBuilder? if so, how does this statement work:
    Code:
    Dim commands As MySqlCommandBuilder 
    commands.GetUInsertCommand()
    And would I GetInsertCommand or Update?
    Last edited by thebuffalo; Feb 9th, 2012 at 08:17 AM.

  15. #15
    PowerPoster Jenner's Avatar
    Join Date
    Jan 2008
    Location
    Mentor, OH
    Posts
    3,444

    Re: Inserting Dataset to MySQL

    Examples of loading and saving data:

    Code:
        Private ds As New DataSet
    
        Private Sub LoadData()
            Using mcon As New MySqlConnection("connectionString goes here")
                Dim da As New MySqlDataAdapter("SELECT * FROM MyTable", mcon)
                mcon.Open()
    
                da.Fill(ds)
                mcon.Close()
            End Using
        End Sub
    
        Private Sub SaveData()
            Using mcon As New MySqlConnection("connectionString goes here")
                Dim da As New MySqlDataAdapter("SELECT * FROM MyTable", mcon)
                Dim cb As New MySqlCommandBuilder(da)
                mcon.Open()
    
                da.Update(ds)
                mcon.Close()
            End Using
        End Sub
    My CodeBank Submissions: TETRIS using VB.NET2010 and XNA4.0, Strong Encryption Class, Hardware ID Information Class, Generic .NET Data Provider Class, Lambda Function Example, Lat/Long to UTM Conversion Class, Audio Class using BASS.DLL

    Remember to RATE the people who helped you and mark your forum RESOLVED when you're done!

    "Two things are infinite: the universe and human stupidity; and I'm not sure about the universe. "
    - Albert Einstein

  16. #16

    Thread Starter
    Addicted Member
    Join Date
    Jan 2012
    Location
    Florida
    Posts
    255

    Re: Inserting Dataset to MySQL

    Wooooo! Thanks to the command builder syntax you just gave me I got the fill working to mysql. Now I jut need to make a progress for for the input and make the load call for the other 2 tables, Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.