Jump to content

Willkommen im Coresystems Community Forum

Dieses Forum wird nicht mehr weiter betrieben.
Sollten Sie eine Lösung für eine spezifische Aufgabenstellung suchen wenden Sie sich bitte an unseren Support: helpdesk.coresystems.ch.
Für allgemeine Produktinformationen kontaktieren Sie unser Sales-Team: sales@coresystems.ch

Welcome at the coresystems community forum

This forum is no longer being maintained.
If you need a specific solution please contact our support: helpdesk.coresystems.ch.
For general product information do not hesitate to contact our sales team: sales@coresystems.ch


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

c# cs code csharp sql

  • Please log in to reply
2 replies to this topic

#1 ThijsG


    New Member

  • Members
  • 4 posts

Posted 12 February 2016 - 16:54

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.



@Ref AS NVARCHAR(100) 
SET @CardCode = '[%G;4]'
SET @Ref = '[%G;14]'
select case when (SELECT COUNT (T0.DocEntry) FROM ODRF T0 where T0.ObjType = 18 and T0.CardCode = @CardCode and 
T0.NumAtCard = @Ref and T0.DocStatus = 'O') = 0
then NULL
   ELSE 1

#2 Michael Hubenschmid

Michael Hubenschmid

    Advanced Member

  • Members
  • PipPipPip
  • 213 posts
  • LocationSwitzerland

Posted 18 February 2016 - 13:47



this wouild be the code:

	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))
			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"); 
	Debug.WriteMessage(errorMessage, Debug.DebugLevel.Exception); 

return true;

Kind regards,



Freundliche Grüsse

Michael Hubenschmid
ERP Lösungen

MTF Schaffhausen AG
Bachstrasse 44 - CH8201 Schaffhausen - Switzerland
phone:+41 52 632 33 42 fax: +41 52 632 33 34 mobile: +41 79 693 80 04
email: michael.hubenschmid@mtf.ch |web: www.mtf-sh.ch

P Save Trees.
Please consider the environment before printing this post. Print only when necessary!

#3 ThijsG


    New Member

  • Members
  • 4 posts

Posted 11 March 2016 - 14:42

Thanks you very much Michael.

The applied code in C# works fine!

Regards Thijs

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users