OleDb Transaction

i guess the error was caused be some statement before instantiating the transaction variable "InTrans" and inside  catch block you were trying to rollback before starting the transaction. try to separate the try block for the transaction part or check InTrans for nothing before trying to roll back in the catch block like

........
........
                If found = 0 Then
                    Try
                                        InTrans = Con.BeginTransaction()
                                        InCommand.Connection = Con
                                        InCommand.Transaction = InTrans

                                        InCommand.CommandText = UpdateStr1
                                        InCommand.ExecuteNonQuery()
                   
                                        InCommand.CommandText = UpdateStr2
                                        InCommand.ExecuteNonQuery()
                   
                                        Response.Write("<script language='JavaScript'>alert('Spare Parts IN updated successfully!');</script>")

                                            InTrans.Commit()
                    Catch ex1 As Exception
                                        Try
                                                            InTrans.Rollback()
                                        Catch ex As OleDbException
                                                            If Not InTrans.Connection Is Nothing Then
                                                                                Console.WriteLine("An exception of type " & ex.GetType().ToString & _
                                                                                " was encountered while attempting to roll back the transaction.")
                                                            End If
                                        End Try

                                        Console.WriteLine("An exception of type " & e.GetType().ToString() & _
                                                            "was encountered while inserting the data.")
                                        Console.WriteLine("Neither record was written to database.")
                    End Try

                End If

            Catch ex1 As Exception
                Try
                    InTrans.Rollback()
                Catch ex As OleDbException
                    If Not InTrans.Connection Is Nothing Then
                        Console.WriteLine("An exception of type " & ex.GetType().ToString & _
                                          " was encountered while attempting to roll back the transaction.")
                    End If
                End Try

                'Catch ex As OleDbException
                Console.WriteLine("An exception of type " & e.GetType().ToString() & _
                            "was encountered while inserting the data.")
                Console.WriteLine("Neither record was written to database.")
            Finally
                Con.Close()
            End Try

I tried both solution but same error output I posted earlier retuned.

ayha

Ok. Looking over the issue more in depth, I see why appari gave a full code example.

The problem is that you're not structuring the Try Catch block correctly. In the code I've posted, I changed the try catch structure a bit. Try it out and see if you get any different output.


Dim InCommand As Data.OleDb.OleDbCommand = Con.CreateCommand()
Dim InTrans As Data.OleDb.OleDbTransaction

If Con.State <> ConnectionState.Open Then
      Con.Open()
End If

'This Try is for the connection itself
Try
      ' First retrieve the information and do your calculations
      Try
            rdr = cmd1.ExecuteReader

            If rdr.Read Then
                  NewTotalIN = rdr.Item("TotalIN")
                  If NewTotalIN = "" Or IsDBNull(NewTotalIN) Or Not IsNumeric(NewTotalIN) Then NewTotalIN = 0
                  Response.Write(NewTotalIN)
            End If
            rdr.Close()

            rdr = cmd2.ExecuteReader
            If rdr.Read Then
                  sum1 = rdr.Item("TotalOUT")
                  If sum1 = "" Or IsDBNull(sum1) Or Not IsNumeric(sum1) Then sum1 = 0
            End If
            rdr.Close()

            rdr = cmd3.ExecuteReader
            If rdr.Read Then
                  sum2 = rdr.Item("TotalOUT2")
                  If sum2 = "" Or IsDBNull(sum2) Or Not IsNumeric(sum2) Then sum2 = 0
            End If
            rdr.Close()

            rdr = cmd4.ExecuteReader
            If rdr.Read Then
                  txtROL = rdr.Item("ROL")
                  If txtROL = "" Or IsDBNull(txtROL) Or Not IsNumeric(txtROL) Then txtROL = 0
            End If
            rdr.Close()

            rdr = cmd5.ExecuteReader
            If rdr.Read Then
                  txtOldQtyIN = rdr.Item("QtyIn")
                  If txtOldQtyIN = "" Or IsDBNull(txtOldQtyIN) Or Not IsNumeric(txtOldQtyIN) Then txtOldQtyIN = 0
            End If
            rdr.Close()

            If ((NewTotalIN) - (txtOldQtyIN) + txtQtyIn) < ((sum1) + ((sum2) + txtROL)) Then
                  Response.Write("<script language='JavaScript'>alert('You cannot update.');</script>")
                  found = 1
            End If
      Catch ex1 As Exception
            Console.WriteLine("An exception of type " & e.GetType().ToString() & _
                  "was encountered while READING the data.")
      End Try

      'Then use a separate try catch block to write the information
      If found = 0 Then
            Try

                  InTrans = Con.BeginTransaction()
                  InCommand.Connection = Con
                  InCommand.Transaction = InTrans

                  InCommand.CommandText = UpdateStr1
                  InCommand.ExecuteNonQuery()

                  InCommand.CommandText = UpdateStr2
                  InCommand.ExecuteNonQuery()

                  Response.Write("<script language='JavaScript'>alert('Spare Parts IN updated successfully!');</script>")

                  InTrans.Commit()

            Catch ex1 As Exception
                  Try
                        InTrans.Rollback()
                  Catch ex As OleDbException
                        If Not InTrans.Connection Is Nothing Then
                              Console.WriteLine("An exception of type " & ex.GetType().ToString & _
                              " was encountered while attempting to roll back the transaction.")
                        Else
                              Console.WriteLine("InTrans.Connection Is Nothing when trying to rollback.")
                        End If
                  End Try

                  Console.WriteLine("An exception of type " & e.GetType().ToString() & _
                  "was encountered while inserting the data.")
                  Console.WriteLine("Neither record was written to database.")
            End Try
      End If

Finally
      Con.Close()
End Try

the output is same.

Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:
Line 338:                            InTrans.Rollback()

 ayha

Oops. I made one mistake.


Instead of:
          Try
               InTrans = Con.BeginTransaction()

Use:
          InTrans = Con.BeginTransaction()
               Try


Does that work?

now the error is;

BeginTransaction requires an open and available Connection. The connection's current state is Open, Fetching.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: BeginTransaction requires an open and available Connection. The connection's current state is Open, Fetching.

Source Error:

Line 337:                        InTrans = Con.BeginTransaction()

 
ayha

than u very much for the hlep.

ayha