Insert /copy records from Access database table to MySQL remote database one by one

Question

I want to insert table records from an Access database to MySQL remote server.

My method is to:

• load Access database from first to last record in a TextBox with loop
• check if the record is already inserted in MySQL remote database
• if not, then insert it one by one

The code produces an error message:

the value cannot be null parameters mode: Data-source

My code is here:

SQLda = New MySqlDataAdapter(("INSERT INTO approved (word, approveds) Select * FROM (SELECT '" & TextBox3.Text & "', 'No') AS tmp WHERE Not EXISTS (SELECT word FROM approved WHERE word= '" & TextBox3.Text & "') LIMIT 1"), MysqlConn)

MysqlConn = New MySqlConnection
ListBox1.Items.Clear()

MysqlConn.ConnectionString = "server=localhost;userid=root;password=;port=3306;database=sindhila_spellchecker;Character Set=utf8;SslMode=none;"

SQLda = New MySqlDataAdapter(("SELECT * FROM approved"), MysqlConn)
SQLda.Fill(dbds, "doctors")
DataGridView1.DataSource = dbds.Tables("doctors")

Try
    MysqlConn.Open()
    MessageBox.Show("Connection Successful, click ok to continue")
    Label3.Visible = True

    romantranssql = "Select * from approved order by id"

    pth = My.Application.Info.DirectoryPath
    romantransconn.ConnectionString = "Provider=Microsoft.ace.oledb.12.0; Data Source=" & pth & "\database.mdb; User Id=admin; Password=;"
    romantransda = New OleDbDataAdapter(romantranssql, romantransconn)
    romantransds = New DataSet
    romantransda.Fill(romantransds, "DisplayCenterData")
    romantransdt = romantransds.Tables("DisplayCenterData")
    romantranscmb = New OleDbCommandBuilder(romantransda)

    Do Until Me.BindingContext(romantransdt).Position = Me.BindingContext(romantransdt).Count - 1

        TextBox3.DataBindings.Clear()
        TextBox3.DataBindings.Add("text", romantransdt, "word")

        MysqlConn = New MySqlConnection

        MysqlConn.ConnectionString = "server=localhost;userid=root;password=;port=3306;database=sindhila_spellchecker;Character Set=utf8;"

        SQLda = New MySqlDataAdapter(("INSERT INTO approved (word, approveds) Select * FROM (SELECT '" & TextBox3.Text & "', 'No') AS tmp WHERE Not EXISTS (SELECT word FROM approved WHERE word= '" & TextBox3.Text & "') LIMIT 1"), MysqlConn)
        SQLda.Fill(dbds, "approved")

        DataGridView1.DataSource = dbds.Tables("approved")
        romantransdtx = dbds.Tables("approved")
        Me.BindingContext(romantransdtx).Position = 0
        Me.BindingContext(romantransdt).Position = Me.BindingContext(romantransdt).Position + 1
        Me.Refresh()
    Loop

    MsgBox("all New records have been uploded To server")
    Label3.Visible = False

    MysqlConn.Close()

Catch ex As Exception
    MessageBox.Show(ex.Message)
Finally
    MysqlConn.Dispose()

End Try

Answer 1

This is not an exact/direct answer to your question but rather it is more of a suggestion

I would be honest here, your code looks ugly and at points, it also makes no sense. Let's start with the ugliness. Here's my favorite quote about this(it's my quote by the way):

You will spend 20% of your time writing codes and 80% of your time maintaining it.

Do you know how many developers in big organizations are frustrated by the poor codes written by the inexperienced programmers? Sometimes they even have to write things from scratch to fix things. Your entire code looks poorly written and i can point out a mistake on almost each line. For example,

Why do

MysqlConn = New MySqlConnection
MysqlConn.ConnectionString = "...."

Where it can be done like

MysqlConn = New MySqlConnection("....")

Yes, even a single line of code matters. It may not be have any effect on the execution at all, but it has a major effect on it's appearance. Try to fix these.

Now, let's talk a little about some useless codes you have there.

SQLda = New MySqlDataAdapter(("INSERT INTO.....")), MysqlConn)
...
SQLda = New MySqlDataAdapter(("SELECT * FROM approved"), MysqlConn)

If SQLda is not being used for anything before assigning a new value to it, what was the use of assigning it a value in the first place? Well, i may be wrong here if you have't posted your entire code..

Now, you are using a Try-Catch statement here.. Why are you catching all possible exceptions by using the class Exception whereas you can just catch the specific exception that could really occur here like an SqlException? Catching an (Exception) is a bad idea. Read more here

You are opening your connection to the database and when things are done, you close it and dispose it(by calling the Dispose method). Why do so much hustle when you can simply use a Using statement as the SqlConnection class already implements IDisposable ?

So, instead of this:

 Dim MySqlConn = New SqlConnection("...")
 MySqlConn.Open()
 .....
 .....
 MySqlConn.Close()
 MySqlConn.Dispose()

Do this

Using MySqlConn = New SqlConnection("....")
...
End Using

Some final words

Your code makes me feel that your's is a small project. But for future projects, here are some suggestions that are strongly recommended to be followed:

• Never store connection string in code or local files

Store connection strings in config files such as the app.config or web.config file.

• Never pass values to SQL statements directly, instead use parameters

Writing codes to pass direct values just opens doors to SQL injections. So, always pass parameters.

• Don't hide/show controls, instead create/remove them from code-behind when needed

You can create a control(a Label in your case) in your form and hide it and then display it when needed. But this would cost you resources. If you're using 1 or even 5 labels, maybe not too much resource would be consumed... But we don't just have a UI with labels only, do we? You will pretty soon realize that a millisecond of increase in execution or performance goes a long long way. So, create/remove controls programatically whenever necessary. This will do the GarbageCollector a huge favor.

• Never use any object/class/anything without understanding it properly

You are using DataTables. Do you know how much memory it consumes? A lighter alternative is an IEnumerable(T)(generic list) like a List. Or even a simple DataReader is a better option when it comes to performance.

• Always add useful comments

There's an infamous meme in the developer community. It goes like this :

When i wrote the code, only god and i knew how it worked ... Now only god knows how it works.

Always add useful comments in code. This may not benefit you directly but it will help future developers who debug your code. Also, in Visual Studio, add the #region keyword to divide codes into section.

I guess that's enough for now. I hope you find this useful.

Cheers!

Answer 2

You do not insert a value for auto-increment fields. The following works on a little test database I use.

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    Dim RetVal As Integer
    Dim InsertQuery = "INSERT INTO dbusers (myuser, mypassword)
                      Select  @name, @pass
                      WHERE Not EXISTS
                      (SELECT * FROM dbusers WHERE myuser = @name AND mypassword = @pass);"
    Using cn As New MySqlConnection(My.Settings.dbusersConnection)
        Using cmd As New MySqlCommand(InsertQuery, cn)
            cmd.Parameters.Add("@name", MySqlDbType.VarChar).Value = TextBox1.Text
            cmd.Parameters.Add("@pass", MySqlDbType.VarChar).Value = TextBox2.Text
            cn.Open()
            RetVal = cmd.ExecuteNonQuery
        End Using
    End Using
    If RetVal = 1 Then
        MessageBox.Show("Success")
    Else
        MessageBox.Show("Failure")
    End If
End Sub