DaniWeb Forum Index > Software Development > C#

C# RSS

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

Please support our C# advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Nov 2007

Posts: 15

Reputation: NatalyC is an unknown quantity at this point

Solved Threads: 0

NatalyC

Offline

Newbie Poster

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

32 Days Ago

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:

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
 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();
            }
        }
 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:

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
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();
        //    }
        }
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

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
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', 'f24@gmail.com');
END
 
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', 'f24@gmail.com');
END

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

Any help would be appreciated

Last edited by NatalyC; 32 Days Ago at 4:42 pm.

•

Join Date: Feb 2009

Posts: 3,187

Reputation: sknake has much to be proud of

Solved Threads: 571

sknake

Offline

.NET Enthusiast

31 Days Ago

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.

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Nov 2007

Posts: 15

Reputation: NatalyC is an unknown quantity at this point

Solved Threads: 0

NatalyC

Offline

Newbie Poster

31 Days Ago

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:

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
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();
        }
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:

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
    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();
 
        }
    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

•

Join Date: Feb 2009

Posts: 3,187

Reputation: sknake has much to be proud of

Solved Threads: 571

sknake

Offline

.NET Enthusiast

31 Days Ago

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.

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Nov 2007

Posts: 15

Reputation: NatalyC is an unknown quantity at this point

Solved Threads: 0

NatalyC

Offline

Newbie Poster

31 Days Ago

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:

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
 string myInsertQuery = "DELETE FROM books WHERE l_BookID=" + txtISBNDeleteBook.Text + "";
 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

•

Join Date: Jul 2009

Posts: 886

Reputation: DdoubleD is a jewel in the rough

Solved Threads: 140

DdoubleD

Offline

Practically a Posting Shark

31 Days Ago

Try this:

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
        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();
                }
            }
        }
        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();
                }
            }
        }

•

Join Date: Nov 2007

Posts: 15

Reputation: NatalyC is an unknown quantity at this point

Solved Threads: 0

NatalyC

Offline

Newbie Poster

31 Days Ago

It works !!!!

Thank you very much

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 !!!!

Attached Files

MySQLBiblioteca1.pdf (37.1 KB, 3 views)

•

Join Date: Jul 2009

Posts: 886

Reputation: DdoubleD is a jewel in the rough

Solved Threads: 140

DdoubleD

Offline

Practically a Posting Shark

31 Days Ago

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.