An unhandled exception of type 'System.Data.SqlClient.SqlException' error

Question

0.00/5 (No votes)

See more:

I get this error:

C#

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:

C#

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?

Posted 23-Nov-15 7:20am

Member 12161319

Add a Solution

3 solutions

Solution 1

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.

C#

  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
        }

Look at this solution that simply inserts and fills rows...

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

Posted 23-Nov-15 7:36am

Michael_Davies

Updated 23-Nov-15 7:39am

v2

Solution 2

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

Posted 23-Nov-15 7:37am

Dave Kreskowiak

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)

Richard Deeming · Accepted Answer · 2015-11-23T07:39:00

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:

C#

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;
        }
    }
}