Click here to Skip to main content
14,735,691 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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?
Posted

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
        }


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[^]
   
v2
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".
   
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;
        }
    }
}
   
Comments
Member 12161319 23-Nov-15 13:47pm
   
Done what you have done now i get this:

Exception thrown: 'System.Data.SqlClient.SqlException' in System.Data.dll

Additional information: Cannot insert the value NULL into column 'Id', table 'Login.dbo.Rota'; column does not allow nulls. INSERT fails.

The statement has been terminated.

i guess this means that there is no data going into the database? but there is..?
Richard Deeming 23-Nov-15 13:50pm
   
You have one or more rows in your grid where the Id column is empty. Debug your code and find out why.
Member 12161319 23-Nov-15 13:58pm
   
this is what my database looks like:

id Name Date
NULL NULL NULL

is that a problem?
Richard Deeming 23-Nov-15 13:59pm
   
The error message suggests that the Id column cannot be null.

Therefore, you need to debug your code to find out why you're getting a null value in the Id column in your grid.
Member 12161319 23-Nov-15 14:12pm
   
just debugged it basically its adding all the data but its adding another one with no value so its null.. can't see why
Member 12161319 23-Nov-15 14:36pm
   
http://www.codeproject.com/Questions/1058871/Exception-thrown-System-Data-SqlClient-SqlExceptio

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