[RESOLVED] Inserting Dataset to MySQL

[thebuffalo]

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.

    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

[jmcilhinney]

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.

[thebuffalo]

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.

    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?

[jmcilhinney]

Why are you calling Update twice?

[thebuffalo]

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.

[jmcilhinney]

Um, yes you are. Did you look at you 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.

[thebuffalo]

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.

[Jenner]

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?

[thebuffalo]

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

[jmcilhinney]

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.

[thebuffalo]

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

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

[thebuffalo]

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

[jmcilhinney]

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.

[thebuffalo]

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:

Dim commands As MySqlCommandBuilder 
commands.GetUInsertCommand()

And would I GetInsertCommand or Update?

[Jenner]

Examples of loading and saving data:

    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

[thebuffalo]

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!