Monthly Archives: April 2012

How to find your GP Company ID

Often times when working with Dynamics GP you will come across something that is asking for a GP company ID.  This can either be an integration application, or a registration program for a cool addin, or a number of other pieces that need to connect to your data.  GP has three ways to identify a company: Company Name, Database Name and Company ID.  All of these luckily can be found in one table in a database.  By selecting out of the SY01500 table we can determine how to find your GP company ID.  Here is an example query:

How to find your GP Company ID


how to find your gp company id

This will return back the Company Name as CMPNYNAM, the Database name as INTERID and the Company ID as CMPANYID.  The company ID will always be an integer, and the test company is usually -1.  The InterID is always a three letter word and potentially a number.  Example: DYN01, DYN02, etc.  The Company Name will be the value that you fill out during setup.  These tables are how to find your GP company ID and they will provide additional information about your company if you want to look further by selecting all columns.

While looking for how to find your GP company ID may seem somewhat difficult in GP it is typically best to look directly at SQL.  GP does not have a great method for looking up company information and you will likely still be asking yourself how to find your GP company ID.  Again, because this information is typically only required for an integraiton or special third party addins it is unlikely that GP will show you how to find your GP company ID anytime soon.

If you are looking for more information on how to find your GP company ID

Check out this excellent SQL posting from Victoria Yudin about how to find your GP company ID in SQL.

Prevent SQL Injection Attack With .NET

There has been a large stir recently with how to prevent SQL injection attacks with .NET.  Huge rumors are flying about viruses because of the nuclear incident that happened in Iran.  Just to be clear, regardless of how secure any site is there is a likelihood that you can be hacked.  The best we can do is prevent as much as possible so that it takes someone who is really good to do it.  At that point you are dealing with a security expert and likely they will just want you to pay them for the information.  Back on to the subject, to solve our security problem we must first ask ourselves, what is a SQL injection?  SQL injections are a trick that hackers use to execute malicious SQL scripts on your server.

Our main goal is not to take away functionality, but to prevent SQL injection attacks

Let’s say you have a login form, and you ask for a username and password.  You have a text box bound to both fields and when they hit a Login button your form code selects from the user table where user name is equal to the User text box.  The SQL might look something like this:

SELECT UserId, UserName,Password FROM Users
WHERE UserName = '" + txtUserName.Text + "'

prevent sql injection attacks
This is where a hacker can come in and where we need to prevent SQL injection attacks.  The user name a hacker would fill out would be something like this:

'; DROP DATABASE myWebApp --

When your code executes it will send a drop database command to your server destroying all of your data.  This is likely not the intent of the hacker as they would much rather send a command to validate their password or fetch data but the goal here is to prevent SQL injection.  Here are the three big steps to prevent SQL injection attacks:

Three methods to prevent SQL injection attacks with .NET

Validate your data

The first step in a SQL injection attack is to know what the developer is expecting to happen with a field and exploit it.  In reverse, the first step to prevent SQL injection attacks is to know what a hacker intends to do with a field and prevent it.  This will involve checking that your text received is the right length, scrubbing for invalid characters and make a decent attempt at stripping out dangerous SQL commands or throwing back errors if you find anything.

Use SQL Stored Procedures

Stored procedures are the next great .NET tool, because a parameter passed through a stored procedure command is sent as literal text as opposed to executed with a command.  While converting your commands to a stored procedure does not prevent SQL injection attacks it does give you an additional security layer in case the injection makes it through and is targeting specific commands.

Use Parameters with Dynamic SQL

Another way to prevent SQL injection attacks is to embed your input from forms as parameters as opposed to injecting them directly into the statement.  This can be done simply by using an @ sign as a parameter in your statement and appending a parameter to your command object.  This looks something like this:

SqlDataAdapter saoSqlAdapter = new SqlDataAdapter(
         "SELECT UserName, UserId, Password FROM Users WHERE UserName = @userName",
  myCommand.SelectCommand.Parameters.Add("@userName", SqlDbType.VarChar, 50);
  myCommand.SelectCommand.Parameters["@userName"].Value = txtUserName.Text;

Other methods to prevent SQL injection attacks

After working these three methods you should be able to prevent SQL injection attacks from mostly all attackers, if you need more information on how to prevent SQL injection attacks check out how to prevent sql injection attacks on MSN.

XML to XML – Translating XML with C#

Working with XML documents is the new way of EDI in today’s business work.  XML to XML provides a way to not only translate data that you want to work with, but also provides a way to store extreme amounts of information in a structured manner.  In this article I will discuss how to use XML in two forms, XML to XML or moving one XML document to another XML document and translating it along the way and XML to Object.  XML to Objects will be used in the XML to XML process but it will also help with keeping your XML uniform as well.

The most important piece in XML to XML is uniform documents

Starting out with a fresh document is very important in being successful in translation.  Here is an example of a simple yet effective XML document:

xml to xml job definition

The first part of XML to XML is to load the document into a XmlDocument file.  We do this through instantiating a XmlDocument and calling the Load method targeting our file.  The XmlDocument will be used to populate our new document and then write out to finish our XML to XML process.

            XmlDocument xmlJobDoc = new System.Xml.XmlDocument();
            XmlDocument xmlLoadDocument = new System.Xml.XmlDocument();
            XmlNode xmlJobNode;
            XmlAttribute xmlAttribute;
            XmlNode xmlRootNode = xmlLoadDocument.CreateElement("Root");
            for (int i = 0; i < 2; i++)
                xmlJobNode = xmlLoadDocument.CreateElement("TruckRun");
                xmlAttribute = xmlLoadDocument.CreateAttribute("JobNumber");
                xmlAttribute.Value = i.ToString();


Xml to Xml utilizing Xml ImportNode Command

This will utilize the Xml to Xml specific command called ImportNode. ImportNode is powerful as it allows you to copy one specific section of an Xml document to another Xml document or use the Xml to Xml functionality as if you were copy and pasting. To finish up the Xml to Xml translation, we utilize the Save method on the Xml document and push the information to the specified file.  If you would like to learn more about translating Xml documents from Xml to Xml or other Xml processing capabilities of Microsoft dotNet Framework, check out this MSDN article.

HOW TO: Write Your First Outlook 2010 Addin

Writing an Outlook 2010 Addin is a very easy task given the tools that Microsoft provides.  While it does require some knowledge about what you are doing and where you are going, overall the Outlook 2010 addin functionality is a robust system that gives you the flexibility you need to get the information required.

I am a big supporter of Outlook as an email client as well as a scheduling tool.  A huge advantage that Microsoft Office has over most of the competition is the ability to easily write addins and communicate with all other tools that are Office related.  An Outlook 2010 addin is no exception to this and it provides a great way to embed ideas and activities into your own interface without much work.  Developing an Outlook 2010 addin has been designed to be so easy that you can start right away simply by installing Visual Studio 2010 and Office 2010.

Creating an Outlook 2010 Addin

To get started an Outlook 2010 addin, Open up the New Solution window and navigate to the Office -> 2010 Section of your preferred language.

outlook 2010 addin - new  solution window

As a test, let’s create a custom tab in our Outlook and use it to pop a Hello World window.  To do this, open the Add New Item window and navigate to Office -> Ribbon (Visual Designer)

outlook 2010 addin - new  item window

How to implement an Outlook 2010 Addin that shows a new ribbon

To choose which Ribbon you are modifying, select the Ribbon Type on the right hand side.  For this example I chose the Explorer Ribbon so it shows up directly on the Office 2010 main ribbon.

On the left side of the Ribbon Designer you will get some nice pre-built Microsoft Office controls to use, simply drag a button onto the Ribbon.

outlook 2010 addin - new  ribbon button

At this point simply double click your button to bring up an event and type in the Message Box command to print Hello World.  If your Outlook is running you will need to close it to deploy.  Once closed, run your add-in through debug mode and it will pop up Outlook with your added loaded into it.

outlook 2010 addin - hello world

Writing an Outlook 2010 addin can be very simple or very complex based on what you want to do with it.  Just like with any coding you should start easy and have fun to start.  If you are interested in more information about writing an Outlook 2010 addin check out this MSDN article for more detail.

What’s New In Microsoft Dynamics GP 12 / GP 2013?

Almost any accounting system on the market has one major issue with it, the human interaction.  Regardless of how automated each system becomes there will always have to be someone who looks at the data and makes changes based on what happens in the real world.  Being that the real world is now involved in your accounting system it is destined to have some kind of error that needs to be corrected.  With the release of Microsoft Dynamics GP 2013 we should expect to see new advanced interaction with error handling and financial recovery.  Here are a few new features of Dynamics GP 2013 that will help keep your financial data secure:

  • Enhanced Reconciliation: At any given point in time there will likely be an account out of balance.  With the new reconciliation system we are going to see a filter based system to show us exactly what needs to be reviewed and when.  Easily setup conditions to review your GL, un-posted and posted batches and any other nitty gritty information that will flaw your perfect system.
  • Mass Fixed Asset Depreciation Reversal: Once your issues are identified it can be a nightmare to roll back your books to a specific point in time to correct it.  Mass Fixed Asset Depreciation Reversal is the process of rolling back your transactions to a point in time all at once instead of having to do it one by one.  In this transactional system your ability to control and correct errors is greatly increased.

Along with the enhanced data security, GP 12 is also moving towards more intuitive warehouse functionality.  Here are some highlights of processes that will be improved with the release of GP 2012:

  • Intuitive Serial/Lot Number Tracking: Drop ship items have always been a sore spot for entering tracking/serial numbers in GP.  When you drop ship an item and you are not tracking inventory you are guessing which item ships.  Seeing this as a flaw, Microsoft has corrected this and provides you the ability to enter what specific item shipped regardless of drop shipping or not.  Kudos, Microsoft!
  • Business Analyzer: A great addition to the already stellar dashboard is the Business Analyzer tool now available on the web!  This tool gives you access to business critical key performance indicators as well as offer you the ability to collaborate and take action all from one interface.

If you have any questions about GP 2013 or would like to talk about upgrades, please don’t hesitate to contact us at Integrated Systems Solutions for more information or call us direct at 714-634-4697 and mention this blog!