"The operation is not valid for the state of the transaction" error and transaction scope

Question

I am getting a "The opertaion is not valid for the state of the transaction" error when I try to call a stored procedure that contains a SELECT Statement. Here is the structure of my calls

public void MyAddUpdateMethod()
{

    using (TransactionScope Scope = new TransactionScope(TransactionScopeOption.RequiresNew))
    {
        using(SQLServer Sql = new SQLServer(this.m_connstring))
        {
            //do my first add update statement

            //do my call to the select statement sp
            bool DoesRecordExist = this.SelectStatementCall(id)
        }
    }
}

public bool DoesRecordExist(System.Guid id)
{
    using(SQLServer Sql = new SQLServer(this.m_connstring)) //breaks on this line
    {
        //create parameters
        //
    }
}

Is the problem with me creating another connection to the same database within the transaction?

Answer 1

I've encountered this error when my Transaction is nested within another. Is it possible that the stored procedure declares its own transaction or that the calling function declares one?

Answer 2

After doing some research, I can not have two connections opened to the same database with the TransactionScope block. I need to modified my code to look like this:

public void MyAddUpdateMethod()
{
    using (TransactionScope Scope = new TransactionScope(TransactionScopeOption.RequiresNew))
    {
        using(SQLServer Sql = new SQLServer(this.m_connstring))
        {
            //do my first add update statement            
            //do my call to the select statement sp
        }

        //removed the method call from the first sql server using statement
        bool DoesRecordExist = this.SelectStatementCall(id)
    }
}

public bool DoesRecordExist(System.Guid id)
{
    using(SQLServer Sql = new SQLServer(this.m_connstring))
    {
        //create parameters
    }
}