I did get my example to handle the "no row.." condition - by enclosing it with a "Catch, Try.."
I'd still be interested If anybody can explain how to check dynamic queryresults for the existence of a row.
I had tried a few without success.
---In vantage@yahoogroups.com, <bruce.d.ordway@...> wrote:Hi,
I think I can add a condition that tests for the existence of a row in the dynamic query results?
But... I'm having a brainlock this afternoon.
Maybe someone can help? I suspect I'm missing something really obvious.
The sample code below is working if there is a row, but errors out if the BAQ results are empty.
I am using a BAQ that either returns one row or no rows.
I've been try to display a result in a textbox instead of the grids.
I get a "no row.." error in the cases where my BAQ isn't returning anything.
'// Dynamic Query – E905 where clause testing
Dim BaqID As [String] = "XXX-PartWhse"
Dim adDynamic As New DynamicQueryAdapter(TimePhasForm)
adDynamic.BOConnect()
adDynamic.GetDashboardQuery(BaqID)
Dim ds As Epicor.Mfg.BO.QueryDesignDataSet = adDynamic.RuntimeQuery
Dim dt As DataTable = ds.Tables("QueryTable")
If xPart > ""
If xPlant > ""
For Each lo_row As DataRow In dt.Rows
If lo_row("DataTableId").ToString() = "PartPlant" Then
lo_row("WhereClause") = "PartPlant.PartNum = '" + xPart + "'and PartPlant.Plant = '" + xPlant + "'"
Exit For
End If
Next
End If
End if
adDynamic.ExecuteDashboardQuery(ds)
‘// The grids work, even when the BAQ doesn’t return a row
'// Place results of BAQ in the epiUltraGrid controls added to the form
'//epiUltraGridC1.DataSource = adDynamic.QueryResults.Tables(0)
'//epiUltraGridC2.DataSource = ds.Tables(0)
If xxxxxxxx ‘//how to determine if the BAQ returned a row to the dataset?
rABC = adDynamic.QueryResults.Tables(0).Rows(0)("PartWhse.SystemAbc").ToString()
txtSystemABC.Text = rABC
adDynamic.Dispose()
xPart = ""
xPlant = ""
rABC = ""
else
adDynamic.Dispose()
txtSystemABC.Text = ""
xPart = ""
xPlant = ""
rABC = ""
End IF
---In vantage@yahoogroups.com, <vantage@yahoogroups.com> wrote:Here is the new way in Epicor ERP (E9) to get a BAQ, manipulate it (add whereClause), and run it. I got this from Epicor Support. I have not implemented it yet, but the code looks sound.
Dan
PROBLEM:
Custom code that calls the AddWhereItem method from the Dynamic Query Adapter will not work after 9.05.605.
This was reported to development on CR 11055ESC. Development did not assign an SCR to this issue. They indicated that there are two different business objects to consideration. They are the BAQDesigner and DynamicQuery business objects. The BAQDesigner business object does have a method AddWhereItem which is used to add a where clause in the Phase Builder. This method is supported in the current and future releases of the product.
However in the DynamicQuery business object, which is used to execute a BAQ, the AddWhereItem method was obsolete as of 9.05 and will be removed from the product permanently with ICE 3.0. The business object now expects the where clause to be passed through the QueryTable row.
To clarify the following code was obsoleted in 9.05:
adDynamic.GetByID(BaqID);
ds = adDynamic.QueryDesignData;
pcValue = "1011" ; //edvReport.dataView[edvReport.Row]["Field1"].ToString();
adDynamic.AddWhereItem(ds, pcQueryID, pcTable, pcField, pcCondition, pbIsConst, pcValue, pcToTable, pcToField, pcLeftParentheses, pcRightParentheses, pcAndOr, pbNeg);
adDynamic.Execute(ds);
It was replaced by:
adDynamic.GetDashboardQuery (BaqID);
Epicor.Mfg.BO.QueryDesignDataSet ds = adDynamic.RuntimeQuery;
DataTable dt = ds.Tables["QueryTable"];
foreach (DataRow lo_row in dt.Rows)
{
if (lo_row["DataTableId"].ToString() == "UD16")
{
lo_row["WhereClause"]="UD16.Key1='1011'";
break;
}
}
//Here is the script code that development gave us that worked:
private static void ProjectEntryForm_Load(object sender, EventArgs args)
{
// Add Event Handler Code
DynamicQuery();
}
private static void DynamicQuery()
{
// this is the BAQ we want to run
String BaqID = "EPIC06-bmsDynQuery";
DynamicQueryAdapter adDynamic = new DynamicQueryAdapter(ProjectEntryForm);
adDynamic.BOConnect();
adDynamic.GetDashboardQuery (BaqID);
Epicor.Mfg.BO.QueryDesignDataSet ds = adDynamic.RuntimeQuery;
DataTable dt = ds.Tables["QueryTable"];
foreach (DataRow lo_row in dt.Rows)
{
if (lo_row["DataTableId"].ToString() == "UD16")
{
lo_row["WhereClause"]="UD16.Key1='1011'";
break;
}
}
// Return records base on BAQ using whereclause
adDynamic.ExecuteDashboardQuery(ds);
// Place results of BAQ in the epiUltraGridC1 control that was added to the form
epiUltraGridC1.DataSource=adDynamic.QueryResults.Tables[0];
// Place string passed to DynamicQuery using the Query Table row into epiUltraGridC2
epiUltraGridC2.DataSource=ds.Tables[0];
}
Here is the customer's DynamicQuery subroutine that used the older format and is obsolete in 9.05.
Private static void DynamicQuery()
{
// this is the BAQ we want to run
String BaqID = "EPIC06-bmsDynQuery";
QueryDesignDataSet ds;
// this is used with the whereitem call to adjust the selection criteria
// these have to be tables that are in your baq.
String pcQueryID = BaqID;
String pcTable = "UD16";
String pcField = "KEY1";
String pcCondition = "=";
Boolean pbIsConst = true;
String pcValue = String.Empty;
String pcToTable = String.Empty;
String pcToField = String.Empty;
String pcLeftParentheses = String.Empty;
String pcRightParentheses = String.Empty;
String pcAndOr = String.Empty;
Boolean pbNeg = false;
// If seletion criteria is needed we need
// to load the query and then add selection criteria before running it instead
// of directly executing it.
DynamicQueryAdapter adDynamic = new DynamicQueryAdapter(ProjectEntryForm);
adDynamic.BOConnect();
// this would be used for BAQ that require NO additional selection criteria
//adDynamic.ExecuteByID(BaqID);
adDynamic.GetByID(BaqID);
ds = adDynamic.QueryDesignData;
// this shows that we can dynamically alter the BAQ selection criteria
// for example we may need to run the report for a differant customer
// for this example we are limiting it to the BOL number entered by the user at the moment
// replace this with values from the screen you are working with
pcValue = "1011" ; //edvReport.dataView[edvReport.Row]["Field1"].ToString();
adDynamic.AddWhereItem(ds, pcQueryID, pcTable, pcField, pcCondition, pbIsConst, pcValue, pcToTable, pcToField, pcLeftParentheses, pcRightParentheses, pcAndOr, pbNeg);
adDynamic.Execute(ds);
epiUltraGridC1.DataSource=adDynamic.QueryResults.Tables[0];
epiUltraGridC2.DataSource=ds.Tables[0];
}