Solved

Stored Procedure gives Transaction count error

Posted on 2011-09-04
13
422 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 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

10 Experts available now in Live!

Get 1:1 Help Now