Skip to main content

Notifications

Announcements

No record found.

Finance | Project Operations, Human Resources, ...
Suggested answer

Error in update TMP table Cannot select a record for update when the transaction is not started on the user connection attached.

(1) ShareShare
ReportReport
Posted on by 332

Hello experts

I face an error when I need to update TMP table

Cannot select a record in Trial balance (LedgerTrialBalanceListPageTmp).
Cannot select a record for update when the transaction is not started on the user connection attached. You need to begin transaction on the user connection first.

My full code is 

[SysEntryPointAttribute(false)]
public void processReport()
{
    CustInvoiceJour         jour,custJour;
    CustInvoiceTrans        trans;
    TransDate               fromDate,toDate;
    ZMLTrialBalanceContract  contract = new ZMLTrialBalanceContract();
    LedgerTrialBalanceDP trialBalanceDP = new LedgerTrialBalanceDP();
    LedgerTrialBalanceContract trialBalanceContract = new LedgerTrialBalanceContract();
    //LedgerTrialBalanceTmp trialBalanceTmp;

    DimensionHierarchy dimHier;
    UserConnection userConn;

    contract = this.parmDataContract();
    fromDate    = contract.parmFromDate();
    toDate      = contract.parmToDate();

    ttsBegin;
    dimHier = DimensionHierarchy::getMainAccountFocus();
    //DimensionFocusUpdateBalance::updateBalance(
        //DimensionHierarchy::findByTypeAndName(DimensionHierarchyType::Focus, dimHier.Name),true);
    userConn = new UserConnection();
    trialBalanceTmp.setConnection(userConn);
    _tmp.setConnection(userConn);
    trialBalanceContract.parmDetailSummary(DetailSummary::Summary);
    trialBalanceContract.parmFromDate(fromDate);
    trialBalanceContract.parmToDate(toDate);
    trialBalanceContract.parmIncludeOpening(False);
    trialBalanceContract.parmIncludeClosing(False);
    trialBalanceContract.parmOperationsTax(OperationsTax::Current);
    //trialBalanceContract.parmPrimaryDimensionFocus(dimHier.Name);//Arafa
        trialBalanceContract.parmPrimaryDimensionFocus("03");

    trialBalanceDP.parmDataContract(trialBalanceContract);
    trialBalanceDP.parmUserConnection(userConn);
    trialBalanceDP.processReport();

    _tmp.recordLevelSecurity(false);
    trialBalanceTmp = trialBalanceDP.getLedgerTrialBalanceTmp();

    insert_recordset _tmp (
                PrimaryFocus, LedgerDimension, OpeningBalance, AmountDebit, AmountCredit, ClosingBalance,
                    EndingBalance, Description, DimensionValues)
        select  PrimaryFocus, LedgerDimension, OpeningBalance, AmountDebit, AmountCredit, ClosingBalance,
                    EndingBalance, PrimaryFocusDescription, DimensionValues
        from trialBalanceTmp;
    ttsCommit;
    
    ttsBegin;
    while select forUpdate _tmp
    {
        _tmp.MainAccountId = MainAccount::findByLedgerDimension(_tmp.LedgerDimension).MainAccountId;
        _tmp.update();
    }
    ttsCommit;
    
    _tmp.recordLevelSecurity(true);
}

[SRSReportDataSetAttribute(tableStr(LedgerTrialBalanceListPageTmp))]
public LedgerTrialBalanceListPageTmp getTmpData()
{
    select _Tmp;
    return _Tmp;
}

The error happend when I add the while select forupdate , if I removed it the report view the data  without any problem 

Thanks all

  • Suggested answer
    Gunjan Bhattachayya Profile Picture
    Gunjan Bhattachayya 35,421 on at
    RE: Error in update TMP table Cannot select a record for update when the transaction is not started on the user connection attached.

    Hi Ahmed,

    I missed the part where you didn't place the code under userConn.ttsbegin()  and userConn.ttsCommit() statements. Please try the code below and see if that resolves your issue

    [SysEntryPointAttribute(false)]
    public void processReport()
    {
        CustInvoiceJour         jour,custJour;
        CustInvoiceTrans        trans;
        TransDate               fromDate,toDate;
        ZMLTrialBalanceContract  contract = new ZMLTrialBalanceContract();
        LedgerTrialBalanceDP trialBalanceDP = new LedgerTrialBalanceDP();
        LedgerTrialBalanceContract trialBalanceContract = new LedgerTrialBalanceContract();
        //LedgerTrialBalanceTmp trialBalanceTmp;
    	DimensionAttributeValueCombination  ledgerDimension;
    	MainAccount							mainAccount;
    
        DimensionHierarchy dimHier;
        UserConnection userConn;
    
        contract = this.parmDataContract();
        fromDate    = contract.parmFromDate();
        toDate      = contract.parmToDate();
    
        ttsBegin;
        dimHier = DimensionHierarchy::getMainAccountFocus();
        //DimensionFocusUpdateBalance::updateBalance(
            //DimensionHierarchy::findByTypeAndName(DimensionHierarchyType::Focus, dimHier.Name),true);
        userConn = new UserConnection();
        trialBalanceTmp.setConnection(userConn);
        _tmp.setConnection(userConn);
    	
    	userConn.ttsBegin();
    	
        trialBalanceContract.parmDetailSummary(DetailSummary::Summary);
        trialBalanceContract.parmFromDate(fromDate);
        trialBalanceContract.parmToDate(toDate);
        trialBalanceContract.parmIncludeOpening(False);
        trialBalanceContract.parmIncludeClosing(False);
        trialBalanceContract.parmOperationsTax(OperationsTax::Current);
        //trialBalanceContract.parmPrimaryDimensionFocus(dimHier.Name);//Arafa
            trialBalanceContract.parmPrimaryDimensionFocus("03");
    
        trialBalanceDP.parmDataContract(trialBalanceContract);
        trialBalanceDP.parmUserConnection(userConn);
        trialBalanceDP.processReport();
    
        _tmp.recordLevelSecurity(false);
        trialBalanceTmp = trialBalanceDP.getLedgerTrialBalanceTmp();
    
        insert_recordset _tmp (
                    PrimaryFocus, LedgerDimension, OpeningBalance, AmountDebit, AmountCredit, ClosingBalance,
                        EndingBalance, Description, DimensionValues)
            select  PrimaryFocus, LedgerDimension, OpeningBalance, AmountDebit, AmountCredit, ClosingBalance,
                        EndingBalance, PrimaryFocusDescription, DimensionValues
            from trialBalanceTmp;
    		
    	while select forUpdate _tmp
        {
            // Update the fields here.
            _tmp.update();
        }
    	
    	userConn.ttsCommit();
    	
        ttsCommit;
           
        _tmp.recordLevelSecurity(true);
    }

  • RE: Error in update TMP table Cannot select a record for update when the transaction is not started on the user connection attached.

    Sorry about the deley and thanks for your efort

    But same issue

  • Gunjan Bhattachayya Profile Picture
    Gunjan Bhattachayya 35,421 on at
    RE: Error in update TMP table Cannot select a record for update when the transaction is not started on the user connection attached.

    Hi Ahmed,

    Could you try to have the update code in the same ttsBegin/ttsCommit block as the insert_recordset statement and check if you get the same issue?

    [SysEntryPointAttribute(false)]
    public void processReport()
    {
        CustInvoiceJour         jour,custJour;
        CustInvoiceTrans        trans;
        TransDate               fromDate,toDate;
        ZMLTrialBalanceContract  contract = new ZMLTrialBalanceContract();
        LedgerTrialBalanceDP trialBalanceDP = new LedgerTrialBalanceDP();
        LedgerTrialBalanceContract trialBalanceContract = new LedgerTrialBalanceContract();
        //LedgerTrialBalanceTmp trialBalanceTmp;
    	DimensionAttributeValueCombination  ledgerDimension;
    	MainAccount							mainAccount;
    
        DimensionHierarchy dimHier;
        UserConnection userConn;
    
        contract = this.parmDataContract();
        fromDate    = contract.parmFromDate();
        toDate      = contract.parmToDate();
    
        ttsBegin;
        dimHier = DimensionHierarchy::getMainAccountFocus();
        //DimensionFocusUpdateBalance::updateBalance(
            //DimensionHierarchy::findByTypeAndName(DimensionHierarchyType::Focus, dimHier.Name),true);
        userConn = new UserConnection();
        trialBalanceTmp.setConnection(userConn);
        _tmp.setConnection(userConn);
        trialBalanceContract.parmDetailSummary(DetailSummary::Summary);
        trialBalanceContract.parmFromDate(fromDate);
        trialBalanceContract.parmToDate(toDate);
        trialBalanceContract.parmIncludeOpening(False);
        trialBalanceContract.parmIncludeClosing(False);
        trialBalanceContract.parmOperationsTax(OperationsTax::Current);
        //trialBalanceContract.parmPrimaryDimensionFocus(dimHier.Name);//Arafa
            trialBalanceContract.parmPrimaryDimensionFocus("03");
    
        trialBalanceDP.parmDataContract(trialBalanceContract);
        trialBalanceDP.parmUserConnection(userConn);
        trialBalanceDP.processReport();
    
        _tmp.recordLevelSecurity(false);
        trialBalanceTmp = trialBalanceDP.getLedgerTrialBalanceTmp();
    
        insert_recordset _tmp (
                    PrimaryFocus, LedgerDimension, OpeningBalance, AmountDebit, AmountCredit, ClosingBalance,
                        EndingBalance, Description, DimensionValues)
            select  PrimaryFocus, LedgerDimension, OpeningBalance, AmountDebit, AmountCredit, ClosingBalance,
                        EndingBalance, PrimaryFocusDescription, DimensionValues
            from trialBalanceTmp;
    		
    	while select forUpdate _tmp
        {
            // Update the fields here.
            _tmp.update();
        }
        ttsCommit;
        ttsCommit;
           
        _tmp.recordLevelSecurity(true);
    }

  • RE: Error in update TMP table Cannot select a record for update when the transaction is not started on the user connection attached.

    Thanks Gunjan Bhattacharyya

    I know it will working fine , but i need to use while select to use some methods in update ,because there are others fields i will update though methos

    Thnaks

  • Gunjan Bhattachayya Profile Picture
    Gunjan Bhattachayya 35,421 on at
    RE: Error in update TMP table Cannot select a record for update when the transaction is not started on the user connection attached.

    Hi Ahmed,

    Rather than using a while select statement for updating, you can use update_recordset instead. Please try this method and see if that takes care of the issue.

    [SysEntryPointAttribute(false)]
    public void processReport()
    {
        CustInvoiceJour         jour,custJour;
        CustInvoiceTrans        trans;
        TransDate               fromDate,toDate;
        ZMLTrialBalanceContract  contract = new ZMLTrialBalanceContract();
        LedgerTrialBalanceDP trialBalanceDP = new LedgerTrialBalanceDP();
        LedgerTrialBalanceContract trialBalanceContract = new LedgerTrialBalanceContract();
        //LedgerTrialBalanceTmp trialBalanceTmp;
    	DimensionAttributeValueCombination  ledgerDimension;
    	MainAccount							mainAccount;
    
        DimensionHierarchy dimHier;
        UserConnection userConn;
    
        contract = this.parmDataContract();
        fromDate    = contract.parmFromDate();
        toDate      = contract.parmToDate();
    
        ttsBegin;
        dimHier = DimensionHierarchy::getMainAccountFocus();
        //DimensionFocusUpdateBalance::updateBalance(
            //DimensionHierarchy::findByTypeAndName(DimensionHierarchyType::Focus, dimHier.Name),true);
        userConn = new UserConnection();
        trialBalanceTmp.setConnection(userConn);
        _tmp.setConnection(userConn);
        trialBalanceContract.parmDetailSummary(DetailSummary::Summary);
        trialBalanceContract.parmFromDate(fromDate);
        trialBalanceContract.parmToDate(toDate);
        trialBalanceContract.parmIncludeOpening(False);
        trialBalanceContract.parmIncludeClosing(False);
        trialBalanceContract.parmOperationsTax(OperationsTax::Current);
        //trialBalanceContract.parmPrimaryDimensionFocus(dimHier.Name);//Arafa
            trialBalanceContract.parmPrimaryDimensionFocus("03");
    
        trialBalanceDP.parmDataContract(trialBalanceContract);
        trialBalanceDP.parmUserConnection(userConn);
        trialBalanceDP.processReport();
    
        _tmp.recordLevelSecurity(false);
        trialBalanceTmp = trialBalanceDP.getLedgerTrialBalanceTmp();
    
        insert_recordset _tmp (
                    PrimaryFocus, LedgerDimension, OpeningBalance, AmountDebit, AmountCredit, ClosingBalance,
                        EndingBalance, Description, DimensionValues)
            select  PrimaryFocus, LedgerDimension, OpeningBalance, AmountDebit, AmountCredit, ClosingBalance,
                        EndingBalance, PrimaryFocusDescription, DimensionValues
            from trialBalanceTmp;
    		
    	update_recordset _tmp
    	setting MainAccountId = mainAccount.MainAccountId
    			join ledgerDimension 
    				where ledgerDimension.RecId == _tmp.LedgerDimension
    				join mainAccount
    					where mainAccount.RecId == ledgerDimension.MainAccount;
        ttsCommit;
           
        _

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,431 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,503 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans