Solved

Stored Procedure gives Transaction count error

Posted on 2011-09-04
13
428 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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
 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

839 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