none
Commit之後出現的錯誤訊息 RRS feed

  • 問題

  • :::小女子利用ADO.net來Insert資料到SQL Server 2000時,出現

    這個 SqlTransaction 已經完成,它已不再是個可使用的項目。

    這個錯誤訊息,請各位該如何解決以及為何會發生此種情況,謝謝!!!

    程式如下:

            Dim myconnection As SqlConnection
            Dim mycommand As SqlCommand
            Dim mytransaction As SqlTransaction
            Dim ConnectionString As String

     

            myconnection = New SqlConnection("server=idd12;database=sqltest;user id=sa;password=sa")

            myconnection.Open()


            mytransaction = myconnection.BeginTransaction()

     

            mycommand = New SqlCommand
            mycommand.Connection = myconnection
            mycommand.Transaction = mytransaction

            Try
                mycommand.CommandText = "insert into Employee(ID,FirstName,LastName) values (119, 'F','L')"
                mycommand.ExecuteNonQuery()

                mytransaction.Rollback()

     

                Debug.WriteLine("Rollback()")

     

                mycommand.CommandText = "insert into Employee(ID,FirstName,LastName) values (119, 'F','L')"

                mytransaction.Commit()

     

                MessageBox.Show("Finish")

     

            Catch ex As Exception
                Debug.WriteLine(ex.Message)
            Finally
                myconnection.Close()
            End Try

    2008年1月8日 上午 09:54

解答

  • 發生這個錯誤訊息是在整個Transaction已經完成後(Commit or Rollback),又再次呼叫到Commit  or Rollback

    您的Roollback應該放在Catch Block裡面。

    2008年1月8日 上午 10:03
    版主
  • 程式碼區塊

    mycommand.CommandText = "insert into Employee(ID,FirstName,LastName) values (119, 'F','L')"
                mycommand.ExecuteNonQuery()

                mytransaction.Rollback()<--SqlTransaction 已經完成

     

                Debug.WriteLine("Rollback()")

     

                mycommand.CommandText = "insert into Employee(ID,FirstName,LastName) values (119, 'F','L')"

                mytransaction.Commit()<--這個 SqlTransaction 已經完成,它已不再是個可使用的項目

     

                MessageBox.Show("Finish")

     

     

    通常寫法

    程式碼區塊

    Dim myconnection As SqlConnection
            Dim mycommand As SqlCommand
            Dim mytransaction As SqlTransaction
            Dim ConnectionString As String

     

            myconnection = New SqlConnection("server=idd12;database=sqltest;user id=sa;password=sa")

            myconnection.Open()


            mytransaction = myconnection.BeginTransaction()

     

            mycommand = New SqlCommand
            mycommand.Connection = myconnection
            mycommand.Transaction = mytransaction

            Try
                mycommand.CommandText = "insert into Employee(ID,FirstName,LastName) values (119, 'F','L')"
                mycommand.ExecuteNonQuery()

                           

                mytransaction.Commit()

     

                MessageBox.Show("Finish")

     

            Catch ex As Exception

                mytransaction.Rollback()
                Debug.WriteLine(ex.Message)
            Finally
                myconnection.Close()
            End Try

     

     

    2008年1月8日 上午 10:43
  • 小琳姐~

     

    不知你為何要在 Commit() 前下 Rollback() ?

    如果要讓這個函式可發生作用,也許可以修改成:

     

            Dim myconnection As SqlConnection
            Dim mycommand As SqlCommand
            Dim mytransaction As SqlTransaction
            Dim ConnectionString As String

     

            myconnection = New SqlConnection("server=idd12;database=sqltest;user id=sa;password=sa")

            myconnection.Open()


            mytransaction = myconnection.BeginTransaction()

     

            mycommand = New SqlCommand
            mycommand.Connection = myconnection
            mycommand.Transaction = mytransaction

            Try
                mycommand.CommandText = "insert into Employee(ID,FirstName,LastName) values (119, 'F','L')"
                mycommand.ExecuteNonQuery()

                mytransaction.Rollback()

     

                Debug.WriteLine("Rollback()")

     

                mytransaction = myconnection.BeginTransaction()

                mycommand.CommandText = "insert into Employee(ID,FirstName,LastName) values (119, 'F','L')"

                mytransaction.Commit()

     

                MessageBox.Show("Finish")

     

            Catch ex As Exception
                Debug.WriteLine(ex.Message)
            Finally
                myconnection.Close()
            End Try

     

    但我認為你只是測試用 XD

    因為以你的程度,我不認為你會出這種錯 ...

     

    2008年1月8日 下午 01:35
    版主

所有回覆

  • 發生這個錯誤訊息是在整個Transaction已經完成後(Commit or Rollback),又再次呼叫到Commit  or Rollback

    您的Roollback應該放在Catch Block裡面。

    2008年1月8日 上午 10:03
    版主
  • 程式碼區塊

    mycommand.CommandText = "insert into Employee(ID,FirstName,LastName) values (119, 'F','L')"
                mycommand.ExecuteNonQuery()

                mytransaction.Rollback()<--SqlTransaction 已經完成

     

                Debug.WriteLine("Rollback()")

     

                mycommand.CommandText = "insert into Employee(ID,FirstName,LastName) values (119, 'F','L')"

                mytransaction.Commit()<--這個 SqlTransaction 已經完成,它已不再是個可使用的項目

     

                MessageBox.Show("Finish")

     

     

    通常寫法

    程式碼區塊

    Dim myconnection As SqlConnection
            Dim mycommand As SqlCommand
            Dim mytransaction As SqlTransaction
            Dim ConnectionString As String

     

            myconnection = New SqlConnection("server=idd12;database=sqltest;user id=sa;password=sa")

            myconnection.Open()


            mytransaction = myconnection.BeginTransaction()

     

            mycommand = New SqlCommand
            mycommand.Connection = myconnection
            mycommand.Transaction = mytransaction

            Try
                mycommand.CommandText = "insert into Employee(ID,FirstName,LastName) values (119, 'F','L')"
                mycommand.ExecuteNonQuery()

                           

                mytransaction.Commit()

     

                MessageBox.Show("Finish")

     

            Catch ex As Exception

                mytransaction.Rollback()
                Debug.WriteLine(ex.Message)
            Finally
                myconnection.Close()
            End Try

     

     

    2008年1月8日 上午 10:43
  • 小琳姐~

     

    不知你為何要在 Commit() 前下 Rollback() ?

    如果要讓這個函式可發生作用,也許可以修改成:

     

            Dim myconnection As SqlConnection
            Dim mycommand As SqlCommand
            Dim mytransaction As SqlTransaction
            Dim ConnectionString As String

     

            myconnection = New SqlConnection("server=idd12;database=sqltest;user id=sa;password=sa")

            myconnection.Open()


            mytransaction = myconnection.BeginTransaction()

     

            mycommand = New SqlCommand
            mycommand.Connection = myconnection
            mycommand.Transaction = mytransaction

            Try
                mycommand.CommandText = "insert into Employee(ID,FirstName,LastName) values (119, 'F','L')"
                mycommand.ExecuteNonQuery()

                mytransaction.Rollback()

     

                Debug.WriteLine("Rollback()")

     

                mytransaction = myconnection.BeginTransaction()

                mycommand.CommandText = "insert into Employee(ID,FirstName,LastName) values (119, 'F','L')"

                mytransaction.Commit()

     

                MessageBox.Show("Finish")

     

            Catch ex As Exception
                Debug.WriteLine(ex.Message)
            Finally
                myconnection.Close()
            End Try

     

    但我認為你只是測試用 XD

    因為以你的程度,我不認為你會出這種錯 ...

     

    2008年1月8日 下午 01:35
    版主
  • 如果有用到.ExecuteReader方法,又下了(CommandBehavior.CloseConnection)參數,當執行SqlDataReader.Close()時,SqlTransaction就死了,因為它的SqlConnection已經變成Nothing了,因此,使用.ExecuteReader方法時,應將(CommandBehavior.CloseConnection)去掉。

    ecbt

    2013年6月9日 下午 04:00