Why Am I Getting Error Using TransactionScope?

Question

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!

Answer 1

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".

Answer 2

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.