Why Am I Getting Error Using TransactionScope?

Julio Bello 221 Reputation points
2020-11-21T04:37:00.397+00:00

Hi, Everybody!

I am having trouble with TransactionScope. I want my web method to be transactional. If any exception occurs, I want all database changes rolled back. Otherwise, commit. Please see error message below.

System.ApplicationException: Error in someClass :: Method public returnType methodName(parameterType parameter) :
System.ApplicationException: Error in someClass :: Method private string someMethod() :
System.Data.Odbc.OdbcException (0x80131937):
ERROR [08003] [Sybase][ODBC Driver]Connection not open
at System.Data.Odbc.OdbcConnection.Open_EnlistTransaction(Transaction transaction)
at System.Data.Odbc.OdbcConnectionOpen.EnlistTransaction(Transaction transaction)
at System.Data.Odbc.OdbcConnection.EnlistTransaction(Transaction transaction)
at System.Data.Odbc.OdbcConnection.Open()
at someNameSpace.someMethod() in c:\somePath\someClass.cs:line 35

The following is an excerpt of my code. What am I doing wrong?

        [WebMethod]
        public returnType methodName(parameterType parameter)
        {
            using (var transactionScope = new TransactionScope(TransactionScopeOption.RequiresNew))
            {
                try
                {
                    var someValue = someMethod();
                        :
                        :
                        :

                    transactionScope.Complete();

                    return response;
                }
                catch (Exception ex)
                {
                    return ErrorMessages(ex);
                }
            }
        }

        private string someMethod()
        {
            var commandText = "...some valid SQL expression...";
            var commandType = CommandType.Text;

            try
            {
                using (var odbcConnection = new OdbcConnection(DefaultDbConnection.ToString()))
                {
                    using (var odbcCommand = new OdbcCommand(commandText, odbcConnection))
                    {
                        odbcConnection.Open();  // Exception is thrown here!
                        odbcCommand.CommandType = commandType;

                        using (var reader = odbcCommand.ExecuteReader())
                        {
                            reader.Read();
                            return reader.GetString(0);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Error in someMethod.", ex);
            }
        }

Thank-you in advance for your help!

.NET
.NET
Microsoft Technologies based on the .NET software framework.
3,920 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
11,011 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Peter Fleischer (former MVP) 19,326 Reputation points
    2020-11-21T06:42:54.197+00:00

    Hi Julio,
    your ConnectionString is invalid to open SyBase database. To check the connection create on desktop an new text document, rename the full link name to TestSQL.udl, double click the new renamed link item, select Odbc, select the odbc name und click "test connection".


  2. Nuno Pereira 1 Reputation point
    2020-11-26T13:13:29.98+00:00

    I'm guessing this is related with the driver you are using.
    You can try the suggestion from @Viorel to check if the driver, database and connection string you are using supports transactions.
    It can also happens that your driver lacks proper support for TransactionScope.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.