problem whit a transaction and sp

hi there i have this func :

   static SqlTransaction tran1;
        public static bool InsNewCall(DateTime CallOpenDate, int CallCustNum, int CallOpenEmpNum, int CallTechId,
         DateTime CallFixDate, string CallHourRange, string CallRem,int CallDetProRunId,string CallDetComplain)
        {
            try
            {
             
                SqlParameter[] inparam = new SqlParameter[7];

                inparam[0] = new SqlParameter("@CallOpenDate", SqlDbType.DateTime);
                inparam[0].Value = CallOpenDate;

                inparam[1] = new SqlParameter("@CallCustNum", SqlDbType.BigInt);
                inparam[1].Value = CallCustNum;

                inparam[2] = new SqlParameter("@CallOpenEmpNum", SqlDbType.BigInt);
                inparam[2].Value = CallOpenEmpNum;

                inparam[3] = new SqlParameter("@CallTechId", SqlDbType.BigInt);
                inparam[3].Value = CallTechId;

                inparam[4] = new SqlParameter("@CallFixDate", SqlDbType.DateTime);
                inparam[4].Value = CallFixDate;

                inparam[5] = new SqlParameter("@CallHourRange", SqlDbType.NVarChar);
                inparam[5].Value = CallHourRange;

                inparam[6] = new SqlParameter("@CallRem", SqlDbType.NVarChar);
                inparam[6].Value = CallRem;


                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;

                cmd.Parameters.Clear();
                cmd.Parameters.Add(inparam[0]);
                cmd.Parameters.Add(inparam[1]);
                cmd.Parameters.Add(inparam[2]);
                cmd.Parameters.Add(inparam[3]);
                cmd.Parameters.Add(inparam[4]);
                cmd.Parameters.Add(inparam[5]);
                cmd.Parameters.Add(inparam[6]);

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "InsertNewCall";
                cmd.Transaction = tran1;
               
               
                con.Open();
                tran1 = con.BeginTransaction();

                int RowId = Int32.Parse(cmd.ExecuteScalar().ToString()); // Fall here !!!
                InsNewCallDet(1, CallDetProRunId, CallDetComplain, con, tran1);

                tran1.Commit();
                tran1.Dispose();
                con.Close();
                return true;


            }
            catch (Exception ex)
            {
                tran1.Rollback();
                tran1.Dispose();
                con.Close();
                throw new Exception(ex.Message);

            }

        }
when i run it i am geting this Exs :

ExecuteScalar requires the command to have a transaction when the connection
assigned to the command is in a pending local transaction.
 The Transaction property of the command has not been initialized."}
in this code line :    int RowId = Int32.Parse(cmd.ExecuteScalar().ToString());
how can i fix that ?
thanks ...

this is the sp :
ALTER PROCEDURE dbo.InsertNewCall
(
	
		@CallOpenDate datetime,
		@CallCustNum bigint,
		@CallOpenEmpNum bigint,
		@CallTechId bigint,
		@CallFixDate datetime,
		@CallHourRange nvarchar(50),
		@CallRem nvarchar(2000),
		@CallDone bit
		
	)
 
AS INSERT INTO [RealCrm].[dbo].[Calls]
(
		
		[CallOpenDate],
		[CallCustNum],
		[CallOpenEmpNum],
		[CallTechId],
		[CallFixDate],
		[CallHourRange],
		[CallRem],
		[CallDone]
		
		
 )
 
	values
	(	
		@CallOpenDate,
		@CallCustNum,
		@CallOpenEmpNum,
		@CallTechId,
		@CallFixDate,
		@CallHourRange,
		@CallRem,
		@CallDone 
		
	
		)
	SELECT SCOPE_IDENTITY()

Open in new window

Tech_MenAsked:
Who is Participating?
 
lucius_theCommented:

...
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "InsertNewCall";
 
con.Open();
tran1 = con.BeginTransaction();
cmd.Transaction = tran1;
 
int RowId = Int32.Parse(cmd.ExecuteScalar().ToString());
...

Open in new window

0
 
lucius_theCommented:
You have to set
cmd.Transaction = tran1

after you open the connection and begin the transaction. Just move it 2 lines down in code :)
0
 
Tech_MenAuthor Commented:
not working
the same problem again
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Tech_MenAuthor Commented:
+            $exception      {"ExecuteScalar: Connection property has not been initialized."}      System.Exception {System.InvalidOperationException}
0
 
Tech_MenAuthor Commented:
wait i think i know what the problem
0
 
Tech_MenAuthor Commented:
thanks...
0
 
lucius_theCommented:
Oh, jes, my bad. Also you need the connection open first. So:
...
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "InsertNewCall";
 
con.Open();
tran1 = con.BeginTransaction();
cmd.connection=con
cmd.Transaction = tran1;
 
int RowId = Int32.Parse(cmd.ExecuteScalar().ToString());
...

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.