• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 914
  • Last Modified:

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

0
Tech_Men
Asked:
Tech_Men
  • 4
  • 3
1 Solution
 
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
 
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
 
Tech_MenAuthor Commented:
not working
the same problem again
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now