I am new to functions in epicor and trying to make a function that will run a few sql statements. I added the reference to System.Data.SqlClient and the using statement but I am getting this error and I am not sure what it means or how to go about resolving it: System.Drawing.Bitmap CS1069 The type name ‘SqlConnection’ could not be found in the namespace ‘System.Data.SqlClient’. This type has been forwarded to assembly ‘System.Data.SqlClient, Version=4.6.1.6, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’ Consider adding a reference to that assembly.
2 Likes
Back up, give us more context.
What db are you thinking of connecting to?
1 Like
I am connecting to my epicor db. My company is in the process of moving to kinetic and I am trying to replicate a customization that I have in classic. Here is the code that is in my classic customization:
private void FixACHChecks()
{
try
{
Cursor.Current = Cursors.WaitCursor;
connection = new SqlConnection(connectionString);
connection.Open();
cmd = connection.CreateCommand();
cmd.CommandText = "update erp.prempded set bankaccttype = 'C' where bankaccttype like 'CHK%'";
cmd.ExecuteNonQuery();
cmd.CommandText = "update erp.prempded set bankaccttype = 'S' where bankaccttype like 'SAV%'";
cmd.ExecuteNonQuery();
cmd.CommandText = "delete from erp.prchkded where (deductionID like 'SAV%' or deductionID like 'CHK%') and scheduledamt = '0' and Deductionamt = '0'";
cmd.ExecuteNonQuery();
connection.Close();
connection.Open();
cmd.Connection.CreateCommand();
cmd.CommandText = "update erp.PRChkDtl set paytypeID = PROTCode_c " +
"from erp.prchkdtl " +
"join erp.prempmas on erp.prchkdtl.EmpLink = erp.prempmas.EmpLink " +
"join erp.PREmpMas_UD on erp.PREmpMas_UD.ForeignSysRowID = erp.prempmas.SysRowID " +
"where erp.prchkdtl.CheckDate IS NULL " +
"and erp.prchkdtl.PayTypeID = 'REG' " +
"and erp.PREmpMas_UD.PROTCODE_c <> PaytypeID";
cmd.ExecuteNonQuery();
connection.Close();
Cursor.Current = Cursors.Default;
MessageBox.Show("Finished");
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
11 Likes
This is my connection string
private const String connectionString = “Data Source=mfa-db;Initial Catalog=EpicorERP;Persist Security Info=True;User ID=sa;Password=**”;
You are gonna get dogpiled 
That is so far from best practice you can’t even see it from here. Best to do data changes via a BAQ or BO if possible, and even if that isn’t possible you should be using Epicor’s DB objects
8 Likes
You are not meant to do that, and while it is technically possible to do so, I would not recommend it.
There are more sanctioned ways to solve your problem.
2 Likes
Especially after reading that sql lol.
But seriously, we are here to help.
What is the business problem you are trying to solve? We’ll show you the “proper” way to do it.
4 Likes
Can you suggest a better way to do this?
2 Likes
Yes, read one post up, we posted again at the same time.
Give us the full context on the business problem so we can advise properly.
2 Likes
Another option for mass updates is DMT. But like Kevin said, we can’t give informed answers without the business goal you are trying to achieve.
4 Likes
To me, it looks like you are trying too hard to do it the hard way… Epicor functions, and epicor BPMs are already connected to the SQL database. You simply need to access the data records you want to use. as someone above said, it is BEST to use business objects to do this, but there are times when you can also simply do queries in c# and get to the data you need. There are MANY examples of c# linq queries in this forum (search for LINQ or LINQ UPDATE)… you can see both good and bad examples.
LINQ is the c# equivalent of a SQL query, but written in C#. It allows you to query and get individual records, or entire sets of records all at once.
9 Likes
in classic we created a button in in the action’s menu for payroll that would run these few updates and delete statements instead of IT having to run them for them before they prosses payroll. I would like to do the same in kinetic. (See statements above)
1 Like
You are on the right track to use a function. @Evan_Purdy 's comment summarizes what you need to do.
There’s lots of examples on the forum that use Business Objects or DB objects. You just need to rewrite your code to use these.
1 Like
Well, its the right track if what he wants to do even makes sense. He hasn’t explained why ACH checks need to be ‘fixed’ every payroll. Its possible the root cause could be solved and this code wouldn’t even be needed.
6 Likes
Very true. I stopped reading what he was actually trying to do once I saw the SQL statements
3 Likes
Evergreen post.
1 Like
Ok, enough dogpiling. He’s gonna think he’s on Stack Overflow.
update erp.prempded set bankaccttype = 'C' where bankaccttype like 'CHK%'
update erp.prempded set bankaccttype = 'S' where bankaccttype like 'SAV%'
delete from erp.prchkded where (deductionID like 'SAV%' or deductionID like 'CHK%') and scheduledamt = '0' and Deductionamt = '0'
update erp.PRChkDtl set paytypeID = PROTCode_c
from erp.prchkdtl
join erp.prempmas on erp.prchkdtl.EmpLink = erp.prempmas.EmpLink
join erp.PREmpMas_UD on erp.PREmpMas_UD.ForeignSysRowID = erp.prempmas.SysRowID
where erp.prchkdtl.CheckDate IS NULL
and erp.prchkdtl.PayTypeID = 'REG'
and erp.PREmpMas_UD.PROTCODE_c <> PaytypeID";
Here is what he’s doing in classic, broken out in a little bit easier to read format.
@cbastijanic what is the explanation as to why this is needed?
We can probably help to fix the underlying issue, and if not, at least massage it into something more acceptable.
3 Likes
System Information
==================
AppServer Connection: https://epicor1.mi.mayfran.com/epicorerp
Form Name: Epicor Functions Maintenance
Customization Name:
Menu ID: BPMN1450
Software Version: 4.3.100.0
============
Application Error
Exception caught in: Epicor.ServiceModel
Error Detail
============
##!Message:##! Invalid token.
##!Program:##! Epicor.ServiceModel.dll
##!Method:##! CallWithCommunicationFailureRetry
Client Stack Trace
==================
at Epicor.ServiceModel.Channels.ImplBase.CallWithCommunicationFailureRetry(String methodName, ProxyValuesIn valuesIn, ProxyValuesOut valuesOut, RestRpcValueSerializer serializer)
at Epicor.ServiceModel.Channels.ImplBase.CallWithMultistepBpmHandling(String methodName, ProxyValuesIn valuesIn, ProxyValuesOut valuesOut, Boolean useSparseCopy)
at Epicor.ServiceModel.Channels.ImplBase.Call(String methodName, ProxyValuesIn valuesIn, ProxyValuesOut valuesOut, Boolean useSparseCopy)
at Ice.Proxy.Lib.EfxLibraryDesignerImpl.ApplyChangesWithDiagnostics(EfxLibraryDataSet libraryTableset, IEnumerable1& diagnostics) at Ice.UI.EfxLibraryMaintenance.Services.LibraryService.<>c__DisplayClass2_0.<ApplyChangesWithDiagnostics>b__0(EfxLibraryDesignerImpl svc) at Ice.Blaq.Client.EfxLibraryDesigner.LibraryInfoService.CallSvc(Action1 func)
at Ice.UI.EfxLibraryMaintenance.Services.LibraryService.ApplyChangesWithDiagnostics(EfxLibraryDataSet& input, IEnumerable1& diagnostics) at Ice.UI.EfxLibraryMaintenance.MainTransaction.ApplyChangesWithDiagnostics(String lastSelectedLibrary, IEnumerable1& diagnostics)
at Ice.UI.EfxLibraryMaintenance.MainTransaction.adapterUpdate()
at Ice.Lib.Framework.EpiMultiViewTransaction.Update(IEnumerable`1 dataSets)
Here are the details for the saving error of the function
I closed functions maintenance and opened it with the desktop app for kinetic and it let me save it this time. Before I had opened functions maintenance from the web app.
1 Like
Interesting, I would report that to support.