Hello,
I currently have a working BPM (data directive in-transaction) on JobHead. When a job is firmed, two user-defined note fields are successfully populated in JobHead from other tables. These work great – a one-time load that’s one-to-one with the job.
My problem is that I also need to update a user-defined note field on one or more rows (or none) in the job’s JobOper_UD table. I need to add custom code that will loop through the Job’s operations and populate the JobGenTxt_c field when a generic operation code is found. Problem is, I don’t know how to do that as my attempts create error messages that say “JobOper does not exist in the current context”.
Any suggestions on how to loop through the job’s operations and update as needed?
Erp.Tables.JobHead JobHead;
Erp.Tables.JobOper JobOper;
Erp.Tables.UD02 UD02;
using (var txscope1 = IceDataContext.CreateDefaultTransactionScope()) /* Open the connection to the database */
{
var firstJobHead = ttJobHead.FirstOrDefault(); /* Select the first ttJobHead record */
if (firstJobHead != null)
{
var JJobNum = firstJobHead.JobNum; //Variable for JobNum
var JobOperQuery = (from JobOperRow in Db.JobOper.With(LockHint.UpdLock)
where JobOper.Company == Session.CompanyID
&& JobOper.JobNum == JJobNum
&& JobOper.GenOpCode_c != null
select JobOperRow);
if (JobOperQuery != null)
{
foreach (var JobOperRow in JobOperQuery) /* iterate through the query on the secondary table*/
{
var UD02Query = (from UD02Row in Db.UD02
where UD02.Key1 == JobOperQuery.GenOpCode_c
select UD02Row);
JobOperRow["JobGenTxt_c"] = UD02Query.UD02_Row.GenTxt_c.ToString();
}
}
}
Db.Validate(); /*Validate the changes */
txscope1.Complete(); /* Commit the changes */
}
The type or namespace name ‘UD02’ does not exist in the namespace ‘Erp.Tables’ (are you missing an assembly reference?)
CS1061
‘IQueryable’ does not contain a definition for ‘GenOpCode_c’ and no extension method ‘GenOpCode_c’ accepting a first argument of type ‘IQueryable’ could be found (are you missing a using directive or an assembly reference?)
CS1061
‘ObjectQuery’ does not contain a definition for ‘UD02_Row’ and no extension method ‘UD02_Row’ accepting a first argument of type ‘ObjectQuery’ could be found (are you missing a using directive or an assembly reference?)
What is the field type of GenTxt_C? is it the same as JobGenTxt_c? If so try this. You don’t need to declare the tables, since you aren’t using them.
var firstJobHead = ttJobHead.FirstOrDefault(); / Select the first ttJobHead record */
if (firstJobHead != null)
{
var JJobNum = firstJobHead.JobNum; //Variable for JobNum
var JobOperQuery = (from JobOperRow in Db.JobOper.With(LockHint.UpdLock)
where JobOper.Company == Session.CompanyID
&& JobOper.JobNum == JJobNum
&& JobOper.GenOpCode_c != null
select JobOperRow);
if (JobOperQuery != null)
{
using (var txscope1 = IceDataContext.CreateDefaultTransactionScope()) /* Open the connection to the database /
{
foreach (var JobOperRow in JobOperQuery) /* iterate through the query on the secondary table*/
{
var UD02Query = (from UD02Row in Db.UD02
where UD02.Key1 == JobOperQuery.GenOpCode_c
select UD02Row).FirstOrDefault();
JobOperRow.JobGenTxt_c = UD02Query.GenTxt_c;
}
Db.Validate(); /*Validate the changes */
txscope1.Complete(); /* Commit the changes */
}
}
}
Just looking at the code… there are several improvements that can be made… but this section is probably your issue…
OLD CODE:
if (JobOperQuery != null) {
foreach (var JobOperRow in JobOperQuery) /* iterate through the query on the secondary table*/ {
var UD02Query = (from UD02Row in Db.UD02 where UD02.Key1 == JobOperQuery.GenOpCode_c select UD02Row);
JobOperRow["JobGenTxt_c"] = UD02Query.UD02_Row.GenTxt_c.ToString();
}
}
New CODE…
uses a Lambda expression
for efficiency, we only select the ONE FIELD we need (instead of the entire record).
also checked for null value on UD02Record
if (JobOperQuery != null) {
foreach (var JobOperRow in JobOperQuery) /* iterate through the query on the secondary table*/ {
var UD02Record = Db.UD02.Where(x => x.Company == firstJobHead.Company && x.Key1 == JobOperQuery.GenOpCode_c).Select(x => new { x.GenTxt_c }).FirstOrDefault();
if (UD02Record != null) {
JobOperRow["JobGenTxt_c"] = UD02Query.GenTxt_c.ToString();
}
}
}
using (var txscope1 = IceDataContext.CreateDefaultTransactionScope())
{
var firstJobHead = ttJobHead.FirstOrDefault();
string[] SaveGenText = new String[5000];
if (firstJobHead != null)
{
var JJobNum = firstJobHead.JobNum;
var JGenOpCode = (String)null;
var JobOperQuery = (from JobOperRow in Db.JobOper.With(LockHint.UpdLock)
where JobOper.Company == Session.CompanyID
&& JobOper.JobNum == JJobNum
&& JobOper.GenOpCode_c != null
select JobOperRow);
if (JobOperQuery != null) {
foreach (var JobOperRow in JobOperQuery) {
var UD02Record = Db.UD02.Where(x => x.Company == firstJobHead.Company && x.Key1 == JobOperQuery.GenOpCode_c).Select(x => new { x.GenTxt_c }).FirstOrDefault();
if (UD02Record != null) {
JobOperRow["JobGenTxt_c"] = UD02Record.GenTxt_c.ToString();
}
}
}
}
Db.Validate();
txscope1.Complete();
}
Syntax Errors:
CS1061 ‘IQueryable’ does not contain a definition for ‘GenOpCode_c’ and no extension method ‘GenOpCode_c’ accepting a first argument of type ‘IQueryable’ could be found (are you missing a using directive or an assembly reference?)
CS0165 Use of unassigned local variable ‘JobOper’
I would do a few things different.