I am trying to convert a E9 custom execution of a BAQ to E10, however, when I use the E9 code, I got an error. I found solution that say to use parameter instead of the QueryWhereItem ( Calling BAQ from Code Epicor ERP 10 - YouTube ), which I did. I created a new BAQ that uses parameter, but when I tried to execute the BAQ, based on the video link attached, I still get errors.
Can anyone help? If you need to see the actual code that uses the parameter let me know.
Thanks.
Search the site for DynamicQueryAdapter. There are lots of posts on using it to run a BAQ to get back data. There is probably a video also from @josegomez on how to use it out there somewhere.
Thanks. I watched the video and followed it closely and still got errors.
But I will search again and see what I can find.
Sorry. Didn’t realize that was a dynamic video you were referencing. post your errors and we can look at what might be missing.
Please share your relevant code and errors and I am sure we can help.
This is the BAQ: CustomerInfo2A
select
[Customer].[CustID] as [Customer_CustID],
[Customer].[Name] as [Customer_Name],
[Customer].[Address1] as [Customer_Address1],
[Customer].[Address2] as [Customer_Address2],
[Customer].[Address3] as [Customer_Address3],
[Customer].[City] as [Customer_City],
[Customer].[State] as [Customer_State],
[Customer].[Zip] as [Customer_Zip],
[Customer].[EMailAddress] as [Customer_EMailAddress],
[CustCnt].[Name] as [CustCnt_Name],
[CustCnt].[Func] as [CustCnt_Func],
[CustCnt].[EMailAddress] as [CustCnt_EMailAddress],
[Customer].[CheckBox01] as [Customer_CheckBox01]
from Erp.Customer as Customer
inner join Erp.CustCnt as CustCnt on
Customer.Company = CustCnt.Company
and Customer.CustNum = CustCnt.CustNum
cross join Erp.Customer_UD as Customer_UD
where (Customer.CustID = @SelCustomerID)
This is the customized code:
using (DynamicQueryAdapter baqAdapter = new DynamicQueryAdapter(oTrans))
{
baqAdapter.BOConnect();
QueryExecutionDataSet qds = new QueryExecutionDataSet();
QueryExecutionDataSet qds = baqAdapter.GetQueryExecutionParametersByID("CustomerInfo2A");
qds.ExecutionParameter.Clear();
qds.ExecutionParameter.AddExecutionParameterRow("SelCustomerID", "BAC100", "nvarchar", false, Guid.NewGuid(), "A");
baqAdapter.ExecuteByID("CustomerInfo2A", qds); // THIS IS WHERE IT IS ERRORING OUT
int rCount = baqAdapter.QueryResults.Tables["Results"].Rows.Count;
if ( baqAdapter.QueryResults.Tables["Results"].Rows.Count > 0 )
{
if ( (bool)baqAdapter.QueryResults.Tables["Results"].Rows[0]["Customer_CheckBox01"] )
{
MessageBox.Show( baqAdapter.QueryResults.Tables["Results"].Rows[0]["Customer_CustID"].ToString() + " - " +
baqAdapter.QueryResults.Tables["Results"].Rows[0]["Customer_Name"].ToString() );
}
}
}
the 1st qds was commented out.
That’s the error I am receiving.
The string parameter SelCustomerID exists in the query? What happens if you run the query manually and pass that param as BAC100?
Yes it does as seen in the BAQ posted:
where (Customer.CustID = @SelCustomerID)
it is the parameter.
When I tested the BAQ on the Analyze tab, it return results but from the customized code posted, it threw the error posted.
Are there any non-alphanumeric characters in any field of your query response when you view in editor?
Yes, the CheckBox1 field is non-alphanumeric and the CustNum used to join the ither table . I bold them in the BAQ below.
select
[Customer].[CustID] as [Customer_CustID],
[Customer].[Name] as [Customer_Name],
[Customer].[Address1] as [Customer_Address1],
[Customer].[Address2] as [Customer_Address2],
[Customer].[Address3] as [Customer_Address3],
[Customer].[City] as [Customer_City],
[Customer].[State] as [Customer_State],
[Customer].[Zip] as [Customer_Zip],
[Customer].[EMailAddress] as [Customer_EMailAddress],
[CustCnt].[Name] as [CustCnt_Name],
[CustCnt].[Func] as [CustCnt_Func],
[CustCnt].[EMailAddress] as [CustCnt_EMailAddress],
[Customer].[CheckBox01] as [Customer_CheckBox01]
from Erp.Customer as Customer
inner join Erp.CustCnt as CustCnt on
Customer.Company = CustCnt.Company
and Customer.CustNum = CustCnt.CustNum
cross join Erp.Customer_UD as Customer_UD
where (Customer.CustID = @SelCustomerID)
Sorry I didnt make that clearer. I mean when you run the query in the editor, do any of your fields have non standard characters - I am specifically curious about < and >. It looks like the xml its trying to deserialize is failing. I would expect that Epicor escapes those but I am just poking to see if it’s related.
Perhaps we could make a simple query, one table, one field returned and one param and see if you get similar results.
I will try that and let you know.
Yes, it seems like the data that was coming back is causing the error. I removed most of the fields and only left 2 and everything worked fine.
Thank you.
But how do you escape the data during execution?
Because the error is occurring at the time for ExecuteByID() so I am concluding that I need to escape the data before it is returned to the custom code.
You can create a BPM on the GetList method to loop through the returned data and massage it. However it’s probably best to see if you can find the offending data and fix it.
