Convert SQL code to C# code for optimizer rule on A/P invoice

ThijsG

I created an optimizer rule to check for duplicated BP reference numbers on A/P invoices.

This rule checks A/P invoice draft documents on BP reference numbers when you want to add a new A/P invoice.

It seems like the code is correct but it the function doesn't work.

As it is not possible to debug the SQL code I need to convert it to cSharp as I will be able to put some message boxes in.

As I am not familiar with cSharp is there anyone who would be able to convert my Optimer SQL code to cSharp so I can see what is missing in my rule. Below the code.

Thanks!

Michael Hubenschmid

Hello,

this wouild be the code:

try 
{ 
StatusBar.WriteWarning("DEBUG - Rule: " + pVal.RuleInfo.RuleName + " was triggered."); 
// Your Code 

string cardCode = TextEdit.GetFromUID(pVal.Form, "4").Value.ToString();
string refNo = TextEdit.GetFromUID(pVal.Form, "14").Value.ToString();

string checkRefNoQry = @"SELECT COUNT (T0.DocEntry) as 'CountRefNo' FROM ODRF T0 where T0.ObjType = 18 and T0.CardCode = '" + cardCode + "' and T0.NumAtCard = '" + refNo + "' and T0.DocStatus = 'O'";

using(System.Data.SqlClient.SqlDataReader sdrCheckRefNo = SwissAddonFramework.B1Connector.GetB1Connector().ExecuteQuery(checkRefNoQry))
{
if(sdrCheckRefNo.Read())
{
int countRefNo = int.Parse(sdrCheckRefNo["CountRefNo"].ToString());

if(countRefNo > 0)
{
MessageBox.Show("Ref.Number still exists!", "OK");
return false;
}
}
}
}
catch (Exception ex)
{ 
string errorMessage = string.Format("Error in {0} Rule '{1}': {2}", pVal.RuleInfo.RuleType, pVal.RuleInfo.RuleName, ex.Message);
MessageBox.Show(errorMessage, "OK"); 
StatusBar.WriteError(errorMessage); 
Debug.WriteMessage(errorMessage, Debug.DebugLevel.Exception); 
}

return true;

Kind regards,

Michael

ThijsG

Thanks you very much Michael.

The applied code in C# works fine!

Regards Thijs