Database

<br />
DECLARE	@ValidationCode INTEGER<br />
BEGIN TRAN<br />
SET @ValidationCode = 0<br />
EXEC sp_CreateSupplier 1, 'M', 1, 'Golden Lucky and Associates', 'Golden Lucky and Associates', NULL, NULL, 'A', 3, 8, @ValidationCode OUTPUT<br />
PRINT 'Validation code: ' + CAST (@ValidationCode AS VARCHAR(10))<br />
<br />
... do other stuff...<br />
<br />
COMMIT TRAN<br />
<br />
CREATE PROCEDURE sp_CreateSupplier(<br />
	@supplier_no INTEGER,<br />
	@type VARCHAR(8),<br />
	@group_no INTEGER = NULL,<br />
	@short_name VARCHAR(50) = NULL,<br />
	@full_name VARCHAR(100) = NULL,<br />
	@address VARCHAR(100) = NULL,<br />
	@country_code VARCHAR(10) = NULL,<br />
	@status VARCHAR(8) = NULL,<br />
	@createBy INTEGER,<br />
	@ownerGroup INTEGER,<br />
	@ValidationCode INTEGER OUTPUT<br />
)<br />
AS<br />
BEGIN<br />
<br />
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE<br />
BEGIN TRANSACTION<br />
<br />
	-- STEP 1: Validation<br />
	SET @ValidationCode = 0<br />
	IF EXISTS(SELECT * FROM fe_supplier WHERE supplier_no=@supplier_no)<br />
		BEGIN<br />
		SET @ValidationCode = -10<br />
		ROLLBACK TRANSACTION<br />
		RETURN<br />
		END<br />
         ...<br />
COMMIT TRAN<br />
END<br />

Server: Msg 266, Level 16, State 2, Procedure sp_CreateSupplier, Line 26<br />
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.

Public Function Create(ByVal sup As vendormanagement.to.Supplier, ByVal tran As IDbTransaction) As vendormanagement.to.Supplier<br />
<br />
  Dim sql As String = "sp_CreateSupplier"<br />
  Dim cmd As IDbCommand<br />
  Dim rdr As IDataReader<br />
  Dim param As IDataParameter<br />
  Dim factory As IDbProviderFactory<br />
  Dim validationCode As Int32 = 0<br />
<br />
  Try<br />
    If _conn.State <> ConnectionState.Open Then<br />
       _conn.Open()<br />
    End If<br />
<br />
    factory = apputil.GlobalVariables.ProviderFactory<br />
    cmd = factory.CreateCommand(sql, Conn)<br />
    cmd.CommandType = CommandType.StoredProcedure<br />
    cmd.Transaction = tran<br />
<br />
    ...<br />
    rdr = cmd.ExecuteReader()<br />
    ...<br />
   Catch<br />
    ...<br />
   Finally<br />
      ...<br />
   End Try<br />
End Function

<br />
vconn = vFactory.CreateConnection(...)<br />
tran = vconn.BeginTransaction(...serializable...)<br />
SupplierBean.Create(..., tran)<br />
AnotherBean.Update(..., tran)<br />
tran.Commit()<br />