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