Duplicate Database Entry Error

Question

0.00/5 (No votes)

See more:

good day, i am looping through a gridview control like this for example

+--------------+---------+---------+---------+---------+---------+---------+
| Values       | Col1    | Col2    | Col3    | Col4    | Col5    | Col6    |
+--------------+---------+---------+---------+---------+---------+---------+
| Row1         | 1       | 2       | 3       | 4       | 5       | 6       |
+--------------+---------+---------+---------+---------+---------+---------+
| Row2         | 7       | 8       | 9       | 10      | 11      | 12      |
+--------------+---------+---------+---------+---------+---------+---------+
| Row3         | 13      | 14      | 15      | 16      | 17      | 18      |
+--------------+---------+---------+---------+---------+---------+---------+
| Row4         | 19      | 20      | 21      | 22      | 23      | 24      |
+--------------+---------+---------+---------+---------+---------+---------+

i'm looping through it and retrieving the values in the table below using these lines of code

C#

MySqlConnection conn = new MySqlConnection(connection);
                MySqlCommand cmd = new MySqlCommand("insert into databasename.tablename (Rows, Columns) values (@rows, @columns)", conn);
                MySqlDataReader read;
                conn.Open();
                cmd.Parameters.Add("@rows", MySqlDbType.VarChar);
                cmd.Parameters.Add("@columns", MySqlDbType.VarChar);
                foreach(GridViewRow j in gridview1.Rows)
                {
                    for (int i = 0; i < gridview1.Rows.Count; i++) 
                    {                        
                        for (int k = 1; k < j.Cells.Count; k++)
                        {
                            label1.Text += gridview1.HeaderRow.Cells[k].Text + " ";
                            label1.Text += gridview1.Rows[i].Cells[k].Text +", ";
                            cmd.Parameters["@rows"].Value = excel.Rows[i].Cells[0].Text;
                            cmd.Parameters["@columns"].Value = label1.Text;
                        }
                        read = cmd.ExecuteReader(); // i don't know why i get a duplicate record error message on this line after it loops through all the rows and inserts them
                        read.Close();
                        test.Text = "";
                    }
                }
                conn.Close();

, lets assume the table below is a database table, this is how the loop results gets saved in the database

+--------------+-------------------------------------------------------+
| Rows         | Columns                                               |
+--------------+-------------------------------------------------------+
| Row1         | col1 1, col2 2, col3 3, col4 4, col5 5, col6 6,       |
+--------------+-------------------------------------------------------+
| Row2         | col1 7, col2 8, col3 9, col4 10, col5 11, col6 12,    |
+--------------+-------------------------------------------------------+
| Row3         | col1 13, col2 14, col3 15, col4 16, col5 17, col6 18, |
+--------------+-------------------------------------------------------+
| Row4         | col1 19, col2 20, col3 21, col4 22, col5 23, col6 24, |
+--------------+-------------------------------------------------------+

but i get this error after the loop loops through the table and inserts all records into the database
"Duplicate entry 'Row1' for key 'PRIMARY'"

Posted 23-Sep-15 8:27am

EasyHero

Add a Solution

2 solutions

Solution 1

That is because you are adding values for same parameter many times inside the loop.

Instead you should do some bulk upload or directly send one xml to a procedure and then insert. Search how to bulk insert from a datatable to database.

Posted 23-Sep-15 8:44am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

EasyHero · Accepted Answer · 2015-09-23T09:28:00

MySqlConnection conn = new MySqlConnection(connection);
MySqlCommand cmd = new MySqlCommand("insert into databasename.tablename (Rows, Columns) values (@rows, @columns)", conn);
MySqlDataReader read;
conn.Open();
cmd.Parameters.Add("@rows", MySqlDbType.VarChar);
cmd.Parameters.Add("@columns", MySqlDbType.VarChar);
for (int i = 0; i < gridview1.Rows.Count; i++)
{
foreach (GridViewRow j in gridview1.Rows)
{
for (int k = 1; k < j.Cells.Count; k++)
{
label1.Text += gridview1.HeaderRow.Cells[k].Text + " ";
label1.Text += gridview1.Rows[i].Cells[k].Text +", ";
cmd.Parameters["@rows"].Value = excel.Rows[i].Cells[0].Text;
cmd.Parameters["@columns"].Value = label1.Text;
}
read = cmd.ExecuteReader();
read.Close();
test.Text = "";
}
}
conn.Close();