Anybody else having Epicor fun at 9pm?
I am trying to streamline some performance in a UDTable implementation.
I was creating a bunch of records and commiting them one at at time like:
It seems like there is alot of overhead doing this with individual transactions (read: SLOOOOW)
I tried to load up multiple records into UD39Data before updating like:
Essentially I expected GetaNewUD39() to keep giving me new rows in UD39Data but all it does it overwrite the first row.
I thought I could outsmart it and I also tried with no luck:
Is there a way to create multiple records in one transaction (Update)?
What’s GetaNewUD39 returning as a row? Can’t it be BufferCopy’d? Set some fields on the copied row, add it to the dataset table?
I’ve never tried it but assumed it would be possible.
Well… there was a method GetNewUD39() which you passed the keys and got back a row. That’s been deprecated and I can only use GetaNewUD39(void) that returns a bool.
Judging by what my blurry eyes tell me from the decomp, it seems that the dataset get a fresh copy on call:
UD39Tableset uD39Tableset = new UD39Tableset();
DatasetAdapter.CopyDataTableToTSTable<UD39Row, UD39DataSet.UD39Row>(ds.UD39, uD39Tableset.UD39, true);
No matter what I do I can’t seem to get UD39.Rows to ever get more than 1 row.
var row = a.UD39Data.UD39.NewUD39Row();
row.Company = "CP";
row.Key1 = "JUNK";
row.Key2 = record+1.ToString();
row.Key3 = record+2.ToString();
row.Key4 = record+3.ToString();
row.Key5 = record+4.ToString();
I’m tired too, so take this with a grain of salt but I believe you can do
a.UD39Data.UD39.NewRow() or add row and that gives you a fresh row in the dataset, just make sure you fill it out with all the proper stuff.
You can also try update ext
I’ve gotten closer, I found the NewRow was failing because of a failure to set a SysRowId - unfortunately it just silently failed with no explanation/indication.
Now I;ve got my rows adding nicely to UD39Data, but on Update I get generic error:
An unhandled exception of type ‘System.Data.ConstraintException’ occurred in Ice.Lib.EpiClientLib.dll
Additional information: This property cannot be set to a null value.
I must be missing some important field but I am cross-eyed at this point and surely don’t see it.
Maybe I’ll see what reflection has to say about UpdateExt. Don’t work too hard - maybe we both need sleep
Ok sleep helped. I’ve got it working. The gist of the problem is that when you manually create a row, it doesn’t initialize (things like fields that cant be null,company, sysrow etc).
You can manually create rows but you have to populate EVERYTHING - usually when using GetaNewUD39 it populates most of the mundane. BTW - You can use the typed fields of the (row) - I just haven’t went back to reformat it. I.E. row.Company = “CP”;
for (int record = 0; record < howmany; record++)
var row = a.UD39Data.UD39.NewUD39Row();
//init the damn thing...... You better do all of this OR ELSE
for (int x = 1; x <= 20; x++)
row["CheckBox" + x.ToString("d2")] = false;
row["Number" + x.ToString("d2")] = 0;
row["ShortChar" + x.ToString("d2")] = "";
if(x<11) row["Character" + x.ToString("d2")] = "";
row["Date" + x.ToString("d2")] = DBNull.Value;
//other required fields you MUST fill
row["Company"] = company;
row["RowMod"] = "A";
row["BitFlag"] = false;
row["GlobalUD39"] = false;
row["GlobalLock"] = false;
row["SysRowID"] = new Guid();
row["SysRevID"] = 0;
//the rest of your actual data here
That’s why I mentioned BufferCopy.Copy() and fix up the new row.
@Bart_Elia everytime I tried to copy the row it complained that somebody else “owned” that row.
hmm… I’ve wanted to play with the whole single dirty row approach on the client for awhile. adding another research project …
@Bart_Elia along the same subject line.
What do you figure the best way to get and modify a handful of records at once is. I’ve been chugging along one at a time with GetByID. I am thinking I could use the same method above in creating new rows in the UDdata ds based on results from a GetRows with a whereclause. I expect I could just pipe the entire ds result of GetRows, creating a new row in the UDdata ds. From there modification should be straight forward with a lonely Update() at the end.
Also, btw, if I haven’t mentioned it lately, I am so thankful that an expert such as yourself takes so much time to give us your wisdom and opinions here. It really makes us feel loved. That goes for the rest of you Epicor knuckleheads as well
@aidacra @Edge @Olga
I’m such a bozo… ImportRow() for the win. Solves all those problems
SearchOptions opts = new SearchOptions(SearchMode.AutoSearch);
opts.NamedSearch.WhereClauses.Add("UD39","Key1 = '996'");
bool more = false;
UD39DataSet ds = (UD39DataSet)Singleton.a.GetRows(opts, out more);
foreach (UD39Row row in ds.UD39.Rows)
//TEST CHANGING SOME DATA
row.BeginEdit(); //we'll modify the 'valid' row
row.Number01 = 61;
Singleton.a.UD39Data.UD39.ImportRow(row); //now all your base are belong to us
And to add to the Bozo-ery, I think you were suggesting this (which would probably work as well)
Give update ext a shot.In theory it takes a partial dataset with as many changes as you want t and it updates it. Works well most of he times
Are you kidding me? I can pass my own dataset to save?! Lol - I have learned so much in the span of one thread!
@Chris_Conn - I found this thread because I am attempting a similar thing. Trying to get legacy data, parse it, and shove it into a UD table so that we can reference it inside Epicor. So I am trying to add many rows at once. Your threads are always entertaining… as a way of laughing at the situation:
I just spent the better part of a day trying to chase down an error I was getting about a key already existing so it could not add the row… I could not figure it out. But eventually, after many failed attempts, I eventually realized that it was failing on the 36th row of data I was trying to add. So I inspected that row, and lo-and-behold, it was the key in my dictionary that it was yelling at me about - not the key in my UD table!!
Haha nice to see my agony is appreciated.
Dont feel bad, I cant tell you how many times I misinterpreted an error message.
So your dictionary already had the same key? What did the key in your dictionary represent from the legacy data?
I was making the assumption that the field I was parsing would have the same layout in all rows - but I came across some rows that did not. So my parsing statement was failing horribly.
In all seriousness, I do have a pertinent question. So in my exercise of futility, I am trying to take all this data (from our Orders tables) and push in pertinent information to our UD table. I am checking first to see if the data is there (in my live environment, there will be existing data and I don’t want to duplicate/overwrite what’s there). If it’s not there, I use a GetaNewUD38 and I have that ironed out and working. If it finds rows, I am trying to update the existing row… Below is what I’ve got. Help. I get no errors, but I can’t get anything to update in my UD table.
foreach (var eachRelease in getOrderRel)
int i = 0;
int releaseNum = eachRelease.OrderRelNum;
Decimal releaseQty = eachRelease.Qty;
var checkForExisting = (from t in Db.UD38
where t.Key1 == eachOrderLine.OrderNum.ToString()
&& t.Key2 == eachOrderLine.OrderLine.ToString()
&& t.Key3 == releaseNum.ToString()
if (checkForExisting.Count() == 0)
//------ CREATE A NEW LINE IN UD38 FOR EACH QUANTITY IN THE ORDER (EACH SERIAL NUMBER NEEDS ITS OWN LINE)-------
while (i < Convert.ToInt32(releaseQty))
dsUD38.UD38.Key1 = eachOrderLine.OrderNum.ToString();
dsUD38.UD38.Key2 = eachOrderLine.OrderLine.ToString();
dsUD38.UD38.Key3 = releaseNum.ToString();
dsUD38.UD38.Key5 = i.ToString();
catch (Exception ex)
} // end while
} // end if
//------------IF LINE ALREADY EXISTS, UPDATE EXISTING LINE ----------------------------------------------
foreach (var eachRow in checkForExisting)
string jobNum = eachRow.Key4;
string serialNum = eachRow.Key5;
dsUD38 = boUD38.GetByID(eachOrderLine.OrderNum.ToString(), eachOrderLine.OrderLine.ToString(), releaseNum.ToString(), jobNum, serialNum);
var updateRow = (from r in dsUD38.UD38
if (updateRow != null)
updateRow["Character10"] = "Test";
updateRow.Character10 = "Test";
updateRow["RowMod"] = "U";
updateRow.RowMod = "U";
catch (Exception ex)
} // end foreach
} // end else
} // end foreach
Are you sure the updateRow != null – perhaps its null.
Make sure always when you are querying Db tables to also add t.Company == Session.CompanyID or something
First, I’d make sure I have data. GetByID will throw an exception if no record is found, however, in a try\empty catch it goes unnoticed. Can you confirm you ever get updateRow != null
Also when creating a new UD38, unless you are wiping your ds, accessing UD38 always give you the first record, not the new one