重复数据库条目错误 [英] Duplicate Database Entry Error

问题描述

美好的一天，我正在循环通过这样的gridview控件，例如

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

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'"

推荐答案

那是因为你要添加值循环内多次使用相同的参数。



相反，你应该做一些批量上传或直接发送一个xml到一个过程，然后插入。搜索如何从数据表批量插入到数据库。

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.

MySqlConnection conn = new MySqlConnection（connection）;

MySqlCommand cmd = new MySqlCommand（insert into databasename。 tablename（Rows，Columns）值（@rows，@ column），conn）;

MySqlDataReader读取;

conn.Open（）;

cmd.Parameters.Add（@ rows，MySqlDbType.VarChar）;

cmd.Parameters.Add（@ columns，MySqlDbType.VarChar）;

for（int i = 0; i< gridview1.Rows.Count; i ++）

{

foreach（Gridview1.Rows中的GridViewRow j）

{

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]。单元格[0]。文本;

cmd.Parameters [@ columns]。Value = label1.Text;

}

read = cmd.ExecuteReader（）;

read.Close（）;

test.Text =;

}

}

conn.Close（）;

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();

这篇关于重复数据库条目错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！