Thanks for letting me know, I haven’t done any inline SQL commands yet so I’ll avoid doing that.
I’ve actually solved the issue now but I had no idea you could run a trace on the BAQ Designer too, that’s a good trick to know thanks for your help!
Cheers
How did you solve it?
-Jose
Used a DynamicQueryAdapter to run the query then updated the DynamicQueryAdapter’s QueryResults “Results” table with what I needed updating and then ran the DynamicQueryAdapter’s Update method.
@Rik, any chance you can provide the code you got working? I’m looking to do the same thing and haven’t been able to find any good examples of updatable BAQs within UI customization (C#).
An example:
DynamicQueryAdapter dqa = new DynamicQueryAdapter(oTrans);
dqa.BOConnect();
QueryExecutionDataSet qeds = dqa.GetQueryExecutionParametersByID("Printer_By_Company_CC");
qeds.ExecutionParameter.Clear();
qeds.ExecutionParameter.AddExecutionParameterRow("COMPANY",((Ice.Core.Session)oTrans.Session).CompanyID,"nvarchar",false,Guid.NewGuid(),"A");
dqa.ExecuteByID("Printer_By_Company_CC",qeds);
//MessageBox.Show(dqa.QueryResults.Tables["Results"].Rows.Count.ToString());
if(dqa.QueryResults.Tables["Results"].Rows.Count > 0)
{
//SET TO GRID HERE........
///prntr = dqa.QueryResults.Tables["Results"].Rows[0]["SysPrinter_NetworkPath"].ToString();
PrinterGrid.DataSource = dqa.QueryResults;
@Chris_Conn, thank you for the example. Populating the grid with the query results is working fine.
How do I modify the code to save any changes made to the data in the grid? The BAQ is already updatable, but I am not sure how to initiate the update from the customization. Any suggestions?
If you use the ExecuteByID method on DynamicQuery then the BAQ won’t be updatable in your code.
Here’s a version of what we use:
if (baqName != string.Empty)
{
if (!gotBAQ)
{
if (adptr.GetByID(baqName)) { gotBAQ = true; }
}
if (updateable)
{
if (!(ds != null)) { ds = adptr.DynamicQueryData; }
if (ds.DynamicQuery.Rows.Count == 0)
{
Ice.BO.DynamicQueryDataSet dsQDesign = adptr.QueryDesignData;
DataRow targetRow;
foreach (DataTable table in ds.Tables)
{
foreach (DataRow sourceRow in dsQDesign.Tables[table.ToString()].Rows)
{
targetRow = table.NewRow();
targetRow.ItemArray = sourceRow.ItemArray;
table.Rows.Add(targetRow);
}
}
}
if (!(dsBAQ != null)) { dsBAQ = adptr.GetQueryExecutionParameters(ds); }
}
else
{
if (!(dsBAQ != null)) { dsBAQ = adptr.GetQueryExecutionParametersByID(baqName); }
}
if (baqParams != null)
{
int i = 0;
foreach (KeyValuePair<string, string> p in baqParams)
{
bool empty = false;
string key = p.Key;
string val = p.Value;
if (key.Substring(0,1) == "-")
{
if (val == string.Empty) { empty = true; }
key = key.Substring(1, key.Length - 1);
}
dsBAQ.ExecutionParameter[i].ParameterID = key;
dsBAQ.ExecutionParameter[i].IsEmpty = empty;
dsBAQ.ExecutionParameter[i].ParameterValue = val;
i++;
}
dsBAQ.AcceptChanges();
}
if (updateable)
{
adptr.Execute(ds, dsBAQ);
}
else
{
adptr.ExecuteByID(baqName, dsBAQ);
}
if (adptr.QueryResults != null && adptr.QueryResults.Tables.Count > 0)
{
results = adptr.QueryResults.Tables["Results"];
}
else
{
results = new DataTable();
}
if (!(edv != null)) { edv = (EpiDataView)oTrans.EpiDataViews[baqName]; }
if (!(edv != null))
{
edv = new EpiDataView();
oTrans.Add(baqName, edv);
}
edv.dataView = results.DefaultView;
if (grid != null) { grid.DataSource = results; }
}
You can leave out the last line and bind the grid to the EpiDataView directly if you want. Either way, as long as you make sure the parts of the above where “updateable” = true are the ones being used, the BAQ data will be updatable.
hi @dhewi,
I tried this all but the database is not getting updated.
how to trigger the save db action?
dynamicqueryadapter.Update(); and BOobjectadapter.Update() both, but no luck.
the baq is updateable and I could update from BAQ designer
what could I be possibly missing?
It’s usually a detail somewhere, which makes it hard to tell without seeing the code you’re using. Are you getting an error, or just no update?
it is not updating, there is no errors
private void btnRetrieve_Click(object sender, System.EventArgs args)
{
// ** Place Event Handling Code Here **
FillPendingRequests();
}
DynamicQueryAdapter yourbaq;
DataTable results;
private void FillPendingRequests()
{
yourbaq = new DynamicQueryAdapter(this.oTrans);
yourbaq.BOConnect();
string baqname = "Ex_PendingStkMove";
//yourbaq.ExecuteByID(baqname, dsBAQ);
yourbaq.GetByID(baqname);
Ice.BO.DynamicQueryDataSet ds = yourbaq.DynamicQueryData;
if (ds.DynamicQuery.Rows.Count == 0)
{
Ice.BO.DynamicQueryDataSet dsQDesign = yourbaq.QueryDesignData;
DataRow targetRow;
foreach (DataTable table in ds.Tables)
{
foreach (DataRow sourceRow in dsQDesign.Tables[table.ToString()].Rows)
{
targetRow = table.NewRow();
targetRow.ItemArray = sourceRow.ItemArray;
table.Rows.Add(targetRow);
}
}
}
var dsBAQ = yourbaq.GetQueryExecutionParametersByID(baqname);
dsBAQ.AcceptChanges();
yourbaq.Execute(ds, dsBAQ);
if (yourbaq.QueryResults != null && yourbaq.QueryResults.Tables.Count > 0)
{
results = yourbaq.QueryResults.Tables["Results"];
}
else
{
results = new DataTable();
}
EpiDataView edv = null;
if (!(edv != null)) { edv = (EpiDataView)oTrans.EpiDataViews[baqname]; }
if (!(edv != null))
{
edv = new EpiDataView();
oTrans.Add(baqname, edv);
}
edv.dataView = results.DefaultView;
gridPendingRequests.DataSource = results;
}
private void btnCreatePack_Click(object sender, System.EventArgs args)
{
yourbaq.Update();
results.AcceptChanges();
//CallMaterialQueueAdapterUpdateMethod();
}
this is my code
could you please have a look?
Look up the DynamicQuery adapter Update method signature in Object Explorer. You need to pass your DataSets etc back into it. I think that’s your key problem.
I could not find it in object explorer - it is not listing this adapter, I tried a lot in google also the method signature for DynamicQueryAdapter.Update() but no luck;
could you please tell me the signature or where exactly I can see the signature.
sorry for this basic question, please help
It IS in the Object Explorer, but it tricks you by starting alphabetically again for the Ice adapters after the Erp adapters. You need to scroll down further.
Thanks a lot, it worked.
for anybody looking for this
I updated the code as follows
private void btnCreatePack_Click(object sender, System.EventArgs args)
{
yourbaq.Update(baqname,results.DataSet);
}
Hi,
Just a quick query . Code is working but instead update changes is coping current row and adding new row to it so i have old row plus new one with changes. I don’t understand why:(
Here is code I used to exec a UBAQ and add new Rows which I use as my Server File Writer, so I can on-prem write to UNC Paths as the Service Account (Server Side) and not Client Side!
public System.Data.DataSet xyz()
{
// Declare and create an instance of the Adapter.
DynamicQueryAdapter adapterDynamicQuery = new DynamicQueryAdapter(this.oTrans);
adapterDynamicQuery.BOConnect();
// Declare and Initialize Variables
string BAQName = "HASO-FileWrite";
bool more;
var dqds = adapterDynamicQuery.GetQueryExecutionParametersByID(BAQName);
var ds = adapterDynamicQuery.GetList(BAQName, dqds, 0, 0, out more);
// This shows me adding 2 Rows manually, here you would loop through your dataset and add many many rows
// then you would call .Update once! And then in the BPM you can Combine them all and use
// WriteAllText to write it out in a single file open.
var newRow = ds.Tables["Results"].NewRow();
newRow["Calculated_FileName"] = "MyFileName.txt";
newRow["Calculated_Content"] = "rrrrrra";
newRow["RowMod"] = "A";
newRow["SysRowID"] = Guid.NewGuid();
newRow["RowIdent"] = newRow["SysRowID"].ToString();
ds.Tables["Results"].Rows.Add(newRow);
var newRow2 = ds.Tables["Results"].NewRow();
newRow2["Calculated_FileName"] = " MyFileName.txt";
newRow2["Calculated_Content"] = "eeeeee";
newRow2["RowMod"] = "A";
newRow2["SysRowID"] = Guid.NewGuid();
newRow2["RowIdent"] = newRow2["SysRowID"].ToString();
ds.Tables["Results"].Rows.Add(newRow2);
adapterDynamicQuery.Update(BAQName, ds);
// Get Results if we need to read for example a Success or Failure Column
// results.Tables["Results"].Rows[0]["Calculated_SomeSuccessColumn"]
DataSet results = adapterDynamicQuery.QueryResults;
// Cleanup Adapter Reference
adapterDynamicQuery.Dispose();
return results;
}
PS: For those always saying I want to log to a shared text-file on my on-prem here you go make a UBAQ and safely write in a controlled manner (dont let the User pick a path) to a UNC Path as a Service Account, Logs Away!
Look at my code above, the .GetList should get you your data, then instead of adding new rows, loop through your data and update existing columns, probably assign also RowMod “U” and then Update.
Thank you. I will try and let you know.