Hello experts. So far I haven’t been able to find an answer that makes sense, so I thought I would throw it out here. I’m customizing our Sales Order Entry form and I need to get the ship-to state and country from the ShipTo table. I’ve added the ShipTo adapters, but get an error running this code…
EpiDataView edvShipTo = ((EpiDataView)(oTrans.EpiDataViews[“ShipTo”]));
string sStateAbbrv = “”;
string sCountry = “”;
foreach(DataRow dr in edvShipTo.dataView.Table.Rows)
{
if (dr[“CustNum”].ToString() == edvOrdHed.dataView[edvOrdHed.Row][“CustNum”])
{
if (dr[“ShipToNum”].ToString() == edvOrdHed.dataView[edvOrdHed.Row][“ShipToNum”])
{
sStateAbbrv = dr[“State”].ToString();
sCountry = dr[“Country”].ToString();
}
}
}
I get this error…
Is the proper way to go about this? It would be easier if we could use the basic C# connection code…
SqlConnection sqlConnection1 = new SqlConnection(“Your Connection String”);
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;
cmd.CommandText = “SELECT * FROM table WHERE something = ‘something’”;
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
reader = cmd.ExecuteReader();
sqlConnection1.Close();
Thanks in advance for the help and have a great day!
1 Like
Definitely don’t need to use a SQL connection to get something like this.
It would help to see more of your code to diagnose why you’re getting this, but from what I see, you haven’t actually referenced your adapter at all. The EpiDataView is a native view to your form (in this case) and is different than an adapter.
To use an adapter, you declare it first (you must have already brought in your adapter files too).
For example, if I was using a customer adapter, it would look like this:
CustomerAdapter adapterCust = new CustomerAdapter(oTrans);
adapterCust.BOConnect();
bool result = adapterCust.GetByID(someParam);
if(result)
{
//do something
}
adapterCust.Dispose();
You can reference data from your adapter like so:
adapterCustomer.CustomerData.Customer[someIntForRowNum].FieldName
You can use the object explorer>Get Adapters to view the adapter properties and methods:
Knowing the relationships between your data will help you pass in the correct parameters and such.
Let me know if that helps or if I’m not understanding your question.
3 Likes
Let me reinforcement this statement
You “NEVER” want to make a direct SQL connection from the client. This is absolutely positively bad practice and it can lead to major headaches down the road. Not sure if I am being clear enough so i’ll say it again… NEVER EVER NEVER NEVER NEVER EVER EVER do this 
The example that @Aaron_Moreng provided is a great one, you can also do it without writing any code by using FKV (Foreign Key View)
[FKV Page 457]
https://epicweb.epicor.com/doc/Docs/EpicorCustomization_UserGuide_101600.pdf
5 Likes
Thank you Aaron! That’s what I needed. I had the adapters loaded, just wasn’t sure how to access it. Would this be the same process for accessing an Ice.UD01 table?
Thank you, Jose, for the clarification about using SQL connections. LOL
I’m a VB.Net and C# software developer trying to get used to Epicor’s way of doing things. I’m picking it up a little here and there. That PDF you attached will come in handy too.
Thanks again!
1 Like
Speaking of the UD01 table… everything for the Ship-To table works great, but when the code gets to the UD01 portion I get the error…
Here is my code…
string sStateAbbrv = “”;
string sCountry = “”;
int iNumOfDays = 0;
// Connect to the ShipTo table to get state abbreviation and country…
ShipToAdapter adapterShipTo = new ShipToAdapter(oTrans);
adapterShipTo.BOConnect();
int custNum = (int)edvOrdHed.dataView[edvOrdHed.Row][“ShipToCustNum”];
string shipToNum = edvOrdHed.dataView[edvOrdHed.Row][“ShipToNum”].ToString();
bool result = adapterShipTo.GetByID(custNum, shipToNum);
if(result)
{
sStateAbbrv = adapterShipTo.ShipToData.Tables[“ShipTo”].Rows[0][“State”].ToString();
sCountry = adapterShipTo.ShipToData.Tables[“ShipTo”].Rows[0][“Country”].ToString();
}
adapterShipTo.Dispose();
// Connect to the UD01 custom table to get the number of days based on state and country…
UD01Adapter adapterUD01 = new UD01Adapter(oTrans);
adapterUD01.BOConnect();
bool resultUD = adapterUD01.GetByID(sStateAbbrv);
if(resultUD)
{
//MessageBox.Show(adapterUD01.UD01Data.Tables[“UD01”].Rows[0][“Number01”].ToString());
MessageBox.Show(adapterUD01.UD01Data.UD01[0][“Number01”].ToString());
}
adapterUD01.Dispose();
The error is triggered at the MessageBox, and it doesn’t matter which version I use, I get the same error.
//MessageBox.Show(adapterUD01.UD01Data.Tables[“UD01”].Rows[0][“Number01”].ToString());
MessageBox.Show(adapterUD01.UD01Data.UD01[0][“Number01”].ToString());
Any ideas?
I think the UD tables have 5 keys, so you would need to pass in all 5 keys if you want to return a record:
bool resultUD = adapterUD01.GetByID(sStateAbb, “”,“”,“”,“”);
assuming your sStateAbb is Key1
Wrong, sorry, I was thinking of UD100.
Your error states that there is no row at position 0, have you checked that there is definitely data for that record in SQL though?
1 Like
You were right about the 5 keys… I changed my call per your example and it worked! Thanks for the help!
Hello Jose,
What would be the correct way to add a BO that is not directly related to custom views? For example, (see screenshot), if I want to add any JobPart field to “Job Receipt to Invetory”, I suppose I should use an intermediate table, but I don’t know how to do it, will you have an example of how to do this “join” that you can share with me?
Thank you
