Transakcje w MS SQL

0

Witam, mam problem z transakcjami, próbuję zapiąć to w całość ale mam problem.
Mam 3 listy, pierwsza z elementami do usunięcia druga z elementami do edycji oraz trzecia z elementami do dodania.
Całość zamieszczam poniżej. Problem pojawia się podczas edycji drugiego rekordu:

ex = {"The variable name '@id_kontrahent_uwaga_kontrahent' has already been declared. Variable names must be unique within a query batch or stored procedure."}

Wcześniej próbowałem tak:

int id_nast_kontrahent_uwaga = Convert.ToInt32(commandUpdateInsertKontrahentUwagi.ExecuteScalar());

, ale i tak było źle

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

 SqlCommand commandInsertKontrahentUwagi = new SqlCommand("INSERT INTO Kontrahent_Uwaga (id_kontrahent_uwaga_kontrahent, info_kontrahent_uwaga, id_utwo_kontrahent_uwaga, data_utwo_kontrahent_uwaga) VALUES(@id_kontrahent_uwaga_kontrahent, @info_kontrahent_uwaga, @id_utwo_kontrahent_uwaga, @data_utwo_kontrahent_uwaga)", connection);

SqlCommand commandUpdateFirstKontrahentUwagi = new SqlCommand("UPDATE Kontrahent_Uwaga SET info_kontrahent_uwaga = '#' + CONVERT(VARCHAR(100), @id_kontrahent_uwaga) + '#' + (SELECT info_kontrahent_uwaga FROM Kontrahent_Uwaga WHERE id_kontrahent_uwaga = @id_kontrahent_uwaga) WHERE id_kontrahent_uwaga = @id_kontrahent_uwaga", connection);
            SqlCommand commandUpdateInsertKontrahentUwagi = new SqlCommand("INSERT INTO Kontrahent_Uwaga (id_kontrahent_uwaga_kontrahent, info_kontrahent_uwaga, id_utwo_kontrahent_uwaga, data_utwo_kontrahent_uwaga) VALUES(@id_kontrahent_uwaga_kontrahent, @info_kontrahent_uwaga, @id_utwo_kontrahent_uwaga, @data_utwo_kontrahent_uwaga) SELECT id = @@IDENTITY", connection);
            SqlCommand commandUpdateSecondKontrahentUwagi = new SqlCommand("UPDATE Kontrahent_Uwaga SET id_nast_kontrahent_uwaga = @id_nast_kontrahent_uwaga WHERE id_kontrahent_uwaga = @id_kontrahent_uwaga", connection);

            SqlCommand commandDeleteKontrahentUwagi = new SqlCommand("UPDATE Kontrahent_Uwaga SET id_usun_kontrahent_uwaga = @id_usun_kontrahent_uwaga, data_usun_kontrahent_uwaga = @data_usun_kontrahent_uwaga WHERE id_kontrahent_uwaga = @id_kontrahent_uwaga", connection);


...
...
...
                connection.Open();
                transaction = connection.BeginTransaction("Edycja kontrahenta Uwagi");

                commandInsertKontrahentUwagi.Transaction = transaction;
                commandDeleteKontrahentUwagi.Transaction = transaction;
                commandUpdateFirstKontrahentUwagi.Transaction = transaction;
                commandUpdateInsertKontrahentUwagi.Transaction = transaction;
                commandUpdateSecondKontrahentUwagi.Transaction = transaction;

                foreach (TextBox tb in lUsunUwagi)
                {
                    commandDeleteKontrahentUwagi.Parameters.Clear();
                    commandDeleteKontrahentUwagi.Parameters.AddWithValue("@id_kontrahent_uwaga", tb.Name);
                    commandDeleteKontrahentUwagi.Parameters.AddWithValue("@id_usun_kontrahent_uwaga", UserLogin.id);
                    commandDeleteKontrahentUwagi.Parameters.AddWithValue("@data_usun_kontrahent_uwaga", now);

                    commandDeleteKontrahentUwagi.ExecuteNonQuery();
                }

                foreach (TextBox tb in lEdytujUwagi)
                {
                    commandUpdateFirstKontrahentUwagi.Parameters.Clear();
                    commandUpdateFirstKontrahentUwagi.Parameters.AddWithValue("@id_kontrahent_uwaga", tb.Name);

                    commandUpdateFirstKontrahentUwagi.ExecuteNonQuery();

                    commandUpdateInsertKontrahentUwagi.Parameters.AddWithValue("@id_kontrahent_uwaga_kontrahent", id_kontrahent);
                    commandUpdateInsertKontrahentUwagi.Parameters.AddWithValue("@info_kontrahent_uwaga", tb.Text);
                    commandUpdateInsertKontrahentUwagi.Parameters.AddWithValue("@id_utwo_kontrahent_uwaga", UserLogin.id);
                    commandUpdateInsertKontrahentUwagi.Parameters.AddWithValue("@data_utwo_kontrahent_uwaga", now);

                    
                    commandUpdateSecondKontrahentUwagi.Parameters.AddWithValue("@id_nast_kontrahent_uwaga", Convert.ToInt32(commandUpdateInsertKontrahentUwagi.ExecuteScalar()));
                    commandUpdateSecondKontrahentUwagi.Parameters.AddWithValue("@id_kontrahent_uwaga", tb.Name);

                    commandUpdateSecondKontrahentUwagi.ExecuteNonQuery();
                }

                foreach (TextBox tb in lDodajUwagi)
                {
                    commandInsertKontrahentUwagi.Parameters.Clear();
                    commandInsertKontrahentUwagi.Parameters.AddWithValue("@id_kontrahent_uwaga_kontrahent", id_kontrahent);
                    commandInsertKontrahentUwagi.Parameters.AddWithValue("@info_kontrahent_uwaga", tb.Text);
                    commandInsertKontrahentUwagi.Parameters.AddWithValue("@id_utwo_kontrahent_uwaga", UserLogin.id);
                    commandInsertKontrahentUwagi.Parameters.AddWithValue("@data_utwo_kontrahent_uwaga", now);

                    commandInsertKontrahentUwagi.ExecuteNonQuery();
                }

                transaction.Commit();
0

Zapomiałem o Parametre.Clear() :( Przepraszam, za temat.

1 użytkowników online, w tym zalogowanych: 0, gości: 1