locked
New transaction is not allowed because there are other threads running in the session. RRS feed

  • Question

  • while running this code:

    I selected records from the table of ListMonDet and i want to insert

    The problem is that db.sp_listmondet_add(....

    transaction is not allowed because there are other threads running in the session."

    ---------------------

    Private Function CopyRows(id_f As Int32, id_t As Int32) As Int32 Dim retInt As Int32 = 0 Dim userid = Session("user_id") Dim db As New WebDatabaseEntities Dim query = From d In db.ListMonDet Where d.LHid = id_f Select d For Each result In query If isExistBimId(id_t, result.BimId) = False Then db.sp_listmondet_add(id_t, result.BimId, result.Jobno, "", "", result.days, result.wDays, result.wMaz, result.wGmaz, result.wMah, result.wMash, result.wTot, result.bimRate, result.bimPrice, result.karRate, result.karPrice, result.bikRate, result.bikPrice) retInt += 1 End If Next db.SaveChanges() Return retInt End Function



    Saturday, October 31, 2015 7:07 AM

Answers

  • Since you are already using stored procedures I wonder if you might be better off using a stored proc to copy the rows.

    Other wise I would put a .ToList() on the results of the query.  I have read where a query (and the results are not retrieved until the app requests them) using a for each loop could have this problem.  

    I would try the .ToList() first as it should be easy to test.


    Lloyd Sheen

    • Proposed as answer by MDeero Tuesday, November 3, 2015 12:04 PM
    • Marked as answer by Ashkan209 Wednesday, November 4, 2015 4:25 AM
    Monday, November 2, 2015 11:14 PM
  • Hi Ashkan209,

    I agreed with sqlguy about using ToList method, and you could find more from http://stackoverflow.com/questions/2113498/sqlexception-from-entity-framework-new-transaction-is-not-allowed-because-ther

    BTW, since you've already used SP to insert into table, I would suggest you put the for statement into SP together, use Cursor to go through ListMonDet table and insert a new row for every item in Cursor. Then you just need call SP using EF.

    I hope it helps.

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Ashkan209 Wednesday, November 4, 2015 4:24 AM
    Tuesday, November 3, 2015 6:47 AM

All replies

  • Check your stored procedure.  Without that code it is impossible to help you.

    Lloyd Sheen

    Sunday, November 1, 2015 9:41 PM
  • store procedure works properly .

    I use this piece of code in other places, and it truly act

     db.sp_listmondet_add(id_t, result.BimId, result.Jobno, "", "", result.days,
                                         result.wDays, result.wMaz, result.wGmaz, result.wMah, result.wMash, result.wTot,
                                         result.bimRate, result.bimPrice, result.karRate, result.karPrice, result.bikRate, result.bikPrice)
                 
    ALTER proc [dbo].[sp_listmondet_add]
    
    @lhid int,
    @bimid int,
    @jobno nvarchar(6),
    @sdate nvarchar(10),
    @edtae nvarchar(10),
    @days int,
    @wdays int,
    @wmaz int,
    @wGmaz int,
    @wmah bigint,
    @wmash bigint,
    @wtot bigint,
    @bimrate decimal(4,2),
    @bimprice bigint,
    @karrate decimal(4,2),
    @karprice bigint,
    @bikrate decimal(4,2),
    @bikprice bigint
    
    as
    begin
    set nocount on
    insert into ListMonDet(lhid ,BimId ,Jobno ,sdate,Edate ,[days] ,wDays ,wMaz ,wGMaz,  wMah ,
                         wMash ,wTot  ,bimRate  ,bimPrice  ,karRate,karPrice ,bikRate  ,bikPrice  )
         			values(@lhid  ,@bimid  ,@jobno  ,@sdate  ,@edtae  ,@days  ,@wdays  ,@wmaz , @wGmaz  ,@wmah , 
    				@wmash ,@wtot ,@bimrate ,@bimprice ,@karrate  ,@karprice ,@bikrate ,@bikprice )
    end


    Monday, November 2, 2015 5:26 AM
  • Since you are already using stored procedures I wonder if you might be better off using a stored proc to copy the rows.

    Other wise I would put a .ToList() on the results of the query.  I have read where a query (and the results are not retrieved until the app requests them) using a for each loop could have this problem.  

    I would try the .ToList() first as it should be easy to test.


    Lloyd Sheen

    • Proposed as answer by MDeero Tuesday, November 3, 2015 12:04 PM
    • Marked as answer by Ashkan209 Wednesday, November 4, 2015 4:25 AM
    Monday, November 2, 2015 11:14 PM
  • Hi Ashkan209,

    I agreed with sqlguy about using ToList method, and you could find more from http://stackoverflow.com/questions/2113498/sqlexception-from-entity-framework-new-transaction-is-not-allowed-because-ther

    BTW, since you've already used SP to insert into table, I would suggest you put the for statement into SP together, use Cursor to go through ListMonDet table and insert a new row for every item in Cursor. Then you just need call SP using EF.

    I hope it helps.

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Ashkan209 Wednesday, November 4, 2015 4:24 AM
    Tuesday, November 3, 2015 6:47 AM