web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Error in update TMP ta...
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 Ahmed Osama Ibrahim 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

I have the same question (0)
  • Gunjan Bhattachayya Profile Picture
    Gunjan Bhattachayya 35,423 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;
       
    _

  • Ahmed Osama Ibrahim Profile Picture
    Ahmed Osama Ibrahim 332 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.

    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,423 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);
}

  • Ahmed Osama Ibrahim Profile Picture
    Ahmed Osama Ibrahim 332 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.

    Sorry about the deley and thanks for your efort

    But same issue

  • Suggested answer
    Gunjan Bhattachayya Profile Picture
    Gunjan Bhattachayya 35,423 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);
}

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Martin Dráb Profile Picture

Martin Dráb 683 Most Valuable Professional

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 563 Super User 2025 Season 2

#3
Sohaib Cheema Profile Picture

Sohaib Cheema 398 User Group Leader

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans