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();