[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Error on submit on newly rolled out website: This SQLTransaction has completed; it is no longer usable.

Posted on 2009-02-23
1
Medium Priority
?
365 Views
Last Modified: 2013-11-26
Hi Experts,

I have just rolled out my first website and am encountering a number of errors that did not occur when I ran the website on my local host.

The latest occurs when I hit the submit button on the page.

I have attached the error message as a bitmap file.

I have posted the relevant section of code in the code section.

Thanks in advance for your help.

taduh



Sub Update_Order_Data()
 
        'Define the connection to the database and open it.
        Dim cnUpdCDARS As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("CDARSConnectionString").ConnectionString)
        Dim UpdTrans As SqlTransaction
        cnUpdCDARS.Open()
        UpdTrans = cnUpdCDARS.BeginTransaction
 
        'Insert the data from the webform into the Orders table.
        Dim UpdCmd As New SqlCommand("IF EXISTS(SELECT 1 FROM tbl_Orders Where Order_ID = @OrderID)" & _
                                            " UPDATE tbl_Orders " & _
                                            " Set Order_Type_Code = @OrderType," & _
                                                 "Order_CDARS_Account_Number = @CDARSAcctNum," & _
                                                 "Order_Date = @OrderDate," & _
                                                 "Order_Institution_No = @Institution," & _
                                                 "Order_Branch_No = @Location," & _
                                                 "Order_Customer1_TaxID = @TaxID1," & _
                                                 "Order_Customer1_Portfolio_Number = @Port1," & _
                                                 "Order_Customer1_Class = @CustClass," & _
                                                 "Order_Customer2_Name = @Name2," & _
                                                 "Order_Customer2_TaxID = @TaxID2," & _
                                                 "Order_Customer2_DOB = @DOB2," & _
                                                 "Order_Debited_Account_Type_code = @FromAcctType," & _
                                                 "Order_Debited_Account_No = @FromAcctNum," & _
                                                 "Order_CDARS_Account_Title = @AcctTitle," & _
                                                 "Order_Contact_First_Name = @ContactFName," & _
                                                 "Order_Contact_Last_Name = @ContactLName," & _
                                                 "Order_Contact_MI = @ContactMI," & _
                                                 "Order_Contact_Phone_No = @ContactPhone," & _
                                                 "Order_Amount = @Amount," & _
                                                 "Order_Placement_Date = @DatePlaced," & _
                                                 "Order_APR_Rate = @APRRate," & _
                                                 "Order_APY_Rate = @APYRate," & _
                                                 "Order_Term_Code = @Term," & _
                                                 "Order_Interest_Pay_Freq_Code = @IntPayFreq," & _
                                                 "Order_Interest_Paid_Method_Code = @IntPayVia," & _
                                                 "Order_Interest_Paid_Account_No = @IntPayAcctNum," & _
                                                 "Order_Cust_Sig = @CustSig," & _
                                                 "Order_Comments = @Comments " & _
                                       "Where Order_ID = @OrderID;", cnUpdCDARS, UpdTrans)
        With UpdCmd
            .Parameters.Add("@OrderID", SqlDbType.BigInt).Value = txtboxOrderNum.Text
            .Parameters.Add("@OrderType", SqlDbType.VarChar).Value = radioOrderType.SelectedValue
            .Parameters.Add("@CDARSAcctNum", SqlDbType.VarChar).Value = txtboxCDARSAcctNum.Text
            .Parameters.Add("@Institution", SqlDbType.VarChar).Value = dropdownInst.SelectedValue
            .Parameters.Add("@Location", SqlDbType.VarChar).Value = dropdownLocations.SelectedValue
 
            'Must convert the date in the textbox from Text to DateTime. If not a date, fill with current date/time.
            If IsDate(txtboxDate.Text) Then
                .Parameters.Add("@OrderDate", SqlDbType.DateTime).Value = DateTime.Parse(txtboxDate.Text)
            Else
                .Parameters.Add("@OrderDate", SqlDbType.DateTime).Value = Now
            End If
 
            .Parameters.Add("@TaxID1", SqlDbType.VarChar).Value = txtboxTaxID1.Text
            .Parameters.Add("@Port1", SqlDbType.VarChar).Value = txtboxPort.Text
            .Parameters.Add("@Name2", SqlDbType.VarChar).Value = txtboxName2.Text
            .Parameters.Add("@TaxID2", SqlDbType.VarChar).Value = txtboxTaxID2.Text
 
            'Must convert the date in the textbox from Text to DateTime. If not a date, fill with null.
            If IsDate(txtboxDOB2.Text) Then
                .Parameters.Add("@DOB2", SqlDbType.DateTime).Value = DateTime.Parse(txtboxDOB2.Text)
            Else
                .Parameters.Add("@DOB2", SqlDbType.DateTime).Value = System.DBNull.Value
            End If
 
            .Parameters.Add("@CustClass", SqlDbType.Char).Value = RadioCustClass.SelectedValue
            .Parameters.Add("@FromAcctType", SqlDbType.VarChar).Value = RadioFromAcctType.SelectedValue
            .Parameters.Add("@FromAcctNum", SqlDbType.VarChar).Value = txtboxFromAcctNum.Text
            .Parameters.Add("@AcctTitle", SqlDbType.VarChar).Value = txtboxAcctTitle.Text
            .Parameters.Add("@ContactLName", SqlDbType.VarChar).Value = txtboxContactLName.Text
            .Parameters.Add("@ContactFName", SqlDbType.VarChar).Value = txtboxContactFName.Text
            .Parameters.Add("@ContactMI", SqlDbType.VarChar).Value = txtboxContactMI.Text
            .Parameters.Add("@ContactPhone", SqlDbType.VarChar).Value = txtboxContactPhone.Text
 
            'Must convert textbox amount from text to decimal. Just in case a blank slips by,
            'convert to zero.
            If IsNumeric(TxtboxAmt.Text) Then
                .Parameters.Add("@Amount", SqlDbType.Decimal).Value = Decimal.Parse(TxtboxAmt.Text)
            Else
                .Parameters.Add("@Amount", SqlDbType.Decimal).Value = 0
                MsgBox("Amount must contain a numeric amount > 0 - temporarily filled with zero.")
            End If
 
            'Must convert the date in the textbox from Text to DateTime. If not a date,
            'fill with current date/time.
            If IsDate(txtboxDatePlaced.Text) Then
                .Parameters.Add("@DatePlaced", SqlDbType.DateTime).Value = DateTime.Parse(txtboxDatePlaced.Text)
            Else
                .Parameters.Add("@DatePlaced", SqlDbType.DateTime).Value = Today
                MsgBox("Placement Date must be filled in with a valid date - temporarily filled with current date.")
            End If
 
            'If APR is not filled in or is zero, want to set database field to null; otherwise 
            'convert the value in the webform field to decimal and store in DB.
            If (Not IsNumeric(txtboxAPR.Text)) Then
                .Parameters.Add("@APRRate", SqlDbType.Decimal).Value = System.DBNull.Value
            ElseIf CType(txtboxAPR.Text, Decimal) = 0 Then
                .Parameters.Add("@APRRate", SqlDbType.Decimal).Value = System.DBNull.Value
            Else
                .Parameters.Add("@APRRate", SqlDbType.Decimal).Value = Decimal.Parse(txtboxAPR.Text)
            End If
 
            'If APY is not filled in or is zero, want to set database field to null; otherwise 
            'convert the value in the webform field to decimal and store in DB.
            If (Not IsNumeric(txtboxAPY.Text)) Then
                .Parameters.Add("@APYRate", SqlDbType.Decimal).Value = System.DBNull.Value
            ElseIf CType(txtboxAPY.Text, Decimal) = 0 Then
                .Parameters.Add("@APYRate", SqlDbType.Decimal).Value = System.DBNull.Value
            Else
                .Parameters.Add("@APYRate", SqlDbType.Decimal).Value = Decimal.Parse(txtboxAPY.Text)
            End If
 
            .Parameters.Add("@Term", SqlDbType.VarChar).Value = RadioTerm.SelectedValue
            .Parameters.Add("@IntPayFreq", SqlDbType.VarChar).Value = RadioIntPayFreq.SelectedValue
            .Parameters.Add("@IntPayVia", SqlDbType.VarChar).Value = dropdownIntPayVia.SelectedValue
            .Parameters.Add("@IntPayAcctNum", SqlDbType.VarChar).Value = txtboxIntPayAcctNum.Text
            .Parameters.Add("@CustSig", SqlDbType.VarChar).Value = chkboxCustSig.Checked.ToString
            .Parameters.Add("@Comments", SqlDbType.VarChar).Value = txtboxComments.Text
        End With
 
        Try
            UpdCmd.ExecuteNonQuery()
            UpdTrans.Commit()
            MsgBox("Order data has been updated for Order # " & txtboxOrderNum.Text)
        Catch ex As Exception
            UpdTrans.Rollback()
            MsgBox("Update of existing Order Data in Order Table was not successful. Contact your System Administrator.")
            MsgBox(ex.Message.ToString)
        Finally
            If cnUpdCDARS.State = ConnectionState.Open Then cnUpdCDARS.Close()
        End Try
 
    End Sub

Open in new window

Snap2.bmp
0
Comment
Question by:taduh
1 Comment
 

Accepted Solution

by:
taduh earned 0 total points
ID: 23715740
This error had to do with the fact that I needed to actually put the the Catch statement within a Try when I was doing a rollback. I did this and it solved the problem.
  Try
            UpdCmd.ExecuteNonQuery()
            UpdTrans.Commit()
            Console.WriteLine("Order data has been updated for Order # " & txtboxOrderNum.Text)
        Catch ex As Exception
            Try
                UpdTrans.Rollback()
                Console.WriteLine("Update of existing Order Data in Order Table was not successful. Contact your System Administrator.")
                Console.WriteLine(ex.Message.ToString)
            Catch ex2 As Exception
                Console.WriteLine("Update transaction & subsequent rollback failed. Contact your System Administrator.")
                Console.WriteLine(ex2.Message.ToString)
            End Try
        Finally
            If cnUpdCDARS.State = ConnectionState.Open Then cnUpdCDARS.Close()
        End Try

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question