CODEWITHSUNDEEP
vb.net
Varenicou
Varenicou

Reputation: 3

VB.NET - Getting value from MySQL database and inserting it into Ms. Access (MDB) database

I want to Export database from MySQL and import it to Ms. Access (MDB) database. I've tried to code a long LoC to process importing database into a DataTable variable and store the value into it.

This is my code for getting value from adm_users database on MySQL

a_query = "SELECT * FROM adm_users where uac_code='4' or uac_code='4A' or uac_code='1'"
a_da = New MySqlDataAdapter(a_query, db_conn)
a_da.Fill(a_ds, "adm_users")
a_dt = a_ds.Tables("adm_users")

and then i want to store a_dt value into MDB database with this code

For x = 0 To a_dt.Rows.Count - 1
    b_query = "INSERT INTO user_login
               (userid, username, password, uac) VALUES
               ('" + a_dt.Rows(x).Item("userid") + "', '" + a_dt.Rows(x).Item("username") + "', '" + a_dt.Rows(x).Item("userpwd") + "', '2')"
    b_sql = New OleDbCommand(b_query, ms_conn)
    b_sql.ExecuteReader()
Next

and then trying to find a string queries stored in b_query, it looks like this

INSERT INTO user_login
                (userid, username, password, uac) VALUES
                ('admin', 'Varenicou', 'administrator', '2')

and then the return value is: "syntax error in INSERT INTO statement" i found there's nothing wrong with the queries, so i replace b_query variable with simple query like "SELECT FROM user_login" and the return value is: "syntax error in SELECT * FROM statement"

maybe i'm too dumb to ask this question, but my opinion is that VB.NET thinks that i've already opened a connection to MySQL Database and must always on MySQL database. So, when i open database connection with Ms. Access it'll return value like that and VB.NET confused with "which database you wanna use?"

any idea for me to do this task? i've asked my friend for some suggestions, he said that i need to work with class, i also have another idea to work with another new project that doing this task for inputing into Ms. Access database by exporting MySQL database into CSV first.

Upvotes: 0

Views: 404

Answers (1)

jmcilhinney
jmcilhinney

Reputation: 54417

EDIT:

Having reread your question, I can tell you that the specific issue you're having is due to the fact that "password" is a reserved word in Jet/Access SQL. Any time you want to use a reserved word as an identifier in SQL, you need to escape it. For Access, you do that by wrapping it in brackets, e.g.

INSERT INTO user_login (userid, username, [password], uac)
VALUES (@userid, @username, @password, @uac)

My original advice below still stands though.

ORIGINAL:

If you have a DataTable then you don't loop through it to save the data. Just as you make a single Fill call on a data adapter to retrieve data, so you make a single Update call to save data. How does it make sense to call ExecuteReader when there's nothing to read? At least call ExecuteNonQuery but even that is not ideal.

If you want to insert the data it contains then you have to configure the InsertCommand of the second data adapter. You also need all the DataRows in the DataTable to have a RowState of Added. To do that, you simply set AcceptChangesDuringFill to False on the first data adapter. When you call Fill, it adds all the rows and thus their RowState is Added. It then calls AcceptChanges, which sets all the RowStates to Unchanged. Tell it not to call AcceptChanges and you're ready to insert.

Here's an example of getting data from one database and inserting into another using two data adapters:

Dim table As New DataTable

Using sourceAdapter As New MySqlDataAdapter("SELECT Column1, Column2 FROM MyTable",
                                            "source connection string here") With {.AcceptChangesDuringFill = False}
    sourceAdapter.Fill(table)
End Using

Using destinationConnection As New OleDbConnection("destination connection string here"),
      destinationCommand As New OleDbCommand("INSERT INTO MyTable (Column1, Column2) VALUES (@Column1, @Column2)",
                                             destinationConnection),
      destinationAdapter As New OleDbDataAdapter With {.InsertCommand = destinationCommand}
    With destinationCommand.Parameters
        .Add("@Column1", OleDbType.VarChar, 50, "Column1")
        .Add("@Column2", OleDbType.Integer, 0, "Column2")
    End With

    destinationAdapter.Update(table)
End Using

As you can see, it's fairly simple stuff. One data adapter with a SelectCommand and another with an InsertCommand. There's nothing special about the adding of the parameters, but you do need to know about parameters in the first place. If you don't know how to use parameters with ADO.NET then you should learn immediately. You can find my own explanation here.

Upvotes: 1

Related Questions