GetRows not filtering

Graeme_Fraser · June 19, 2017, 2:16pm

Hi

I have spent ages looking at this and cant figure it out. Can anyone shed any light on why my code returns every row and is ignoring the where clause I have specified?

Thanks in advance.

Graeme

            adapt.BOConnect();

            Ice.Lib.Searches.SearchOptions so = new Ice.Lib.Searches.SearchOptions(Ice.Lib.Searches.SearchMode.AutoSearch);
            String whereClause = "Company = 'EPIC06' and plant = 'MfgSys'"; 
            
         
            so.DataSetMode = Ice.Lib.Searches.DataSetMode.RowsDataSet;
            so.NamedSearch.WhereClauses.Add("ShipViaCode", whereClause);
         

            System.Data.DataSet gs = adapt.GetRows(so,out more);

asmar · November 8, 2017, 11:02pm

I am encountering the same issue with my code.

		Ice.Lib.Searches.SearchOptions opts = new SearchOptions(SearchMode.AutoSearch);
		//Ice.Lib.Searches.SearchOptions opts = new SearchOptions(SearchMode.ShowDialog);
        opts.DataSetMode = DataSetMode.RowsDataSet;

        // limit the number of rows 
		opts.PageSize = 0;
		//string MyWhere = "SalesRepCode = '" + "CUSTSRV'" + " and " + "Key1 = '" + "164'";

		string MyWhere = string.Format("Key1 = \'164\'"); 
		opts.NamedSearch.WhereClauses.Add("TaskSeqs",MyWhere);
        
		System.Boolean morePages = false;

		// Call Adapter method (retuns a dataset dsTask):
		System.Data.DataSet dsTask = adapterTask.GetList(opts, out morePages);
		//You can also access the data in the dataset (trans in thiis case) like:
		// dsTask.Tables[0].Rows[0][1]

Key1 is a column in the Task table. I am using the adapterTask.

josecgomez · November 8, 2017, 11:38pm

The first parameters in the where clause is the table name

-Jose

Chris_Conn · November 8, 2017, 11:49pm

You can get a hint if you look in GetRows implementations in the appropriate BO (as opposed to the adapter)

base.AddTraceParam("whereClauseLaborHed", "System.String", "", whereClauseLaborHed);
		base.AddTraceParam("whereClauseLaborDtl", "System.String", "", whereClauseLaborDtl);
		base.AddTraceParam("whereClauseLaborDtlAttch", "System.String", "", whereClauseLaborDtlAttch);
		base.AddTraceParam("whereClauseLaborDtlComment", "System.String", "", whereClauseLaborDtlComment);
		base.AddTraceParam("whereClauseLaborEquip", "System.String", "", whereClauseLaborEquip);
		base.AddTraceParam("whereClauseLaborPart", "System.String", "", whereClauseLaborPart);
		base.AddTraceParam("whereClauseLbrScrapSerialNumbers", "System.String", "", whereClauseLbrScrapSerialNumbers);
		base.AddTraceParam("whereClauseLaborDtlGroup", "System.String", "", whereClauseLaborDtlGroup);
		base.AddTraceParam("whereClauseSelectedSerialNumbers", "System.String", "", whereClauseSelectedSerialNumbers);
		base.AddTraceParam("whereClauseSNFormat", "System.String", "", whereClauseSNFormat);
		base.AddTraceParam("whereClauseTimeWeeklyView", "System.String", "", whereClauseTimeWeeklyView);
		base.AddTraceParam("whereClauseTimeWorkHours", "System.String", "", whereClauseTimeWorkHours);
		base.AddTraceParam("pageSize", "System.Int32", "", pageSize);
		base.AddTraceParam("absolutePage", "System.Int32", "", absolutePage);
		base.AddTraceParam("morePages", "System.Boolean", "out", morePages);

Bart_Elia · November 9, 2017, 12:38am

Another tidbit to think about is the implementation on the server:

public UserFileTableset GetRows(
      string whereClauseUserFile, 
      string whereClauseUserComp, 
      string whereClauseUserCompExt,
      int pageSize,
      int absolutePage, 
      out bool morePages)
 {
            CurrentFullTableset = new UserFileTableset();
            Dictionary<IIceTable, string> tableWhereClauses = new Dictionary<IIceTable, string>();
            tableWhereClauses.Add(CurrentFullTableset.UserFile, whereClauseUserFile);
            tableWhereClauses.Add(CurrentFullTableset.UserComp, whereClauseUserComp);
            tableWhereClauses.Add(CurrentFullTableset.UserCompExt, whereClauseUserCompExt);

            InnerGetRows(Db, pageSize, absolutePage, out morePages, CurrentFullTableset, tableWhereClauses);
            return CurrentFullTableset;
        }

The where clause parameters are placed into a dictionary so that the clause can be validated against the columns in the tables and prevent SQL Injection, cross company isolation, etc.

I find it odd that you did not receive some form of ‘malformed sql statement’ or the like. (I don’t remember the error text off the top of my head)

asmar · November 9, 2017, 12:59am

I did use a “try” block with show exception to catch any errors but nothing is being reported.
private void CallTaskAdapterGetRowsMethod(Int32 CurrentRow, Boolean ClearGrid)
{
try
{

	// Map Search Fields to Application Fields
	EpiDataView edvTask = ((EpiDataView)(this.oTrans.EpiDataViews["Task"]));

	// We have to override the current row because its being overwritten somehow. 
	// When the list is changed the row will always be zero. So we pass it as a param. 
	edvTask.Row = CurrentRow;

	
    System.Data.DataRow edvTaskRow = edvTask.CurrentDataRow; // This is left because it should return the current row which is set to 0
	



		String LKey1 = edvTaskRow["Key1"].ToString();
    	String LSalesRepCode = edvTaskRow["SalesRepCode"].ToString();


		TaskAdapter adapterTask = new TaskAdapter(this.oTrans);
		adapterTask.BOConnect();

		// Declare and Initialize Variables
		SearchOptions opts = new SearchOptions(SearchMode.AutoSearch);
		//Ice.Lib.Searches.SearchOptions opts = new SearchOptions(SearchMode.ShowDialog);
        opts.DataSetMode = DataSetMode.RowsDataSet;

        // limit the number of rows 
		opts.PageSize = 0;
		//string MyWhere = "SalesRepCode = '" + "CUSTSRV'" + " and " + "Key1 = '" + "164'";

		string MyWhere = "(Key1 = '164')"; 
		opts.NamedSearch.WhereClauses.Add("TaskSeqs",MyWhere);
        
		System.Boolean morePages = false;

		// Call Adapter method (retuns a dataset dsTask):
		DataSet dsTask = (DataSet) adapterTask.GetRows(opts, out morePages);
		//You can also access the data in the dataset (trans in thiis case) like:
		// dsTask.Tables[0].Rows[0][1]

		// Place the dataset target table inside a dataview
		DataView dvTechBuyer = new DataView(dsTask.Tables[0]); 
		//epiUltraGridC1.DataSource = dsTask.Tables[0];

		//MessageBox.Show("dvTechBuyer row count: " + dvTechBuyer.Count.ToString());

		// Sort the data view on TaskSeqNum in descending order
		dvTechBuyer.Sort = "TaskSeqNum DESC";

	// Populate the grid to view the results from the adapter where clause.

	   DataTable tblTechBuyers = dvTechBuyer.ToTable();
	   epiUltraGridC1.DataSource = tblTechBuyers;




		// Display the first row Name			
		MessageBox.Show(dvTechBuyer[0]["Key1"] + " : "  + dvTechBuyer[0]["SalesRepCode"] + " : " + dvTechBuyer[0]["ChangeDcdUserID"]);

//adapterRow[“SalesRepCode”]

		// Cleanup Adapter Reference
		adapterTask.Dispose();

	}



	} catch (System.Exception ex)
	{
		ExceptionBox.Show(ex);
	}
}

asmar · November 9, 2017, 1:02am

Is this information in available in 10.1.400 through object explorer in customization mode or do I have to search the BO reference schema? Maybe I am making this more complicated. I just need to search for rows in the Task table based on the value in Key1 and SalesRepCode columns.

asmar · November 9, 2017, 1:10am

As is the code returns all rows in the Task table. I am not sure if I understand you suggested code example. I was following previously posted code examples on the Internet. Also, I tried to add inner params through the business logic wizard for the adapterTask method GetRows but it would give an error each time. I am using 10.1.400.16.

asmar · November 9, 2017, 1:13am

asmar · November 9, 2017, 1:18am

asmar · November 9, 2017, 1:27am

I am not finding a reference for GetRows for the Task table in my BO reference schema:

josecgomez · November 9, 2017, 1:47am

Again, the first parameter in the whereClause is the Table name (Task) not Task Seq, also why are you escaping your single quotes in the whereClause?
Try this

TaskAdapter ta = new TaskAdapter(oTrans);
ta.BOConnect();
Ice.Lib.Searches.SearchOptions opts = new SearchOptions(SearchMode.AutoSearch);
opts.DataSetMode = DataSetMode.RowsDataSet;
opts.PageSize = 0;
string MyWhere = string.Format("Key1 = '10001'");  //No need to escape the single quotes within a double quoted string
opts.NamedSearch.WhereClauses.Add("Task",MyWhere); //The first parameter is the Table name in the BO that will filter
System.Boolean morePages = false;
var dsTask =(Erp.BO.TaskDataSet) ta.GetRows(opts, out morePages); //Use Get Rows not Get List if you want to get the entire dataset. Cast to TaskDataSet so you don't use a Generic Data set makes life easier
MessageBox.Show(dsTask.Task[0].RelatedToFile);

asmar · November 9, 2017, 2:01am

Fantabulous (Goooooooooal) it worked!!!

Mazin

asmar · November 9, 2017, 2:04pm

Thank you both.

Topic		Replies	Views
GetRows Epicor BOAdapter Epicor ERP 10 e10 , epicor , customization	2	4763	May 18, 2017
whereClause on adapter not filtering? Epicor ERP 10	1	2861	May 11, 2017
GetRows on Sales Order Adapter Hanging Me Out To Dry Epicor ERP 10 sales-order-adapter	8	1991	October 30, 2020
Is it my code or the Adapter failing here? Epicor ERP 10	41	5529	April 30, 2019
LotSelectUpdateAdapter Search Epicor ERP 10 customization	4	713	May 18, 2022

GetRows not filtering

Related Topics