Click here to Skip to main content
15,567,466 members
Please Sign up or sign in to vote.
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

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.
 
Share this answer
 
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();
 
Share this answer
 
v2
Comments
Richard Deeming 23-Sep-15 15:46pm    
This is not a solution to your question. Why have you posted it as a solution, and accepted it as the answer?
EasyHero 23-Sep-15 16:10pm    
It worked for me perfectly without issuing the error.
Richard Deeming 23-Sep-15 16:12pm    
So why does your "answer" say:
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

If you're still having problems with the code, then it's not an answer.
EasyHero 23-Sep-15 16:27pm    
i just forgot to remove that comment
Richard Deeming 23-Sep-15 16:13pm    
It's exactly the same code block you posted in your question. How is that an answer?

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900