Insert a table to mysql from access data base using vb.net

[Logic_Brown]

I found this peaice of code to import a Access database table to sql server

Private Sub btnImport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImport.Click

    Dim fileName As String = ""

    Dim ofd As New OpenFileDialog
    If ofd.ShowDialog = Windows.Forms.DialogResult.OK Then
        fileName = ofd.FileName
        PerformImportToSql(fileName)
    End If
End Sub

Private Sub PerformImportToSql(ByVal Filename As String)
    Dim table As DataTable = New DataTable
    Dim accConnection As New OleDb.OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0; DataSource=" & Filename & ";User Id=admin; Password=;")
    Dim sqlConnection As New SqlClient.SqlConnection("Data Source=yourServer; Initial Catalog=yourDatabase; User Id=yourUsername; Password=yourPassword;")

    Try

        'Import the Access data
        accConnection.Open()

        Dim accDataAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM <tablename>", accConnection)
        accDataAdapter.Fill(table)
        accConnection.Close()

        'Export to MS SQL
        sqlConnection.Open()
        Dim sqlDataAdapter As New SqlClient.SqlDataAdapter("SELECT * FROM <tablename>", sqlConnection)
        Dim sqlCommandBuilder As New SqlClient.SqlCommandBuilder(sqlDataAdapter)
        sqlDataAdapter.InsertCommand = sqlCommandBuilder.GetInsertCommand()
        sqlDataAdapter.UpdateCommand = sqlCommandBuilder.GetUpdateCommand()
        sqlDataAdapter.DeleteCommand = sqlCommandBuilder.GetDeleteCommand()
        sqlDataAdapter.Update(table)
        sqlConnection.Close()
    Catch ex As Exception
        If accConnection.State = ConnectionState.Open Then
            accConnection.Close()
        End If
        If sqlConnection.State = ConnectionState.Open Then
            sqlConnection.Close()
        End If
        MessageBox.Show("Import failed with error: " & Environment.NewLine & Environment.NewLine _
        & ex.ToString)
    End Try
End Sub

I want exactly the same thing but to mysql database. or can someone point me a diffrenet way to do this

Many Thanks

[rproffitt]

I see so many possible points of failure that I'll just write "found code" is usually not the code you are looking for.

It may give you a possible view of the steps you'll need when you write your code but such an import is rarely done more than one time.
Why not look at the usual steps I find when I google "How do I import MS Access database into a SQL Server database?"

[Logic_Brown]

Thank you rpooffitt
actually my original code is

Imports System
Imports System.IO
Imports System.Text
Imports System.Data.OleDb
Imports MySql.Data.MySqlClient

Public Class Form1
Public ds As New DataSet
Public cmd As MySqlCommand
Public dr As MySqlDataReader
Public da As New MySqlDataAdapter
Public conn As New MySqlConnection

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim fileName As String = ""
Dim ofd As New OpenFileDialog
If ofd.ShowDialog = Windows.Forms.DialogResult.OK Then
    fileName = ofd.FileName
    PerformImportToMySql(fileName)
End If
End Sub

Private Sub PerformImportToMySql(ByVal Filename As String)
Dim table As DataTable = New DataTable
Dim accConnection As New OleDb.OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0; Data Source=" & Filename & ";Jet OLEDB:Database Password=cabcpis;")
conn = New MySqlConnection("datasource = localhost; port=3306; username=root; password=12345; database=cps; Character Set=utf8; Convert Zero Datetime=True")

Try

'Import the Access data
accConnection.Open()

Dim accDataAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM CPaper", accConnection)
accDataAdapter.Fill(table)
accConnection.Close()

'Export to MS SQL
 conn.Open()
 da.SelectCommand = New MySqlCommand("SELECT * FROM CPaper", conn)

        Dim MysqlCommandBuilder As New MySqlCommandBuilder(da)
        da.InsertCommand = MysqlCommandBuilder.GetInsertCommand()
        da.UpdateCommand = MysqlCommandBuilder.GetUpdateCommand()
        da.DeleteCommand = MysqlCommandBuilder.GetDeleteCommand()
        da.Update(table)
        conn.Close()
        MsgBox("Inport successfull")
    Catch ex As Exception
        If accConnection.State = ConnectionState.Open Then
            accConnection.Close()
        End If
        If conn.State = ConnectionState.Open Then
            conn.Close()
        End If
        MessageBox.Show("Import failed with error: " & Environment.NewLine & Environment.NewLine _
        & ex.ToString)
    End Try
End Sub

End Class

code runs without showing any error. but, table is not imported. I think problem is with PerformImportToMySql() Function. import and write table back into mysql table. can someone points out the missing part?.

Thanks

[SoftBa]

Try to load data into DataGridView and then load them in the MySQL database.
This is how I do it:

 Dim conn As String = System.Configuration.ConfigurationManager.ConnectionStrings("myproject.My.MySettings.mydatabaseConnectionString").ConnectionString
        Try
            mysqlconn.Open()
            Dim sql As String
            Dim cmd As New MySqlCommand
            For Each row As DataGridViewRow In DataGridView.Rows
                If IsDBNull(row.Cells(0).Value) = False Then
                    If String.IsNullOrEmpty(row.Cells(0).Value) = False Then
                        sql = "INSERT INTO mytable (col1, col2, col3, col4) VALUES (@col1, @col2, @col3, @col4)"
                        cmd.Connection = mysqlconn
                        cmd.CommandText = sql
                        cmd.Parameters.Clear()
                        cmd.Parameters.AddWithValue("col1", row.Cells(0).Value)
                        cmd.Parameters.AddWithValue("col2", row.Cells(1).Value)
                        cmd.Parameters.AddWithValue("col3", row.Cells(2).Value)
                        cmd.Parameters.AddWithValue("col4", row.Cells(3).Value)
                        cmd.ExecuteNonQuery()
                    End If
                End If
            Next
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            mysqlconn.Close()
        End Try

[Logic_Brown]

Thank you very much SoftBa. Thanks for your solution. I can tweak it as i need. I am stuck with date field. what is the format cmd.parameters for date field.
What is the correct parameter format for date field?
This is date field in the database. CDATE()?
cmd.Parameters.AddWithValue("col4", row.Cells(3).Value)

[SoftBa]

I haven't tried but this looks like the part of the code you're looking for

cmd.Parameters.Add("@AddedDate", MySqlDbType.DateTime).Value = category.AddedDate