ASP.NET

Strange error in my WEB APP RSS

4 replies

Last post Feb 23, 2013 07:07 AM by hikaru1207

‹ Previous Thread|Next Thread ›

- Reply
hikaru1207

Member

1 Points

17 Posts

Strange error in my WEB APP

Feb 22, 2013 03:12 PM|LINK

Hi Experts,

Always having this problem in all my WEB APP that I created. I think I have a problem in my coding. I'm using ASP.NET as platform for this and SQL Server R2 2008 for my database. Here's my error. When more than 5 user are currently using the WEB APP, when doing simultaneous transaction.

"The transaction is either not associated with the current connection or has been completed."

I don't know where to start with this. Hope to hear positive feedback for this.

Regards,
- Reply
ignatandrei

All-Star

134491 Points

21566 Posts

Moderator
MVP

Re: Strange error in my WEB APP

Feb 22, 2013 07:12 PM|LINK

and some code,please?

My .NET blog : http://msprogrammer.serviciipeweb.ro/ |2012 Programmer tools

hikaru1207

Member

1 Points

17 Posts

Re: Strange error in my WEB APP

Feb 22, 2013 11:29 PM|LINK

Hi Sir,

Here's a sample code. Hope you get it. In this command, they do update for some records.

Sub cmdUpdate_onClick(Obj As Object, e As EventArgs) Handles cmdUpdate.Click
		Try
			Dim oFmasDoc As New WEB_Library.oDocuments(121)
			Dim RequestType As Long
			Dim oAppId(20) As String
			Dim oAppNo(20) As String
			Dim oCtr As Integer = 1
			Dim oDup As String
			oFmasDoc.StartTransaction
			Select AppType.Text
				Case 120
					If Not oSaveLines Then Return
			 
				Case 122, 124
					Dim dgObj As DataGrid
					HasExistingRequest = ""
					For oTab As Integer = 2 To 2
						dgObj = dgActivated(oTab)
						If AppType.Text = 124 Then
							dgObj = dgActivated(4)
						End If
						Select GetQString("rtype")
							Case 0, 2
								If GetQString("report") = 0 Then
									WebCtrl.strCommand = "DELETE FROM temp1 WHERE Liquidator = '"& txtLiqId.Text &"' "
								End If
							Case Else
								If AppType.Text = 124 Then
									WebCtrl.strCommand = "DELETE FROM APP_UCA1 WHERE Liquidator = '"& GetUserId &"' "
								End IF
						End Select
	
						
						For Each oGridItem In dgObj.Items
							Dim oCheck As CheckBox = oGridItem.FindControl("chkSelect1")
							If oCheck.Checked = True Then
								Try
									If AppType.Text = 122 Then
										WebCtrl.strCommand = "SELECT Liq.AppNo, aa.IsLiquidated, Liq.LineTotal, Liq.ReqAmnt, DATEDIFF(HH, Liq.ModifiedDate, GETDATE()) FROM ["& WebCtrl.GetTableName(121) &"] aa INNER JOIN ["& WebCtrl.GetTableName(121, 2) &"] bb ON aa.AppId = bb.AppId INNER JOIN ["& WebCtrl.GetTableName(123, 2) &"] Liq ON aa.AppId = Liq.BaseAppId WHERE aa.IsLiquidated = 'N' AND aa.Liquidator = '"& txtLiqId.Text &"' AND bb.CompanyId = '"& GetCompanyId &"' AND (DATEDIFF(HH, Liq.ModifiedDate, GETDATE()) >= '24' ) AND aa.DocStatus = 2 AND Liq.LineTotal != Liq.ReqAmnt"
										If WebCtrl.dsCount() > 0 Then
											WebCtrl.setMessage("You have an Open Cash Advance subject for Liquidation.", 2)
											Return
										End If
									End If
																		 
									If AppType.Text = 124 Then
										WebCtrl.strCommand = "EXEC spFilterUnLiquidation '"& AppType.Text &"', '"& GetGridItem("AppId").Text &"', '"& dgObj.DataKeys(oGridItem.ItemIndex) &"', '"& GetCompanyId &"' "
										WebCtrl.strCommand = "EXEC spUpdateUnliquidate '" & GetGridItem("AppId").Text & "', '"& dgObj.DataKeys(oGridItem.ItemIndex) &"', "& GetGridItem("DiffAmnt").Text &", '"& GetCompanyId &"' "
									Else
										WebCtrl.strCommand = "EXEC spFilterLiquidation '"& AppType.Text &"', '"& GetGridItem("AppId").Text &"', '"& dgObj.DataKeys(oGridItem.ItemIndex) &"', '"& GetCompanyId &"' "
									End If
									
									'Response.Write(WebCtrl.strCommand)
								Catch Exp As Exception
									WebCtrl.SetMessage(Exp)
									Goto RollBack
								End Try
	' 'Response.Write(dgObj.DataKeys(oGridItem.ItemIndex))
								 
								With oFmasDoc
									Select GetQString("report")
										Case 0, 1
											 
											If .GetByAppId(GetGridItem("AppId").Text) Then
											
												If .Lines.GetByLineId(dgObj.DataKeys(oGridItem.ItemIndex)) Then
													WebCtrl.strCommand = "UPDATE APP_CAE1 SET IsPostedToSAP = 'Y', Liquidator = '"& Iif(cmbAlternate.Text = 1, txtLiqIdAltn.Text, txtLiqId.Text) &"', TAG = 1 WHERE AppId = '"& GetGridItem("AppId").Text &"' AND LineId = '"& dgObj.DataKeys(oGridItem.ItemIndex) &"' AND CompanyId = '"& GetCompanyId &"' "
													
													WebCtrl.strCommand = "UPDATE APP_REL1 SET IsPostedToSAP = 'Y', Liquidator = '"& Iif(cmbAlternate.Text = 1, txtLiqIdAltn.Text, txtLiqId.Text) &"', TAG = 1 WHERE AppId = '"& GetGridItem("AppId").Text &"' AND LineId = '"& dgObj.DataKeys(oGridItem.ItemIndex) &"' AND CompanyId = '"& GetCompanyId &"' "
													'Response.Write(WebCtrl.strCommand)
											 
												End If
											
											End If
									End Select
								End With
								'End If
								
								oCtr += 1
								oAppId(oCtr) = GetGridItem("AppId").Text
								oAppNo(oCtr) = GetGridItem("AppNo").Text
							End If
							
						Next
						
						ReDim Preserve oAppId(oCtr)
						ReDim Preserve oAppNo(oCtr)
	
						For oCtr = 0 To oAppId.Length - 1
	
							If oAppId(oCtr) = oDup Then
								
							Else
								oDup = oAppId(oCtr)
								'Update Table
								Select AppType.Text
									Case 124
									
									Case Else
										WebCtrl.strCommand = "UPDATE APP_OCAE SET ReleaseDate = GetDate(), ReleaseBy = '"& GetUserId &"' WHERE AppId = '"& oDup &"' AND CompanyId = '"& GetCompanyId &"' "
								
										WebCtrl.strCommand = "UPDATE APP_OREL SET ReleaseDate = GetDate(), ReleaseBy = '"& GetUserId &"' WHERE AppId = '"& oDup &"' AND CompanyId = '"& GetCompanyId &"' "
								End Select
								 
								If GetQString("rtype") = 0 And GetQString("report") <> 1 Then
									If Not HasApprovers(oFmasDoc, AppType.Text, oDup, 0, GetCompanyId, , 1) Then
										Goto RollBack
									End If
								End If
											
							End If
							 
						Next
						
						oDup = ""
						
						'Update SAP Table
						If GetQString("rtype") = 2 And cmbAlternate.Text = 1 Then
							For oCtr = 0 To oAppNo.Length - 1
								
								If oAppNo(oCtr) = oDup Then
				
								Else
	
									oDup = oAppNo(oCtr)
									
									WebCtrl.strCommand = "SELECT oHeader.*, oDetails.U_APP_EmpCode FROM ["& GetCompanyId &"]..ODRF oHeader INNER JOIN ["& GetCompanyId &"]..DRF1 oDetails ON oHeader.DocEntry = oDetails.DocEntry WHERE oHeader.DocStatus = 'O' AND oDetails.U_APP_CANumber = '"& oDup &"' "
									If WebCtrl.dsCount() > 0 Then
										WebCtrl.strCommand = "UPDATE ["& GetCompanyId &"]..DRF1 Set U_APP_EmpCode = '"& txtLiqId.Text &"' WHERE oHeader.DocStatus = 'O' AND oDetails.U_APP_CANumber = '"& oDup &"' "
									Else
										WebCtrl.strCommand = "SELECT oHeader.*, oDetails.U_APP_EmpCode FROM ["& GetCompanyId &"]..OPCH oHeader INNER JOIN ["& GetCompanyId &"]..PCH1 oDetails ON oHeader.DocEntry = oDetails.DocEntry WHERE oHeader.DocStatus = 'O' AND oDetails.U_APP_CANumber = '"& oDup &"' "
										If WebCtrl.dsCount() > 0 Then
											WebCtrl.strCommand = "UPDATE ["& GetCompanyId &"]..PCH1 Set U_APP_EmpCode = '"& txtLiqId.Text &"' WHERE oHeader.DocStatus = 'O' AND oDetails.U_APP_CANumber = '"& oDup &"' "
										End If
									End If
	
								End If
							Next
						End If
					Next
				Case Else
RollBack:
					oFmasDoc.Rollback_Transaction
					Return
			End Select
Commit:
			WebCtrl.SetMessage("Successfully Updated.")
			oFmasDoc.Commit_Transaction()
		 	 
			Call SetGrid()
		Catch Exp As Exception
			WebCtrl.SetMessage(Exp)
		End Try
	End Sub

If you notice the WebCtrl.strCommand, it is a function in my DLL.

    Private Shared oSqlConnection As SqlConnection
    Private Shared oSqlDataAdapter As SqlDataAdapter
    Private Shared oSqlCommand As SqlCommand
    Private Shared oSqlTransaction As SqlTransaction

Public Sub New() '(Optional ByVal oHttpRequest As Web.HttpContext = Nothing)
        oSqlConnection = New SqlConnection("initial catalog=" & MainDb & "; data source=" & ServerName & "; user id=" & SqlID & "; password=" & SqlPassword & "; Application Name=WEB-APP; MultipleActiveResultSets=True")
        Dim oString As String = oSqlConnection.ConnectionString
    End Sub 

    Private Shared _strCommand As String

    Public Shared Property strCommand() As String
        Get
            Return _strCommand
        End Get

        Set(ByVal value As String)
            If Not InTransaction Then RollBack_Transaction()
            _strCommand = "SET DATEFORMAT mdy " & vbCrLf & value
            DbQuery()
        End Set
    End Property

    Protected Shared Sub DbQuery()
        'Try

        If Not InTransaction Then
            RollBack_Transaction()

            oSqlCommand = New SqlCommand(_strCommand, oSqlConnection)
        Else
            oSqlCommand = oSqlConnection.CreateCommand
            If _InTransaction_Initial Then
                oSqlConnection.Open()
                oSqlTransaction = oSqlConnection.BeginTransaction(IsolationLevel.ReadCommitted)
                _InTransaction_Initial = False
            End If

            oSqlCommand.Transaction = oSqlTransaction
            oSqlCommand.CommandText = _strCommand
            oSqlCommand.CommandTimeout = 0
        End If

        oSqlDataAdapter = New SqlDataAdapter(oSqlCommand)
        Ds = New DataSet
        oSqlDataAdapter.Fill(Ds)

        'Catch ex As Exception
        '    SetMessage(ex)
        '    ' MsgBox("Server: " & ServerName & vbCrLf & "User Id: " & SqlID & vbCrLf & "Password: " & SqlPassword & vbCrLf & "Database: " & MainDb) 
        'End Try

        'oSqlConnection.Close()

    End Sub

    Private Shared _InTransaction_Initial As Boolean = False
    Private Shared InTransaction As Boolean

    Public Shared Sub StartTransaction()
        _InTransaction_Initial = True
        InTransaction = True
    End Sub
    Public Shared Sub RollBack_Transaction()
        Try
            oSqlTransaction.Rollback()
        Catch ex As Exception
        End Try
        Try
            oSqlConnection.Close()
        Catch ex As Exception
        End Try

        InTransaction = False
    End Sub

    Public Shared Sub Commit_Transaction()
        Try
            oSqlTransaction.Commit()
        Catch ex As Exception
        End Try
        Try
            oSqlConnection.Close()
        Catch ex As Exception 
        End Try

        InTransaction = False
    End Sub

Hope to hear you response soon.

Thanks,

- Reply
ignatandrei

All-Star

134491 Points

21566 Posts

Moderator
MVP

Re: Strange error in my WEB APP

Feb 23, 2013 04:38 AM|LINK

too contrived code - and I do not see where yo you begin transaction - just where commit or rollback.

Re-factor the code.

My .NET blog : http://msprogrammer.serviciipeweb.ro/ |2012 Programmer tools

hikaru1207

Member

1 Points

17 Posts

Re: Strange error in my WEB APP

Feb 23, 2013 07:07 AM|LINK

Hi Sir,

Here's the start of the transaction.

Dim oFmasDoc As New WEB_Library.oDocuments(121)
                        Dim RequestType As Long
                        Dim oAppId(20) As String
                        Dim oAppNo(20) As String
                        Dim oCtr As Integer = 1
                        Dim oDup As String
                        oFmasDoc.StartTransaction

Regards,

‹ Previous Thread|Next Thread ›

Strange error in my WEB APP RSS

4 replies

hikaru1207

Strange error in my WEB APP

ignatandrei

Re: Strange error in my WEB APP

hikaru1207

Re: Strange error in my WEB APP

ignatandrei

Re: Strange error in my WEB APP

hikaru1207

Re: Strange error in my WEB APP

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft