phpmyadmin Question posted in
The official documentation can be found here.

VB.Net MySQL Connector. Bulk insert stopped working – Phpmyadmin

I’ve been using the code below to populate tables in a MySQL database for a few years and it’s been working fine.

Imports MySql.Data.MySqlClient

Public sub Thing(dt as datatable)

            Try
                'dt is datatable object populated from MSsqlServer
                Dim MySQLcmdSelect As MySqlCommand = New MySqlCommand("Select * From " & tableName) With {
                    .Connection = MySQLcnn
                }
                Dim MySQLad As New MySqlDataAdapter(MySQLcmdSelect)
                Dim MySQLcmdBuilder As New MySqlCommandBuilder(MySQLad)
                Dim MySQLcmd As MySqlCommand = MySQLcmdBuilder.GetInsertCommand()
                MySQLcmd.Connection = MySQLcnn
                MySQLad.InsertCommand = MySQLcmd
                MySQLad.Update(dt)
            Catch ex As Exception
                Debug.Print(ex.Message)
                Console.WriteLine("Error when populating " + tableName + ": " + ex.Message + vbCrLf + ex.InnerException.ToString)
                WriteLog(ex.Message)
            End Try
End Sub

The problem is, it’s stopped working!

The connector seems to be working ok as I can drop tables and create them, but this will not let me bulk insert the contents of the datatable ‘dt’ into the MySQL table.

It doesn’t throw an exception, it just passes over the .update(dt) line as if it were a Debug.Print() line when there’s 1000s of rows in dt.

Effectively I’m selectively extracting data from tables in MS SQLserver and bulk uploading them to tables with the same name in MySQL.

a) is this the best way to do it, and
b) why has this suddenly stopped working?

According to phpMyAdmin its Server version: 5.6.51 – MySQL Community Server (GPL)

EDIT: Further info… all the SQL commands to drop existing tables and create tables work. It is only the .update(dt) that doesn’t work.

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    Still can't get the bulk upload to work, but we have redesigned this so that data is inserted into MySQL at the same time we store it in the MS-SQL database and have removed the need to bulk upload.


  2. 0

    Possible problems with your code could be that the schemas do not match exactly, auto increment columns, or the connection could have been disposed elsewhere (it should be declared local to the method where it is used). Another problem is that dt has had AcceptChanges called or LoadOptions were set incorrectly.

        Private ConnStr As String = "Your connection string"
    Public Sub Thing(dt As DataTable, tableName As String)
        'check the datatable - if it is ok then this check is probably not necessary
        Dim changesDT = dt.GetChanges()
        If changesDT.Rows.Count < 1 Then
            MessageBox.Show("No changes to update")
            Exit Sub
        End If
        Using cn As New MySqlConnection(ConnStr),
            MySQLad As New MySqlDataAdapter("Select * From " & tableName, cn)
cn.open() ' added by OP
            Dim MySQLcmdBuilder As New MySqlCommandBuilder(MySQLad)
            MySQLad.Update(dt)
        End Using
    End Sub
    Login or Signup to reply.
  3. 0

    Perhaps you have "0000-00-00"? If so, a setting has changed the default — new versions of MySQL disallow zero dates. Read about sql_modes.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search