Solved

Stored Procedure gives Transaction count error

Posted on 2011-09-04
13
432 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

688 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