Solved

Stored Procedure gives Transaction count error

Posted on 2011-09-04
13
426 Views
Last Modified: 2012-05-12
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

Open in new window

0
Comment
Question by:doramail05
  • 8
  • 5
13 Comments
 
LVL 9

Accepted Solution

by:
mimran18 earned 500 total points
ID: 36482430
Please try this.  
/****** 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

SET XACT_ABORT ON

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

Select @returnoutput 

COMMIT TRANSACTION TransInsertSA_TM
END TRY

BEGIN CATCH
         if @@TRANCOUNT > 0
ROLLBACK TRANSACTION TransInsertSA_TM
END CATCH

Open in new window

0
 
LVL 9

Expert Comment

by:mimran18
ID: 36482433
I just added "SET XACT_ABORT ON" to line # 37
and change "RETURN @returnoutput" to "Select @returnoutput " .

cheers



0
 
LVL 1

Author Comment

by:doramail05
ID: 36482490
having conversion prob which

 Unable to cast object of type 'System.Data.SqlClient.SqlParameter' to type 'System.IConvertible'.

-- >   return Convert.ToInt32(returnValue);
0
 
LVL 9

Expert Comment

by:mimran18
ID: 36482500
Step 1- First execute the procedure in SQl if it is working fine.
Step-2 print returnValue in c#
Step-3 then you can type cast it respectively.



0
 
LVL 1

Author Comment

by:doramail05
ID: 36482534
once putting Convert.ToInt32(returnValue);
input immediate window,

it shows the error same as

Unable to cast object of type 'System.Data.SqlClient.SqlParameter' to type 'System.IConvertible'.

-.-
0
 
LVL 9

Expert Comment

by:mimran18
ID: 36482559
try this.

/****** 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,
@returnoutput INT OUTPUT 

AS

DECLARE @error int
--DECLARE @returnoutput int

SET XACT_ABORT ON

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

COMMIT TRANSACTION TransInsertSA_TM
return @returnoutput 
END TRY

BEGIN CATCH
         if @@TRANCOUNT > 0
ROLLBACK TRANSACTION TransInsertSA_TM
END CATCH

Open in new window

0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 1

Author Comment

by:doramail05
ID: 36482569
tried that b4, it gave

Procedure or function 'spInsertStudentAttendanceNTraineeModule' expects parameter '@returnoutput', which was not supplied.
0
 
LVL 1

Author Comment

by:doramail05
ID: 36482579
tried couple of .net side code

but still couldnt get it -.- ~
SqlParameter returnValue = new SqlParameter("@returnoutput", SqlDbType.Int);
           
            returnValue.Direction = ParameterDirection.Output;

            myCommand.Parameters.Add(returnValue);

            //myCommand.ExecuteScalar();
            myCommand.ExecuteNonQuery();
            myConnection.Close();

            int intreturnvalue = Convert.ToInt32(returnValue.Value);
            return intreturnvalue;

Open in new window

0
 
LVL 1

Author Comment

by:doramail05
ID: 36482628
wait, it solved already

SqlParameter returnValue = new SqlParameter("@returnoutput", SqlDbType.Int);
   
            returnValue.Direction = ParameterDirection.Output;

            myCommand.Parameters.Add(returnValue);

            myCommand.ExecuteNonQuery();
            myConnection.Close();

            int intreturnvalue = Convert.ToInt32(returnValue.Value);
            return intreturnvalue;

Open in new window

0
 
LVL 1

Author Comment

by:doramail05
ID: 36482632
using the stored procedure -- >
USE [iDash5]
GO
/****** Object:  StoredProcedure [dbo].[spInsertStudentAttendanceNTraineeModule]    Script Date: 09/05/2011 13:29:15 ******/
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(100),
@strLeaveEarlierDuration varchar(100),
@strLeaveStatus varchar(100),
@strAbsenseReason varchar(100),
@strAttendanceStatus varchar(100),

@lgIntakeID bigint,
@lgCTID bigint,
@lgMID bigint,
@lgLID bigint,
@lgTID bigint,
@lgTRID bigint,
@lgSID bigint,
@returnoutput INT OUTPUT 


AS

DECLARE @error int
--DECLARE @returnoutput int

SET XACT_ABORT ON

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

COMMIT TRANSACTION TransInsertSA_TM 

IF @error > 0 

SET @returnoutput = 0

ELSE

SET @returnoutput = 1

RETURN @returnoutput 


END TRY

BEGIN CATCH
         if @@TRANCOUNT > 0
ROLLBACK TRANSACTION TransInsertSA_TM
END CATCH

Open in new window

0
 
LVL 1

Author Comment

by:doramail05
ID: 36482657
I've requested that this question be closed as follows:

Accepted answer: 0 points for doramail05's comment http:/Q_27291222.html#36482632

for the following reason:

solved from other post , forum
0
 
LVL 1

Author Comment

by:doramail05
ID: 36482658
kindly cancel the close, adding point to the answerer
0
 
LVL 9

Expert Comment

by:mimran18
ID: 36482706
Hi,
   "solved from other post "
     What was the problem ? How did u fix it  ?
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now