title: Begin transaction in VB.net AND SQL Server 2008
body: <p>I begin a transaction in vb.net. I execute a stored procedure on SQL Server 2008. That stored procedure contains <code>BEGIN TRANSACTION</code>. It fails, and <code>ROLLBACK</code> in <code>CATCH</code> block runs...</p>
  
  <pre><code>BEGIN CATCH
  
      IF @@TRANCOUNT &gt; 1 ROLLBACK
          EXEC p_RethrowError
  
  END CATCH
  </code></pre>
  
  <p>Rethrow effectively does a 'raiserror'. </p>
  
  <p>Execution passes back to vb.net. Rollback in "Catch sqlException " executes.</p>
  
  <p>Questions:</p>
  
  <ul>
  <li><p>Why is <code>@@TRANCOUNT</code> 1 rather than 2? (i.e. how come <code>begin trans</code> in vb.net is not included?)</p></li>
  <li><p>Why does <code>ROLLBACK</code> in SQL not rollback client trans as well (but a rollback in client does rollback SQL Server)?</p></li>
  </ul>
  
  <p>And finally, in vb.net, if you try to rollback transaction twice within client, you get exception "transaction has completed". Is there anyway of knowing whether the transaction has completed, or is still pending? Thanks.</p>
  
  <p>----------- VB.Net Code</p>
  
  <pre><code>Public Sub sub1(ByVal intID As Integer,  ByVal intValue as integer, ByVal intAuditUser As Int16)
  
  
  Dim objConn As New SqlConnection(GetDBaseConnectionString())
  
      objConn.Open()
  
      '***** start the transaction ************************************************'
      Dim objTrans As SqlTransaction = objConn.BeginTransaction()
  
      Try
  
          Call sub2(objTrans, intID, intValue, intAuditUser)
  
          '***** commit the transaction ************************************************'
          objTrans.Commit()
  
      Catch es As SqlException
          objTrans.Rollback()
          Throw es
  
      Catch ex As Exception
          '***** rollback the transaction ************************************************'
          objTrans.Rollback()
          Throw ex
  
      Finally
          If objConn.State &lt;&gt; ConnectionState.Closed Then objConn.Close()
      End Try
  
  End Sub
  
  Private Sub Sub2(ByVal objTrans As SqlTransaction, ByVal intID As Integer, ByVal intValue as integer, ByVal intAuditUser As Int16)
  
      Dim objParams As New List(Of SqlParameter)
  
          SqlHelper.AddInParameter(objParams, "ID", SqlDbType.Int, intID)
          SqlHelper.AddInParameter(objParams, "Value", SqlDbType.Int, intValue)
          SqlHelper.AddInParameter(objParams, "AuditUser", SqlDbType.SmallInt, intAuditUser)
  
          '* save details'
          SqlHelper.ExecuteNonQuery(objTrans, CommandType.StoredProcedure, "p_StoredProc_UpdateSomething", objParams.ToArray)
  
  End Sub
  </code></pre>