How to import a Microsoft Access database into mySQL by using c#

[NatalyC]

Hello everybody, :)

I'm trying to import an access database to mySQL by using c#, I'm using this software: MS Access to MySQL from Bullzip, and it seems to be working ok; by using c#, I can display all data from tables, using this connection:

private void btndisplayBooks_Click(object sender, EventArgs e)
        {
            String chainCnx = "Server=localhost; Port=3306; Database=library;Uid=root;Pwd=dark;";
            MySqlConnection cnx = new MySqlConnection(chainCnx);
            MySqlCommand cmd = new MySqlCommand("Select * from books;", cnx);
            DataTable dt = new DataTable();
            try
            {
                cnx.Open();
                dt.Load(cmd.ExecuteReader());
                dgvSearchResult.DataSource = dt; //Gridview
            }
            catch (Exception)
            {
                MessageBox.Show("ERROR");
            }
            finally
            {
                cnx.Close();
            }
        }

That seems to be working very well, finally I can display all data from tables, know, my problem is when I try to insert data from this GUI or a stored procedure executing this action, it seems to be not working, by using c# or MySQL Administrator and QueryBrowser, both gave me an error like this: Column count doesn't match value count at row 1

By using c#, I build this connection to insert data:

private void btnInsertarUser_Click(object sender, EventArgs e)
        {

            String chain2 = "Server=localhost;Port=3306;Database=library;Uid=root;Pwd=dark;";
            MySqlConnection cnx2 = new MySqlConnection(chain2);

            String sql = "insert into users(u_UserID, u_Name, u_lastName, u_Address,  u_Tel, u_Notes, u_Email) values('"
                + txtUserID.Text +     
                "','"
                + txtName.Text +
                "','"
                + txtLastName.Text +
                "','"
                + txtTel.Text +
                "','"
                + txtNotes +
                "','"
                + txtEmail.Text +
                "' );";

            MySqlCommand cmd = new MySqlCommand(sql, cnx2);
            //try
            //{
            cnx2.Open();
            cmd.ExecuteNonQuery();
        //    }
        //    catch (Exception)
        //    {
        //        MessageBox.Show("ERROR");
        //    }
        //    finally
        //    {
        //        cnx2.Close();
        //    }
        }

By using a Stored Procedure in QueryBrowser

CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertData`()
BEGIN
        insert into users(u_UserID, u_Name, u_lastName, u_Address,  u_Tel, u_Notes, u_Email) values('T0120', 'F. John.', 'SMITH', 'SmallVille, '619 570 1053', 'STUDENT', '[e-mail]');
END

The error is: Column count doesn't match value count at row 1

Any help would be appreciated :(

[sknake]

That error explains it. Your column count does not match your value count.

>>insert into users(u_UserID, u_Name, u_lastName, u_Address, u_Tel, u_Notes, u_Email)
1. UserId
2. User Name
3. Last Name
4. Address
5. Telephone
6. Notes
7. Email

>>txtUserID.Text + "','" + txtName.Text + "','" + txtLastName.Text + "','" + txtTel.Text + "','" + txtNotes + "','" + txtEmail.Text + "' );";
1. User Id
2. Name
3. Last Name
4. Telephone
5. Notes
6. Email

As you can see you're specifying data for 7 columns and providing data for 6. You left the address column out of your data.

[NatalyC]

Thank you very much sknake :)

I think that was my problem, but I found another solution to insert data into the database, here is my solution:

private void btnInsertBook_Click(object sender, EventArgs e)
        {
            string myConnectionString2 = "";


            myConnectionString2 = "Server=localhost; Port=3306; Database=library;Uid=root;Pwd=dark;";


            MySqlConnection myConnection2 = new MySqlConnection(myConnectionString2);
            string myInsertQuery2 = "INSERT INTO books(l_BookID, l_Title, l_Author, l_Publisher, l_Subject, l_Count) Values(?l_BookID, ?l_Title, ?l_Author, ?l_Publisher, ?l_Subject, ?l_Count)";
            MySqlCommand myCommand2 = new MySqlCommand(myInsertQuery2);
            myCommand2.Parameters.Add("?l_BookID", MySqlDbType.VarChar, 255).Value = txtISBNBooks.Text;
            myCommand2.Parameters.Add("?l_Title", MySqlDbType.VarChar, 255).Value = txtTitle.Text;
            myCommand2.Parameters.Add("?l_Author", MySqlDbType.VarChar, 255).Value = txtAuthor.Text;
            myCommand2.Parameters.Add("?l_Publisher", MySqlDbType.VarChar, 255).Value = txtPublisher.Text;
            myCommand2.Parameters.Add("?l_Subject", MySqlDbType.VarChar, 255).Value = txtSubject.Text;
            myCommand2.Parameters.Add("?l_Count", MySqlDbType.Int16,11).Value = txtCountBooks.Text;
            


            myCommand2.Connection = myConnection2;
            myConnection2.Open();
            myCommand2.ExecuteNonQuery();
            myCommand2.Connection.Close();
        }

But, know . . . my problem is when I try to delete some data :(
Here is my code about deleting data from a database:

private void btnRemoveBook_Click(object sender, EventArgs e)
        {

            string myConnectionString="";

            myConnectionString = "Server=localhost; Port=3306; Database=library;Uid=root;Pwd=dark;";
  		
 			 MySqlConnection myConnection = new MySqlConnection(myConnectionString);
             string myInsertQuery = "DELETE FROM books WHERE l_BookID=" + txtISBNDeleteBook.Text + "";
 			 MySqlCommand myCommand = new MySqlCommand(myInsertQuery);
  			    
            
            myCommand.Connection = myConnection;
            myConnection.Open(); 	
            myCommand.ExecuteNonQuery();
            myCommand.Connection.Close();
  
        }

I don´t know why is not working, no sintaxis error and nothing else, just that is not working :(

Could anybody help me ? :(

Thanks in advance
:)

[sknake]

You do realize you just gave out your root password, right?

You should change your delete query to use parameterized SQL like your modified insert query and try that.

[NatalyC]

Could you please give me a hand? I don´t know how to do that in case of deleting data.

I think I can use this sentence:

string myInsertQuery = "DELETE FROM books WHERE l_BookID=" + txtISBNDeleteBook.Text + "";

but that seems to be the problem, I don't know how to use parameters in this case.

Thanks in advance

[DdoubleD]

Try this:

public static int DeleteRecord(string bookID)
        {
            const string query = "DELETE FROM Books WHERE l_bookID = @l_bookID";
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    cmd.Parameters.Add(new SqlParameter("@l_bookID", SqlDbType.VarChar)).Value = bookID;
                    return cmd.ExecuteNonQuery();
                }
            }
        }

[NatalyC]

It works !!!!

Thank you very much :D

I've uploaded my project in a pdf file for those who want to know something about MySQL, the only thing is that is in Spanish, but if you have any doubt, you can ask me.

Thanks again !!!! :D

[DdoubleD]

That was very nice that you posted your program/solution so everyone could see it. Couple of suggestions:
1) you define your connection string in multiple places... consider just creating a single "const string" definition for the whole class
2) be careful about posting details of your connection string, especially for "root" uid and password; it's better you don't share this information with the world.

[NatalyC]

Ok :) I've changed my root and pass, so thanks for the advice, I'll consider that for next time

I hope to help somebody else, so thanks again.

:D