C# Help with Adapter GetByID

markdamen · September 21, 2017, 4:43pm

Hi

Please can somebody put me out of my misery - I’ve tried multiple ways to get this working, and I give up!

Basically inside the method btnPrintPartLabel_Click, I would like to get data from the Part record. I wish to get the value from Part.EngPartCat_c and Part.EngPartSubCat_c. With current code, I get no errors on compile or runtime but the sCategory variable always remains blank.

Many Thanks
Mark

// **************************************************
// Custom code for InventoryQtyAdjustForm
// Created: 18/08/2017 14:39:46
// **************************************************
using System;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Windows.Forms;
using Erp.Adapters;
using Erp.UI;
using Ice.Lib;
using Ice.Adapters;
using Ice.Lib.Customization;
using Ice.Lib.ExtendedProps;
using Ice.Lib.Framework;
using Ice.Lib.Searches;
using Ice.UI.FormFunctions;

using Erp.BO;
using Ice.BO;

using System.Text;

public class Script
{
// ** Wizard Insert Location - Do Not Remove ‘Begin/End Wizard Added Module Level Variables’ Comments! **
// Begin Wizard Added Module Level Variables **

            // End Wizard Added Module Level Variables **

            // Add Custom Module Level Variables Here **

            private EpiDataView edvPart;
            private PartAdapter partAD ;


            public void InitializeCustomCode()
            {
                            // ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Variable Initialization' lines **
                            // Begin Wizard Added Variable Initialization

                            // End Wizard Added Variable Initialization

                            // Begin Wizard Added Custom Method Calls

                            this.btnPrintBinLabel.Click += new System.EventHandler(this.btnPrintBinLabel_Click);
                            this.btnPrintPartLabel.Click += new System.EventHandler(this.btnPrintPartLabel_Click);

                            EpiDataView edvPart = ((EpiDataView)(this.oTrans.EpiDataViews["Part"]));

                            edvPart.dataView.Table.Columns.Add("Category");
                            edvPart.dataView.Table.Columns.Add("SubCategory", typeof(string));

                            //partAD = new PartAdapter( oTrans ) ;
                            //partAD.BOConnect() ;

                            // End Wizard Added Custom Method Calls
            }

            public void DestroyCustomCode()
            {
                            // ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Object Disposal' lines **
                            // Begin Wizard Added Object Disposal

                            this.btnPrintBinLabel.Click -= new System.EventHandler(this.btnPrintBinLabel_Click);
                            this.btnPrintPartLabel.Click -= new System.EventHandler(this.btnPrintPartLabel_Click);
                            // End Wizard Added Object Disposal

                            // Begin Custom Code Disposal

                            this.edvPart = null;

                            //partAD.Dispose() ;
                            partAD = null ;

                            // End Custom Code Disposal
            }

            private void btnPrintBinLabel_Click(object sender, System.EventArgs args)
            {
                            string [] Lines = new string [4];
                            string FileName = string.Empty;
                            string LabelPrinter = "";
                            string LabelReport = "EngineeringBinLabel.btw";
                            string OutputPath = @"\\EpicorAPP01\BarTender\Commander\EngineeringBin\";
                            DateTime cNow = DateTime.Now;

                            EpiCombo cmbWhse = (EpiCombo)csm.GetNativeControlReference("17fd807c-51fc-431c-a72c-e300fb8208ac");
                            EpiTextBox txtBinNum = (EpiTextBox)csm.GetNativeControlReference("5a5538e6-53a1-4cc4-8de5-569c6b8e70c1");
                            EpiTextBox txtDescription = (EpiTextBox)csm.GetNativeControlReference("a030367f-9dbd-4b73-9efc-e39ef8628478");

                string sWhse = cmbWhse.Text;
                            string sBinNum = txtBinNum.Text;
                            string sDescription = txtDescription.Text;

                            if(sWhse != "Engineering Stock")
                {
                               EpiMessageBox.Show("Only intended for Engineering Parts");
                                              return;
                }

                            FileName = OutputPath + "EngBinLabel" + cNow.ToString ("_yyyy-MM-dd_HHmmss") + ".bt";

                            LabelPrinter = @"\\BV-PC-001\DYMO";

                            using (var OutFile = new System.IO.StreamWriter(new System.IO.FileStream(FileName,System.IO.FileMode.Create)))
                            {
                                            Lines[0] = "%BTW% ";
                                            Lines[0] = Lines [0] + "/AF=\"" ;
                                            Lines[0] = Lines [0] + @"\\epicorapp01\BarTender\Commander\EngineeringBin\" + LabelReport +"\"";
                                            Lines[0] = Lines [0] + @" /D=""<Trigger File Name>"" /PRN=";
                                            Lines[0] = Lines [0] +  "\"" + LabelPrinter + "\"";
                                            Lines[0] = Lines [0] + @" /DBTEXTHEADER=3 /R=3 /P";
                                            Lines[1] = "%END%";
                                            Lines[2] = "\"BinNum\",\"Description\"";

                                            StringBuilder sb = new StringBuilder();

                                            if (sBinNum.IndexOfAny(new char[] { '"', ',' }) != -1)
                                                            sb.AppendFormat("\"{0}\"", sBinNum.Replace("\"", "\"\""));
                                            else
                                                            sb.Append(sBinNum);

                                            sb.Append(",");

                                            if (sDescription.IndexOfAny(new char[] { '"', ',' }) != -1)
                                                            sb.AppendFormat("\"{0}\"", sDescription.Replace("\"", "\"\""));
                                            else
                                                            sb.Append(sDescription);

                                            Lines[3] = sb.ToString();

                                            OutFile.WriteLine(Lines[0]);
                                            OutFile.WriteLine(Lines[1]);
                                            OutFile.WriteLine(Lines[2]);
                                            OutFile.WriteLine(Lines[3]);
                            }
            }

            private void btnPrintPartLabel_Click(object sender, System.EventArgs args)
            {

                            string [] Lines = new string [4];
                            string FileName = string.Empty;
                            string LabelPrinter = "";
                            string LabelReport = "EngineeringPartLabel.btw";
                            string OutputPath = @"\\EpicorAPP01\BarTender\Commander\EngineeringPart\";
                            DateTime cNow = DateTime.Now;

                            EpiTextBox txtPartNum = (EpiTextBox)csm.GetNativeControlReference("138c27f2-8793-4d33-bb53-e1f5a753909a");
                            EpiTextBox txtDescription = (EpiTextBox)csm.GetNativeControlReference("f59e7876-bacd-4314-b354-bae4f83ebf9c");

                            string sPartNum = txtPartNum.Text;
                            string sDescription = txtDescription.Text;
                            string sCategory = string.Empty;

                            // Check if the Part is an Engineering part, if not then return
                            if(sPartNum.Length < 3)
                            {
                                              EpiMessageBox.Show("Only intended for Engineering Parts");
                                              return;
                            }

                            string First3Letters = sPartNum.Substring(0, 3);

                            if(First3Letters != "ENG")
                                {
                                              EpiMessageBox.Show("Only intended for Engineering Parts");
                                              return;
                              }

                            // Lookup Part Eng Category, and add into dataView

                            partAD = new PartAdapter( oTrans ) ;
                            partAD.BOConnect() ;

                             try
                            {
                                            partAD.ClearData() ;
                                             partAD.GetByID(sPartNum);
                                            edvPart.dataView[0]["Category"] = partAD.PartData.Part[0]["EngPartCat_c"] ;
                                              //sCategory = edvPart.dataView[0]["EngPartCat_c"].ToString();
                                              sCategory = partAD.PartData.Part[0]["AnalysisCode"].ToString();
                            }
                            catch (Exception ex)
                            {
                            }

                            partAD.Dispose() ;
                            partAD = null ;

                            EpiMessageBox.Show("cat = " + sCategory);

                            //EpiCombo txtCategory = (EpiCombo)csm.GetNativeControlReference("fc9a3fb0-dd3f-4d09-9d65-724d41f1c4e2");
                            //BAQCombo txtSubCategory = (BAQCombo)csm.GetNativeControlReference("50be7f9f-96ec-489c-8457-9f81078443ea");

                            //string sSubCategory = txtSubCategory.Text;


                            FileName = OutputPath + "EngPartLabel" + cNow.ToString ("_yyyy-MM-dd_HHmmss") + ".bt";

                            LabelPrinter = @"\\BV-PC-001\DYMO";

                            /*using (var OutFile = new System.IO.StreamWriter(new System.IO.FileStream(FileName,System.IO.FileMode.Create)))
                            {
                                            Lines[0] = "%BTW% ";
                                            Lines[0] = Lines [0] + "/AF=\"" ;
                                            Lines[0] = Lines [0] + @"\\epicorapp01\BarTender\Commander\EngineeringPart\" + LabelReport +"\"";
                                            Lines[0] = Lines [0] + @" /D=""<Trigger File Name>"" /PRN=";
                                            Lines[0] = Lines [0] +  "\"" + LabelPrinter + "\"";
                                            Lines[0] = Lines [0] + @" /DBTEXTHEADER=3 /R=3 /P";
                                            Lines[1] = "%END%";
                                            Lines[2] = "\"PartNum\",\"Description\",\"Category\",\"SubCategory\"";

                                            StringBuilder sb = new StringBuilder();

                                            if (sPartNum.IndexOfAny(new char[] { '"', ',' }) != -1)
                                                            sb.AppendFormat("\"{0}\"", sPartNum.Replace("\"", "\"\""));
                                            else
                                                            sb.Append(sPartNum);

                                            sb.Append(",");

                                            if (sDescription.IndexOfAny(new char[] { '"', ',' }) != -1)
                                                            sb.AppendFormat("\"{0}\"", sDescription.Replace("\"", "\"\""));
                                            else
                                                            sb.Append(sDescription);

                                            sb.Append(",");

                                            if (sCategory.IndexOfAny(new char[] { '"', ',' }) != -1)
                                                            sb.AppendFormat("\"{0}\"", sCategory.Replace("\"", "\"\""));
                                            else
                                                            sb.Append(sCategory);

                                            sb.Append(",");

                                            if (sSubCategory.IndexOfAny(new char[] { '"', ',' }) != -1)
                                                            sb.AppendFormat("\"{0}\"", sSubCategory.Replace("\"", "\"\""));
                                            else
                                                            sb.Append(sSubCategory);

                                            Lines[3] = sb.ToString();

                                            OutFile.WriteLine(Lines[0]);
                                            OutFile.WriteLine(Lines[1]);
                                            OutFile.WriteLine(Lines[2]);
                                            OutFile.WriteLine(Lines[3]);
                            }*/

            }

}

Aaron_Moreng · September 21, 2017, 5:15pm

In your code, you’re assuming that there is a valid value for the variable sPartNum and because you’re setting sCategory inside a try/catch, you won’t see any runtime errors when you pass in a bad value into your GetByID(sPartNum) method.

markdamen · September 21, 2017, 6:51pm

Thanks Aaron. You’ve moved my understanding along a bit. I have the PartAD lookup working now. Moved on to struggle with the Category lookup. I’m trying to lookup the CodeDesc from UDCodes.

Here is the code. The error is “There is now row a position 0” – which I take to mean that it hasn’t found a row. Is there something wrong with the DatasetMode, or parameters?

// Lookup Category Description from UserCodes
usercodeAD = new UserCodesAdapter( oTrans ) ;
usercodeAD.BOConnect() ;

                try
                {
                               usercodeAD.ClearData() ;

                               bool morePages;
                               SearchOptions opts = new SearchOptions(SearchMode.AutoSearch);
                               opts.DataSetMode = DataSetMode.RowsDataSet;
                               opts.PageSize = 1; //return this many

                                              string whereCodeTypeID = string.Format("CodeTypeID = '{0}'", "ENGPARTCAT");
                                              string whereCodeID = string.Format("CodeID = '{0}'", sCatID);
                               opts.NamedSearch.WhereClauses.Add("UDCodeType", whereCodeTypeID);
                               opts.NamedSearch.WhereClauses.Add("UDCodes", whereCodeID);

                               usercodeAD.GetRows(opts, out morePages);
                               sCategory = usercodeAD.UserCodesData.UDCodes[0]["CodeDesc"].ToString();
                                              //MessageBox.Show(sCategory);
                            }
                            catch (Exception ex)
                            {
                                            MessageBox.Show("An error has occured - " + ex.Message);
                }
                            finally
                            {
                                            usercodeAD.Dispose();
                                            usercodeAD = null;
                            }

I took the names of the whereClauses from the BL Tester:

[/uploads/default/original/2X/2/2c8842fa32f4c4dba13a88005988be9bac325217.png]

Many Thanks!

Aaron_Moreng · September 21, 2017, 7:04pm

Can you try invoking a filter like this:

SearchOptions opts = new SearchOptions(SearchMode.AutoSearch);
opts.PreLoadSearchFilter = String.Format(“CodeTypeID = ‘ENGPARTCAT’ and CodeID = ‘{0}’”, sCatID);
opts.DataSetMode=DataSetMode.RowsDataSet;
usercodeAD.InvokeSearch(opts);

I think this returns a data table…

Chris_Conn · September 21, 2017, 7:26pm

Often times if I am trying to nail down a problem, I’ll simplify things. If possible, reduce your criteria down by one (for example codetype only). Also, like Aaron does in his example above, hardcode the codetype.

Finally, if you have VisualStudio - you can run the customization in DEBUG mode (click on the button on the bottom left of the customization select screen).

markdamen · September 21, 2017, 7:35pm

So, if I use the code you gave then it produces an error (the first bit, “An error has occurred” is from my exception handling):
[/uploads/default/original/2X/9/94c6810ec84cef60fc893557089ddfd7a0de6e96.png]

If I change it slightly, to the below code then I do get a data table returned, and I can access the CodeDesc. BUT, it’s always just the first code alphabetically within that CodeTypeID

opts.PreLoadSearchFilter = “CodeTypeID = ‘ENGPARTCAT’”;

sCategory = usercodeAD.UserCodesData.UDCodes[0][“CodeDesc”].ToString();

I know that it’s telling me it doesn’t understand the column name CodeID, but I can’t see why not!! That’s what the BL Tester shows!

markdamen · September 21, 2017, 8:16pm

So I can conclude that the InvokeSearch method is only returning CodeTypeID List - which is why it errors out when trying to select the CodeID column - this is similar to the method on the BL Tester called GetList.

I still don’t know why GetRows doesn’t work - on BL Tester it shows this:
GetRows(whereClauseCodeTypeID, whereClauseUDCodes, Int32 pageSize, Int32 absolutePage, out Boolean morePages). But, in C# coding it says no GetRows method takes 5 arguments. I would say that is not the case, as the BL Tester clearly shows it taking 5 arguments. On the BL Tester, I can provide where clauses in both CodeTypeID and UDCodes and it works perfectly returning a single row.

What I ended up getting to work, it overkill but see what you think:

    // Lookup Category Description from UserCodes
usercodeAD = new UserCodesAdapter( oTrans ) ;
usercodeAD.BOConnect() ;
			
    try
    {
   	   usercodeAD.ClearData() ;
   	   
   	   bool morePages;
   	   SearchOptions opts = new SearchOptions(SearchMode.AutoSearch);
   	   opts.DataSetMode = DataSetMode.RowsDataSet;
   	   opts.PageSize = 100; //return this many
   	   opts.PreLoadSearchFilter = "CodeTypeID = 'ENGPARTCAT'";
   	   opts.DataSetMode = DataSetMode.RowsDataSet;
   	   usercodeAD.InvokeSearch(opts);			
		  	   	   
		  for (int i = 0; i < usercodeAD.UserCodesData.UDCodes.Count; i++)
          {
              if (usercodeAD.UserCodesData.UDCodes[i]["CodeID"].ToString() == sCatID)
			  {
			  	 sCategory = usercodeAD.UserCodesData.UDCodes[i]["CodeDesc"].ToString();
			  	 break;
			  }
		  }
		  			  
	} 
	catch (Exception ex) 
	{
		MessageBox.Show("An error has occured: " + ex.Message);
    }
	finally
	{
		usercodeAD.Dispose();
		usercodeAD = null;
	}

Aaron_Moreng · September 21, 2017, 9:03pm

FYI, none of your pictures are uploading
That’s definitely overkill for what you’re needing. Let me dig through my code and find out why this isn’t working for you. Stay tuned…

Chris_Conn · September 21, 2017, 9:19pm

It’s because the call doesn’t have that format originally (i.e. it’s recalled )

// Ice.Adapters.UserCodesAdapter
protected override DataSet OnGetRows(SearchOptions opts, out bool MorePages, params string[] whereClauses)
{
	if (whereClauses.Length != 2)
	{
		throw new TargetParameterCountException();
	}
	return this.userCodesBO.GetRows(whereClauses[0], whereClauses[1], opts.PageSize, opts.AbsolutePage, out MorePages);
}

So it appears you must have those 2 where clauses added to a SearchOpts object (like you’ve done before)

                               SearchOptions opts = new SearchOptions(SearchMode.AutoSearch);
                               opts.DataSetMode = DataSetMode.RowsDataSet;
                               opts.PageSize = 1; //return this many

                                              string whereCodeTypeID = string.Format("CodeTypeID = '{0}'", "ENGPARTCAT");
                                              string whereCodeID = string.Format("CodeID = '{0}'", sCatID);
                               opts.NamedSearch.WhereClauses.Add("UDCodeType", whereCodeTypeID);
                               opts.NamedSearch.WhereClauses.Add("UDCodes", whereCodeID);
                 bool more = false;
                 DataSet YourResult = usercodeAD.GetRows(opts, out more);  /* CALLING IT */

Also note it appears to returns a DataSet - You can crawl through it and get what you want:

foreach(DataRow row in YourResult.Tables[0].Rows)
{
    MessagBox.Show(row["AValidColumnName"].ToString() );
}

However it seems like you only expect one result - maybe throw an error if YourResult.Tables[0].Rows.Count != 1

Aaron_Moreng · September 21, 2017, 9:58pm

Ok this one is tricky, so I’m not surprised we are having issues.

Try this code:

  bool morePages = false;
  UserCodesAdapter adapterUserCodes = new UserCodesAdapter(oTrans);
  adapterUserCodes.BOConnect();
  SearchOptions opts = new SearchOptions(SearchMode.AutoSearch);
  string whereClause = "Your WhereClause Here";
  opts.NamedSearch.WhereClauses.Add("UDCodes", whereClause);
  DataSet dsUserCodes = adapterUserCodes.GetRows(opts, out morePages);
  if(dsUserCodes.Tables[0].Rows.Count>0)
  {

  	MessageBox.Show("DataSet: " + dsUserCodes.Tables[0].Rows.Count.ToString());

  }
  adapterUserCodes.Dispose();

I noticed from debugging that the adapter data doesn’t get filled out with the GetRows call. Instead, I threw the results into a dataset and used that dataset to pull data from.

Chris_Conn · September 21, 2017, 10:10pm

But you have to ensure you send it both whereclauses or it wont work. See above.

kjavid · September 22, 2017, 12:30am

Yup, that’l do it. hasbtable and subtable search for this one. failrly standerized code.

markdamen · September 27, 2017, 8:44am

Thanks for all the help on this, another great example of the amazing knowledge and willingness to share it on this forum!

Chris_Conn · September 27, 2017, 1:22pm

But the million dollar question is - Did it work? NM - I see the solution mark. Awesome!

Topic		Replies	Views
Access field information in AfterAdapterMethod "GetByID" method Epicor ERP 10	2	2756	June 30, 2017
LotSelectUpdateAdapter Search Epicor ERP 10 customization	4	699	May 18, 2022
C# syntax help within E10 customization layer Epicor ERP 10 customization	4	997	August 16, 2021
Oh No More Simple searching guys Epicor ERP 10 e10	17	1371	January 3, 2019
Is it my code or the Adapter failing here? Epicor ERP 10	41	5401	April 30, 2019

C# Help with Adapter GetByID

Related Topics