sqltransaction insert a double record while insert data

Question

I am using that sqltransaction for the insert multiple tables each data.

But I have problem that have the database have the two same data.

What should I do for solve that problem?

please help me? Thanx

SqlConnection baglanti = system.baglan();

            SqlCommand Trislem1_Ekle = new SqlCommand("Insert tblTr (Ad,TipID,BolgeID,Yerler,Resim) values(@Ad,@TipID,@BolgeID,@Yerler,@Resim)  SELECT SCOPE_IDENTITY()", baglanti);
            SqlCommand Tr2_TrAciklama = new SqlCommand("Insert tblTrAciklamaDetay (TrID,TrProgram) values((SELECT IDENT_CURRENT('tblTr')),@TrProgram)", baglanti);

            Trislem1_Ekle.Parameters.AddWithValue("@Ad", txtTrAd.Text);
            Trislem1_Ekle.Parameters.AddWithValue("@TipID", dlTrTip.SelectedValue);

            Trislem1_Ekle.Parameters.AddWithValue("@BolgeID", BolgeID.SelectedValue);
            Trislem1_Ekle.Parameters.AddWithValue("@Yerler", Yerler.Text);
            Trislem1_Ekle.Parameters.AddWithValue("@Resim", Resim.SelectedValue);

            Tr2_TrAciklama.Parameters.AddWithValue("@TrProgram", TrProgram.Text);

            SqlTransaction sqlTrans = baglanti.BeginTransaction();

            Trislem1_Ekle.Transaction = sqlTrans;
            Tr2_TrAciklama.Transaction = sqlTrans;
            try
            {
                Trislem1_Ekle.ExecuteNonQuery();
                Tr2_TrAciklama.ExecuteNonQuery();
                string SonIDGelen = Trislem1_Ekle.ExecuteScalar().ToString();
               sqlTrans.Commit();

            }
           catch (Exception hata)
            {
                Response.Write("İşleminiz yapılamadı, Oluşan Hatanın Detayı<br />" + hata);
               sqlTrans.Rollback(); 
            }

            finally
            {  
                baglanti.Close();
                baglanti.Dispose();
                Trislem1_Ekle.Dispose();
                Tr2_TrAciklama.Dispose();

            }

Answer 1

As far as I see, you executing your Trislem1_Ekle command twice.

One with

Trislem1_Ekle.ExecuteNonQuery();

and the other one with;

string SonIDGelen = Trislem1_Ekle.ExecuteScalar().ToString();

Deleting the first one seems enough. Both ExecuteNonQuery and ExecuteScalar executes your query, and ExecuteScalar returns first column of the first row additionally.

Instead of disposing your database connections and commands manually, use using statement instead.

using(SqlConnection conn = new SqlConnection(conString))
{    
   using(SqlCommand cmd = conn.CreateCommand())
   {
      // Create your commands
      // Add your parameter values
      // Execute your commands
   }
}

And don't use AddWithValue method. It may generate some unexptected results. Use .Add() method and overloads instead.

• Can we stop using AddWithValue() already?

Answer 2

try like this

I think you are executing ExecuteScalar() twice on Command Trislem1_Ekle

            Trislem1_Ekle.ExecuteNonQuery();    
            Tr2_TrAciklama.ExecuteNonQuery();
            string SonIDGelen = Trislem1_Ekle.ExecuteScalar().ToString();

Replace with this:

            string SonIDGelen = Trislem1_Ekle.ExecuteScalar().ToString();
            Tr2_TrAciklama.ExecuteNonQuery();