[02/03] Updating from sql server to mysql

[Oliver1]

Hi All,

As a bit of background we have a local sql server database and a remote mysql server. I need to update all the changes made in the sql server database to the my sql database. I select all the sql server records as follows.

Private Function getupcompdata() As Boolean
 
 
        getupcompdata = True
        Try
 
 
            dsweb.Clear()
  
            sqlcon.Open()
 
            Dim spupcomp As New SqlClient.SqlCommand("spupcomp", sqlcon, Nothing)
            spupcomp.CommandType = CommandType.StoredProcedure
            Dim dasqlserver As New SqlDataAdapter
            dasqlserver.SelectCommand = spupcomp
            dasqlserver.Fill(dsweb, "company")
            sqlcon.Close()
            Me.DGWEB.DataSource = dsweb
            Me.DGWEB.DataMember = "company"
            If Me.BindingContext(dsweb, "company").Count() = 0 Then
                getupcompdata = False
            End If
 
        Catch
            getupcompdata = False
        End Try
    End Function

How can I update this to the mysql database, I really don't want to have to iterate through each record, generate an update comand run said update comand and then move on to the next record. Have tried toying around with the below, but suspect what I'm trying to do is impossible.

Private Sub upsqlsql()
        Try
 
            Dim dasql As MySqlDataAdapter = New MySqlDataAdapter
            Dim cmd As MySqlCommand
            Dim parm As MySqlParameter
 
            Dim spupcomp As New MySqlCommand("select * from company", MySqlConnection, Nothing)
            dasql.SelectCommand = spupcomp
 
 
            cmd = New MySqlCommand("UPDATE Company SET  CompanyName = @CompanyName " & _
                                 "WHERE CompanyID = @oldCompanyID", MySqlConnection)
 
                        cmd.Parameters.Add("@CompanyName", SqlDbType.VarChar, 50, "CompanyName")
 
            parm = cmd.Parameters.Add("@oldCompanyID", SqlDbType.Int, 8, "CompanyID")
            parm.SourceVersion = DataRowVersion.Original
 
            dasql.UpdateCommand = cmd
            MySqlConnection.Open()
 
            dasql.Update(dsweb, "company")
            MySqlConnection.Close()
 
        Catch ex As Exception
            Me.TextBox1.Text = ex.ToString
        End Try
 
 
    End Sub

Basically any help or pointers would be great.