Need help with working with Dynamic Query results

So I’ve been working on this most of the day and have been able to get pretty far using examples I’ve found.

Using @josecgomez example for how to call a query from code here, I’ve been able to run a query, and bind it to a grid. I won’t need the grid for production, I just used it for testing/learning.

Using this example from the link below, I’ve been able to loop through the rows and columns to put the whole grid into a single string.

So this is working, but I need better control over the columns. There’s good and bad to be able to read the BAQ even after it’s changed, (like the column order), but in this case the template needs to stay the same so I want to explicitly call the columns by column name rather than loop through them dynamically.

I don’t think I’m too far off, but now I need to more precisely call specific columns from my DataTable to be able to build up the string that will be sent out as a CSV to present to bartender. I’m trying to do it like this thread:

But, I’m not dealing with a grid, I have the dynamic query adapter results I am trying to use, and I’m getting confused on how I’m supposed to that DataTable. (DataTables are new to me so I’m trying to learn how to use them)

Here is the code that I have so far. The Message box is just for debugging purposes and will be removed once it’s working.

	private void TruckPackButton_Click(object sender, System.EventArgs args)
	{
		// ** Place Event Handling Code Here **
		DynamicQueryAdapter dalps = new DynamicQueryAdapter(oTrans);
		dalps.BOConnect();
		QueryExecutionDataSet qeds = dalps.GetQueryExecutionParametersByID("PackingListPrintingDQA");
		qeds.ExecutionParameter.Clear();
		qeds.ExecutionParameter.AddExecutionParameterRow("JobNum",TruckJobNum.Text,"nvarchar", false, Guid.NewGuid(),"A");
		qeds.ExecutionParameter.AddExecutionParameterRow("LiftNum",TruckNum.Text,"int", false, Guid.NewGuid(),"A");		
		dalps.ExecuteByID("PackingListPrintingDQA",qeds);
		DataTable dt = dalps.QueryResults.Tables["Results"];
		//udMyGrid.DataSource = dalps.QueryResults.Tables["Results"];
			if (dalps.QueryResults.Tables["Results"].Rows.Count >0)
			{
			System.Text.StringBuilder b = new System.Text.StringBuilder();
			//this is the header that I need for my bartender file
			b.Append("CustName,OrderNum,JobNum,CustPoNum,TruckNum,TruckTrackingNum,TruckContainerNum,TruckSealNum,LiftNum,LiftWeight,AddedToTruckByEmpID_c,PartNum,Amount,UOM,TruckID,ShipDate,PartDescription,LiftPartComment,LiftComment,TruckComment,ShippingCompany,PrintingEmpID,CopyQty");
				foreach (System.Data.DataRow r in dalps.QueryResults.Tables["Results"].Rows)
				{
					b.Append(Environment.NewLine);
					//I need to know how to call specific columns here
					b.Append(How do I call a specific column here?);		
				}
			MessageBox.Show(b.ToString());
			string mycsv = b.ToString();
			string[] source = {""};
			source[0] = mycsv;
			System.IO.File.WriteAllLines("\\\\FolderPathHere\\Testing.csv",source);
			}

var x = r[“ColumnName”];

1 Like

Wow, I was making that harder than it had to be… Thanks @josecgomez

				foreach (System.Data.DataRow r in dalps.QueryResults.Tables["Results"].Rows)
				{
					b.Append(Environment.NewLine);
					//I need to know how to call specific columns here
					b.Append(r["Customer_Name"]);				
				}
1 Like

I am really trying to follow along here, but I’m just having trouble.
I need a text box to equal a value from a column in a Dynamic Query.
This is what I have so far:

DynamicQueryAdapter dqa1 = new DynamicQueryAdapter(oTrans);
        dqa1.BOConnect();
        QueryExecutionDataSet qeds1 = dqa1.GetQueryExecutionParametersByID("GS_Quote_LetterUD15");
        qeds1.ExecutionParameter.Clear();
        qeds1.ExecutionParameter.AddExecutionParameterRow("QuoteNum", QuoteRefNum.Value.ToString(), "nvarchar", false, Guid.NewGuid(), "C");
        dqa1.ExecuteByID("GS_Quote_LetterUD15", qeds1);
        grid_QuoteLetterPrev.DataSource = dqa1.QueryResults.Tables["Results"];
		foreach (System.Data.DataRow r in dqa1.QueryResults.Tables["Results"].Rows);
		epiTextBoxQuoteLetterHTML.Text = r["UD15_GS_BodyEmail_c"].ToString();

I am getting an error of the name “r” does not exist in the current context.

I think you need the {} around the the stuff after the foreach line. The For each is the loop and the curly brackets denote what is needs to repeat.

		foreach (System.Data.DataRow r in dqa1.QueryResults.Tables["Results"].Rows)
               {
		epiTextBoxQuoteLetterHTML.Text = r["UD15_GS_BodyEmail_c"].ToString();
               }

And you don’t end the line ( ; ) on the foreach line. It continues below that line.

2 Likes

did it work?

Yes, It did work. I am now trying to figure out how to make a text box fill with a column value upon selection in the grid.

Please start a new topic

Sorry for replying to an ancient post but for those googling a solution I thought I would add my 2 cents…

I wrote a BAQ that returns a single line but I wanted to avoid C# code in my function. This meant that I needed an expression to the single line in the BAQ rather the multiple lines of C#.

The returned value from the Dynamic Query is a data structure. I have called this QueryReturnDS.

To get the first line out of the query I wrote this as an expression into the ‘Set BPM Data Field’ field mapping for Character01:

Convert.ToString(QueryReturnDS.Tables[“Results”].Rows[0][“LaborDtl_ChangeDate”])

My field name (not the label) is LaborDTl_ChangeDate.