ADO With SQL Transactions

[subman]

We currently have S.P.'s that are already coded with sql transactions.
Is it possible to have a EXE that has an ADO transaction call an S.P.
that has a transaction. No matter how hard I try I cant get it to
work. I cant seem to find any concrete info that it can or can not be
done.

I can run the S.P. in analyzer no problem.
I can execute the S.P. from a command object w/o a transaction no
problem.

I can NOT call the S.P. from a command object with a transaction
I get the following error when I try it

"System.Data.SqlClient.SqlException: Transaction count after EXECUTE
indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.
Previous count = 1, current count = 0."
Subman

[Chris]

subman wrote:

We currently have S.P.'s that are already coded with sql transactions.
Is it possible to have a EXE that has an ADO transaction call an S.P.
that has a transaction. No matter how hard I try I cant get it to
work. I cant seem to find any concrete info that it can or can not be
done.

I can run the S.P. in analyzer no problem.
I can execute the S.P. from a command object w/o a transaction no
problem.

I can NOT call the S.P. from a command object with a transaction
I get the following error when I try it

"System.Data.SqlClient.SqlException: Transaction count after EXECUTE
indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.
Previous count = 1, current count = 0."
Subman

Some code would probably help use figure out what's going on easier.

Also, since the proc has the transaction built into it, why do you need
to wrap the command in a transaction, are you calling multiple procs?

Chris

[subman]

There are over 500 S.P.'s that have been written already. Alot of
times we(.net devs) use 1 to many of the sp's in a exe. it would be
nice to be able to "wrap" the sp's in a ado transaction.

Subman

SP Follows

ALTER PROCEDURE dbo.proc_Bajio_SalesPosting

AS

SET NOCOUNT ON

--Declarations
DECLARE @_StatusString VARCHAR(200)
DECLARE @_ProcessTime DATETIME
DECLARE @_WED DATETIME
DECLARE @_StartTime DATETIME
DECLARE @_MyRowCount INTEGER
DECLARE @_MyError INTEGER
DECLARE @_SalesPostingRunning INTEGER
DECLARE @_TERST varchar(10)

--Start The Transaction
BEGIN TRANSACTION SalePosting

SELECT @_StatusString = 'Trans count ' +
CONVERT(VARCHAR(50),@@TRANCOUNT)
PRINT @_StatusString
-- Start logging progress
SELECT @_StatusString = 'Started Stored
Procedure(proc_Bajio_SalesPosting) At - ' +
CONVERT(VARCHAR(50),GETDATE())
PRINT @_StatusString
SELECT @_ProcessTime = GETDATE()
SELECT @_StartTime = @_ProcessTime

-- Verify all sales have been entered
CREATE TABLE #MissingStores
(FranchiseNum INTEGER)

--Log Progress
SELECT @_MyError = @@ERROR, @_MyRowCount = @@RowCount
IF @_MyError <> 0
BEGIN
SELECT @_StatusString = 'STEP 10|ERROR! Creating #MissingStores
(Rolling Back) - Total Minutes - ' +
CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,3),DATEDIFF (SECOND,@_ProcessTime,GETDATE()))/60)
+ ' ROWS AFFECTED(' + convert(varchar(50),@_MyRowCount) +')'
PRINT @_StatusString
GOTO ErrorHandler
END
ELSE
BEGIN
SELECT @_StatusString = 'STEP 10|Created #MissingStores - Total
Minutes - ' +
CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,3),DATEDIFF (SECOND,@_ProcessTime,GETDATE()))/60)
+ ' ROWS AFFECTED(' + convert(varchar(50),@_MyRowCount) +')'
PRINT @_StatusString
SELECT @_ProcessTime = GETDATE()
END

-- Get current WeekEndingDate
SET @_WED = (SELECT TOP 1 WeekEndingDate FROM dbo.WeekEndingDate)

--Log Progress
SELECT @_MyError = @@ERROR, @_MyRowCount = @@RowCount
IF @_MyError <> 0
BEGIN
SELECT @_StatusString = 'STEP 20|ERROR! Setting WED Variable (Rolling
Back) - Total Minutes - ' +
CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,3),DATEDIFF (SECOND,@_ProcessTime,GETDATE()))/60)
+ ' ROWS AFFECTED(' + convert(varchar(50),@_MyRowCount) +')'
PRINT @_StatusString
GOTO ErrorHandler
END
ELSE
BEGIN
SELECT @_StatusString = 'STEP 20|Set WED Variable - Total Minutes - '
+
CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,3),DATEDIFF (SECOND,@_ProcessTime,GETDATE()))/60)
+ ' ROWS AFFECTED(' + convert(varchar(50),@_MyRowCount) +')'
PRINT @_StatusString
SELECT @_ProcessTime = GETDATE()
END

-- Check if the sales posting is already running
SELECT @_SalesPostingRunning = AvailableInd
FROM SubwayNet.dbo.ObjectGroup
WHERE ObjectGroupID = 1 -- Sales Posting

--Log Progress
SELECT @_MyError = @@ERROR, @_MyRowCount = @@RowCount
IF @_MyError <> 0
BEGIN
SELECT @_StatusString = 'STEP 30|ERROR! Setting Sales Posting
Variable (Rolling Back) - Total Minutes - ' +
CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,3),DATEDIFF (SECOND,@_ProcessTime,GETDATE()))/60)
+ ' ROWS AFFECTED(' + convert(varchar(50),@_MyRowCount) +')'
PRINT @_StatusString
GOTO ErrorHandler
END
ELSE
IF @_SalesPostingRunning <> 1
BEGIN
SELECT @_StatusString = 'Sales Posting Currently Running (Rolling
Back) - Total Minutes - ' +
CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,3),DATEDIFF (SECOND,@_ProcessTime,GETDATE()))/60)
+ ' ROWS AFFECTED(' + convert(varchar(50),@_MyRowCount) +')'
PRINT @_StatusString
GOTO ErrorHandler
END
ELSE
BEGIN
SELECT @_StatusString = 'STEP 30|Set Sales Posting Variable - Total
Minutes - ' +
CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,3),DATEDIFF (SECOND,@_ProcessTime,GETDATE()))/60)
+ ' ROWS AFFECTED(' + convert(varchar(50),@_MyRowCount) +')'
PRINT @_StatusString
SELECT @_ProcessTime = GETDATE()
END

-- populate the temp table with all stores that are missing sales
INSERT INTO #MissingStores
EXEC proc_Bajio_Franchise_GetAllByMissingSales

--Log Progress
SELECT @_MyError = @@ERROR, @_MyRowCount = @@RowCount
IF @_MyError <> 0
BEGIN
SELECT @_StatusString = 'STEP 40|ERROR! Populating #MissingStores
(Rolling Back) - Total Minutes - ' +
CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,3),DATEDIFF (SECOND,@_ProcessTime,GETDATE()))/60)
+ ' ROWS AFFECTED(' + convert(varchar(50),@_MyRowCount) +')'
PRINT @_StatusString
GOTO ErrorHandler
END
ELSE
IF @_MyRowCount > 0
BEGIN
SELECT @_StatusString = 'Not All Stores Sent Sales (Rolling Back) -
Total Minutes - ' +
CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,3),DATEDIFF (SECOND,@_ProcessTime,GETDATE()))/60)
+ ' ROWS AFFECTED(' + convert(varchar(50),@_MyRowCount) +')'
PRINT @_StatusString
GOTO ErrorHandler
END
ELSE
BEGIN
SELECT @_StatusString = 'STEP 40|Inserted Into #MissingStores -
Total Minutes - ' +
CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,3),DATEDIFF (SECOND,@_ProcessTime,GETDATE()))/60)
+ ' ROWS AFFECTED(' + convert(varchar(50),@_MyRowCount) +')'
PRINT @_StatusString
SELECT @_ProcessTime = GETDATE()
END

COMMIT TRANSACTION SalePosting
RETURN 0

-- Error Handler Subroutine
ErrorHandler:
BEGIN
SELECT @_StatusString = CONVERT(VARCHAR(50),@@TranCount)
PRINT @_StatusString
IF @@TRANCOUNT = 1
BEGIN
ROLLBACK TRANSACTION
END
SELECT @_StatusString = CONVERT(VARCHAR(50),@@TranCount)
PRINT @_StatusString
RETURN -1
END

Test Code Follows
Imports System.Data.SqlClient
Imports Microsoft.ApplicationBlocks.Data

Public Class Form1
Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

Public Sub New()
MyBase.New()

'This call is required by the Windows Form Designer.
InitializeComponent()

'Add any initialization after the InitializeComponent() call

End Sub

'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As
Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub

'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the Windows Form
Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents SqlConnection1 As
System.Data.SqlClient.SqlConnection
Friend WithEvents SqlCommand1 As System.Data.SqlClient.SqlCommand
Friend WithEvents Button1 As System.Windows.Forms.Button
Friend WithEvents RichTextBox1 As System.Windows.Forms.RichTextBox
Friend WithEvents Button2 As System.Windows.Forms.Button
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.SqlCommand1 = New System.Data.SqlClient.SqlCommand
Me.Button1 = New System.Windows.Forms.Button
Me.RichTextBox1 = New System.Windows.Forms.RichTextBox
Me.Button2 = New System.Windows.Forms.Button
Me.SuspendLayout()
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "workstation
id=""IS-WARAKOMSKI"";packet size=4096;integrated security=SSPI;data
sou" & _
"rce=""SPINOZA\SPINOZA2000"";persist security info=True;initial
catalog=Bajio"
'
'SqlCommand1
'
Me.SqlCommand1.CommandText =
"dbo.[proc_Bajio_SalesPosting03022006]"
Me.SqlCommand1.CommandType =
System.Data.CommandType.StoredProcedure
Me.SqlCommand1.Connection = Me.SqlConnection1
Me.SqlCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE" ,
System.Data.SqlDbType.Int, 4,
System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte),
CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
'
'Button1
'
Me.Button1.Location = New System.Drawing.Point(8, 16)
Me.Button1.Name = "Button1"
Me.Button1.TabIndex = 0
Me.Button1.Text = "Button1"
'
'RichTextBox1
'
Me.RichTextBox1.Location = New System.Drawing.Point(8, 64)
Me.RichTextBox1.Name = "RichTextBox1"
Me.RichTextBox1.Size = New System.Drawing.Size(904, 96)
Me.RichTextBox1.TabIndex = 1
Me.RichTextBox1.Text = ""
'
'Button2
'
Me.Button2.Location = New System.Drawing.Point(776, 192)
Me.Button2.Name = "Button2"
Me.Button2.TabIndex = 2
Me.Button2.Text = "Button2"
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(912, 266)
Me.Controls.Add(Me.Button2)
Me.Controls.Add(Me.RichTextBox1)
Me.Controls.Add(Me.Button1)
Me.Name = "Form1"
Me.Text = "Form1"
Me.ResumeLayout(False)

End Sub

#End Region

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim tnn As SqlTransaction = Nothing
Dim strConnectionString = "data
source=devsql.subapp.subwaydai.com\Spinoza2000;ini tial
catalog=Bajio;user iD=Subs;password=sixears;packet size=4096"
Try
AddHandler SqlConnection1.InfoMessage, New
SqlInfoMessageEventHandler(AddressOf OnInfoMessage)
SqlConnection1.ConnectionString = strConnectionString
SqlConnection1.Open()
tnn = SqlConnection1.BeginTransaction()
SqlCommand1.Transaction = tnn
SqlCommand1.CommandText =
"dbo.proc_Bajio_SalesPosting03022006"
SqlCommand1.ExecuteNonQuery()
' SqlCommand1.CommandText =
"dbo.proc_Bajio_SalesPosting03022006_dropme"
' SqlCommand1.ExecuteNonQuery()
' SqlHelper.ExecuteNonQuery(tnn,
SqlCommand1.CommandText, SqlHelper.enuTimeOut.Default, Nothing)

tnn.Commit()
Catch Ex As SqlException
If ex.Number <> 266 Then
tnn.Rollback()
End If
Catch Ex As Exception
tnn.Rollback()

Finally
If Not tnn Is Nothing Then tnn.Dispose()
SqlConnection1.Close()
End Try
End Sub

Private Sub OnInfoMessage(ByVal sender As Object, ByVal args As
SqlInfoMessageEventArgs)
Try
Dim err As SqlError
For Each err In args.Errors
RichTextBox1.Text += ControlChars.CrLf & err.Message
Next
Catch ex As Exception

End Try
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Dim strConn As String =
"Server=jalapeno;Database=Northwind;Integrated Security=True;"
Dim cnn As New SqlConnection(strConn)
Dim cmd As New SqlCommand
cnn.Open()
Dim trans As SqlTransaction = Nothing
Try
trans = cnn.BeginTransaction()

' @AccountNo (From Account)
Dim paramFromAcc As New SqlParameter("@AccountNo",
SqlDbType.Char, 20)
paramFromAcc.Value = "12345"

' @AccountNo (To Account)
Dim paramToAcc As New SqlParameter("@AccountNo",
SqlDbType.Char, 20)
paramToAcc.Value = "67890"

' @Money (Credit amount)
Dim paramCreditAmount As New SqlParameter("@Amount",
SqlDbType.Money)
paramCreditAmount.Value = 500

' @Money (Debit amount)
Dim paramDebitAmount As New SqlParameter("@Amount",
SqlDbType.Money)
paramDebitAmount.Value = 500

With cmd
.Connection = cnn
.CommandType = CommandType.StoredProcedure
.Transaction = trans
.CommandText = "Debit"
.Parameters.Add(paramFromAcc)
.Parameters.Add(paramDebitAmount)
.ExecuteNonQuery()
End With

With cmd
.Connection = cnn
.CommandType = CommandType.StoredProcedure
.Transaction = trans
.CommandText = "Credit"
.Parameters.Add(paramToAcc)
.Parameters.Add(paramCreditAmount)
.ExecuteNonQuery()
End With
trans.Commit()
Catch ex As Exception
' throw an exception
trans.Rollback()
Throw ex
Finally
If Not trans Is Nothing Then trans.Dispose()
End Try
End Sub
End Class