troubleshooting Question

Stored Procedure gives Transaction count error

Avatar of doramail05
doramail05Flag for Malaysia asked on
ASP.NETMicrosoft SQL Server 2005Microsoft SQL Server 2008
13 Comments1 Solution471 ViewsLast Modified:
While using the stored procedure and asp.net to get return value from a stored procedure,

it gives :
[System.Data.SqlClient.SqlException] = {"Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1."}
USE [iDash5]
GO

  using (SqlConnection myConnection = new SqlConnection(strConnString))
        {
      
            myConnection.Open();
         
            SqlCommand myCommand = new SqlCommand("spInsertStudentAttendanceNTraineeModule", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.Parameters.AddWithValue("@dtStartTime", dtStartTime);
          
            myCommand.Parameters.AddWithValue("@dtActualTime", dtActualTime);
            myCommand.Parameters.AddWithValue("@dtLeaveTime", dtLeaveTime);
            myCommand.Parameters.AddWithValue("@dtActualLeaveTime", dtActualLeaveTime);
            myCommand.Parameters.AddWithValue("@strAttendanceStatus", strAttendanceStatus);
            myCommand.Parameters.AddWithValue("@strLateDuration", strLateDuration);
            myCommand.Parameters.AddWithValue("@strLeaveEarlierDuration", strLeaveEarlierDuration);
            myCommand.Parameters.AddWithValue("@strLeaveStatus", strLeaveStatus);
            myCommand.Parameters.AddWithValue("@strAbsenseReason", strAbsenseReason);

            myCommand.Parameters.AddWithValue("@lgCTID", lgCTID);
            myCommand.Parameters.AddWithValue("@lgMID", lgMID);
            myCommand.Parameters.AddWithValue("@lgLID", lgLID);
            myCommand.Parameters.AddWithValue("@lgTID", lgTID);
            myCommand.Parameters.AddWithValue("@lgSID", lgSID);
            myCommand.Parameters.AddWithValue("@lgIntakeID", lgPID);
            myCommand.Parameters.AddWithValue("@lgTRID", lgTRID);

            SqlParameter returnValue = new SqlParameter("@returnoutput", SqlDbType.Int);
            returnValue.Direction = ParameterDirection.ReturnValue;
           
            myCommand.ExecuteNonQuery();
            myConnection.Close();

            return Convert.ToInt32(returnValue);

sp
--
/****** Object:  StoredProcedure [dbo].[spInsertStudentAttendanceNTraineeModule]    Script Date: 09/05/2011 10:30:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spInsertStudentAttendanceNTraineeModule]

@dtStartTime datetime,
@dtActualTime datetime,
@dtLeaveTime datetime,
@dtActualLeaveTime datetime,
@strLateDuration varchar,
@strLeaveEarlierDuration varchar,
@strLeaveStatus varchar,
@strAbsenseReason varchar,
@strAttendanceStatus varchar,

@lgIntakeID bigint,
@lgCTID bigint,
@lgMID bigint,
@lgLID bigint,
@lgTID bigint,
@lgTRID bigint,
@lgSID bigint


AS

DECLARE @error int
DECLARE @returnoutput int
BEGIN TRY

BEGIN TRANSACTION TransInsertSA_TM
DECLARE @id int; 
Insert into dbo.StudentAttendance(attendancestatus, lateduration, actualtime, arrivedtime, leavetime, actualleavetime, leavestatus, earlierduration, absensereason, markby, datecreated, datemodified) 
VALUES (@strAttendanceStatus, @strLateDuration, @dtActualTime, @dtStartTime, @dtLeaveTime, @dtActualLeaveTime, @strLeaveStatus, @strLeaveEarlierDuration, @strAbsenseReason, @lgTRID, GETDATE(), GETDATE()) 
set @id = SCOPE_IDENTITY(); 


Insert into dbo.TraineeModule(tid, lid, mid, sid, pid, trid, said, ctid) 
VALUES (@lgTID, @lgLID, @lgMID, @lgSID, @lgIntakeID, @lgTRID, @id, @lgCTID);

SELECT @error = @@ERROR

IF @error > 0 

SET @returnoutput = 0

ELSE

SET @returnoutput = 1

RETURN @returnoutput 

COMMIT TRANSACTION TransInsertSA_TM
END TRY

BEGIN CATCH
         if @@TRANCOUNT > 0
ROLLBACK TRANSACTION TransInsertSA_TM
END CATCH
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 13 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros