类型为“System.Data.SqlClient.SqlException”的未处理异常错误 [英] An unhandled exception of type 'System.Data.SqlClient.SqlException' error

问题描述

我收到此错误：

I get this error:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: The variable name '@Id' has already been declared. Variable names must be unique within a query batch or stored procedure

我的代码：

my code:

private void button2_Click(object sender, EventArgs e)
{
    string con = "Data Source=dqq5ndqef2.database.windows.net;Initial Catalog=Login;Integrated Security=False;User ID=richardjacobs97;Password=********;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False;";
    string connectionString = con;

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand cmd = new SqlCommand("INSERT INTO Rota (Id, Name, DateWorking) Values (@identification, @fullname, @dateworking)");
        cmd.CommandType = CommandType.Text;
        cmd.Connection = connection;
        SqlConnectionStringBuilder scb = new SqlConnectionStringBuilder();
        DataTable dt = new DataTable();
        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        for (int i = 0; i < dataGridView1.Rows.Count; i++)
        {
            cmd.Parameters.AddWithValue("@identification", dataGridView1.Rows[i].Cells["Id"].Value);
            cmd.Parameters.AddWithValue("@fullname", dataGridView1.Rows[i].Cells["Name"].Value);
            cmd.Parameters.AddWithValue("@dateworking", dataGridView1.Rows[i].Cells["DateWorking"].Value);
        }
        dt = new DataTable();
        sda.Fill(dt);
        dataGridView1.DataSource = dt;
    }
}

in sugguestions？

in sugguestions?

推荐答案

填写了执行命令所需命令的参数和数据，然后再次回合并尝试再次填充，因此参数已经存在。

Having filled the parameters and data for the command you need to execute the command before going round again and trying to fill it again, hence the parameter already exists.

  for (int i = 0; i < dataGridView1.Rows.Count; i++)
        {
            cmd.Parameters.AddWithValue("@identification", dataGridView1.Rows[i].Cells["Id"].Value);
            cmd.Parameters.AddWithValue("@fullname", dataGridView1.Rows[i].Cells["Name"].Value);
            cmd.Parameters.AddWithValue("@dateworking", dataGridView1.Rows[i].Cells["DateWorking"].Value);

// Here you need to execute the command
        }

看看这个简单地插入和填充行的解决方案...



https://social.msdn.microsoft.com/Forums/en-US/072ce358-bad2-4379-b4ff-3f4170dd8215/how-to-copy-整个datagridview-into-new-table-in-database-using-c？forum = csharplanguage [ ^ ]

您创建了一个在循环的每次迭代中重复使用的Command对象。您必须对参数执行相同的操作。



现在，您将在循环的每次迭代中创建新的Parameter对象。当你进入第二次迭代时，你正在尝试使用与已经存在的参数完全相同的名称创建Parameter对象。



在循环外创建Parameter对象然后只需在循环中设置每个参数的值。



阅读有关Parameters集合的文档。您会发现还有其他创建参数的方法而不是AddWithValue。

You created a Command object that you're reusing in every iteration of your loop. You have to do the same thing with the parameters.

As it stands now, you're creating new Parameter objects on every iteration of the loop. When you get to the second iteration, you're trying to create Parameter objects with the exact same names as Parameters that already exist.

Create the Parameter objects outside the loop and then just set the value of each parameter inside the loop.

Read the documentation on the Parameters collection. You'll find there are other methods of creating Parameters than "AddWithValue".

您在循环中添加参数，但从不执行查询。当您尝试执行查询时，SQL Server正确地告诉您您有重复的参数。



您需要在循环内执行查询，并清除参数每次收集。



另外， INSERT 查询不会返回任何行，所以 SqlDataAdapter 代码不起作用。



尝试这样的事情：

You're adding the parameters in a loop, but never executing the query. When you try to execute the query, SQL Server is correctly telling you that you have duplicate parameters.

You need to execute the query inside the loop, and clear the parameters collection each time.

Also, an INSERT query will not return any rows, so the SqlDataAdapter code isn't going to work.

Try something like this:

private void button2_Click(object sender, EventArgs e)
{
    const string con = "....";
 
    using (SqlConnection connection = new SqlConnection(con))
    {
        connection.Open();
        
        using (SqlTransaction transaction = connection.BeginTransaction())
        using (SqlCommand cmd = new SqlCommand("INSERT INTO Rota (Id, Name, DateWorking) Values (@identification, @fullname, @dateworking)", connection, transaction))
        {
            cmd.CommandType = CommandType.Text;
            
            for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                cmd.Parameters.Clear();
                
                cmd.Parameters.AddWithValue("@identification", dataGridView1.Rows[i].Cells["Id"].Value);
                cmd.Parameters.AddWithValue("@fullname", dataGridView1.Rows[i].Cells["Name"].Value);
                cmd.Parameters.AddWithValue("@dateworking", dataGridView1.Rows[i].Cells["DateWorking"].Value);
                
                cmd.ExecuteNonQuery();
            }
            
            transaction.Commit();
        }
        
        using (SqlCommand cmd = new SqlCommand("SELECT Id, Name, DateWorking FROM Rota", connection))
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            sda.Fill(dt);
            dataGridView1.DataSource = dt;
        }
    }
}

这篇关于类型为“System.Data.SqlClient.SqlException”的未处理异常错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！