So, I have a Data Directive which uses the table UD08 and based on data in there, it will create a Part, Part Site, Part Alternate, etc… Everything works great except that I’m trying to get the error message in one of the UD Field (Character03, I need to show the error message in an Excel File to the user).
I get the error in the ex.Message in my try-catch. However, I have an error saying that my transaction isn’t disposed but is completed. How can I fix this error?
===============
The transaction associated with the current connection has completed but has not been disposed. The transaction must be disposed before the connection can be used to execute SQL statements.
Is the disposal error what’s written into UD08 or does that get thrown when it tries to write it? Also, what’s the commonality - same record, long running query, etc.?
A couple of ideas come to mind either way:
This looks like it could be a somewhat common timeout issue. Does the update transaction take a while? Can you try breaking it up into multiple updates? Are you trying to do concurrent updates and accidentally putting a lock on the row/table causing a deadlock that only resolves on a timeout?
You could try disposing of the partSvc in the catch block before doing anything else. Or, as I prefer to do, move all of the code that needs the partSvc in the try block and call the service with a using statement so that it handles the disposal for you automatically.
If I move everything in the try block, i get the error ‘The Transaction has aborted’. The update transaction shouldn’t take a while it’s a simple part creation (only one for now)
To get the data to process, I first read the UD08 table rows that aren’t processed yet. For each row, I create the part (try-catch) and I would like to be able to update the UD08 row in the catch block. I guess that you’re right since by reading the UD08 rows, it might put a lock resulting in a timeout when I try to update.
However, even if I do nothing in my catch block, I get ‘The transaction has aborted’
var ud08Lst = from ud08 in Db.UD08
where ud08.Company == callContextClient.CurrentCompany && ud08.Character03 != "Imported"
select ud08;
foreach(var row in ud08Lst)
{
var company = row.Company;
var partNum = row.Key1;
var key2 = row.Key2;
var key3 = row.Key3;
var key4 = row.Key4;
var key5 = row.Key5;
/*Part*/
var partDescription = row.Character01;
var partDescriptionFR = row.Character02;
var type = row.ShortChar01;
var uomClass = row.ShortChar02;
var ium = row.ShortChar03;
var salesUM = row.ShortChar04;
var pum = row.ShortChar05;
var prodCode = row.ShortChar06;
var classID = row.ShortChar07;
var costMethod = row.ShortChar08;
var nonStock = row.CheckBox01;
var qtyBearing = row.CheckBox02;
var usePartRev = row.CheckBox03;
var commercialCategory = row.ShortChar11;
var commercialSubCategory = row.ShortChar12;
var lastUpdate = new DateTime?(DateTime.Now);
/*Create Part*/
var partSvc = ServiceRenderer.GetService<Erp.Contracts.PartSvcContract>(Db);
using(partSvc)
{
try
{
Erp.Tablesets.PartTableset partTS = new Erp.Tablesets.PartTableset();
partSvc.GetNewPart(ref partTS);
type = "data_that_does_not_exist_to_trigger_error";
partTS.Part[0].PartNum = partNum;
partTS.Part[0].PartDescription = partDescription;
partTS.Part[0].TypeCode = type;
partTS.Part[0].UOMClassID = uomClass;
partTS.Part[0].IUM = ium;
partTS.Part[0].SalesUM = salesUM;
partTS.Part[0].PUM = pum;
partTS.Part[0].ProdCode = prodCode;
partTS.Part[0].ClassID = classID;
partTS.Part[0].CostMethod = costMethod;
partTS.Part[0].NonStock = nonStock;
partTS.Part[0].QtyBearing = qtyBearing;
partTS.Part[0].UsePartRev = usePartRev;
partTS.Part[0].CommercialCategory = commercialCategory;
partTS.Part[0].CommercialSubCategory = commercialSubCategory;
partSvc.Update(ref partTS);
}
catch(Exception ex)
{
this.PublishInfoMessage(ex.Message, Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "FirstVar","SecondVar");
}
}
Simply querying a table shouldn’t put any kind of lock on it, so that’s all good.
I’ve never tried calling the using statement quite that way and I’m not sure it’s actually going to dispose of the object. That might be the root of the disposal error. Give this a shot and see what happens:
The transaction associated with the current connection has completed but has not been disposed. The transaction must be disposed before the connection can be used to execute SQL statements.
At this point, I’m almost giving up on doing this in a Data Directive and will do from an external script
I was doing it from another table in a In Transaction. I changed it to Standard Directive and it worked at first but after adding more code, I had other errors.
I didn’t have too much time to do this development so I did it in an external script but I’ll try to try again in some months to do it the right way. Once I figure it out, I’ll post the code here!