Resolving Single Quote Errors When Inserting Data into Dynamics GP via SmartConnect

When inserting data into Microsoft Dynamics GP using SmartConnect (on-premise), records containing apostrophes or single quotes in field values may sometimes fail to import. This article explains why this occurs and how to resolve it.

Affected Product

• SmartConnect (on-premise)
• Microsoft Dynamics GP

Cause

SmartConnect sends data to Dynamics GP through the Microsoft eConnect API, which is a collection of SQL stored procedures written by Microsoft to replicate the behavior of the GP user interface. SmartConnect reads the source data, builds an eConnect XML document, submits it to the local eConnect runtime service, and that service passes the data to SQL Server where the appropriate stored procedure executes.

In SQL, single quotes are used to denote the boundaries of a string value. When a field value itself contains a single quote (apostrophe), SQL interprets it as the end of the string, causing the statement to break and the import to fail.

Example: Consider a city value of VAL-D'OR. SQL would generate a statement like this:

UPDATE RM00101 SET Address1='123 Main Street', CITY='VAL-D'OR', State='ND' WHERE CUSTNMBR='AARONFIT0001'

SQL interprets the single quote inside VAL-D'OR as the closing quote of the CITY string, truncating the statement to:

UPDATE RM00101 SET Address1='123 Main Street', CITY='VAL-D'

This results in a SQL syntax error and the record fails to insert.

Resolving Single Quote Errors

The fix is to “double-up” any single quotes in the data before it is passed to the stored procedure. SQL recognizes two consecutive single quotes ('') as a literal apostrophe within a string, rather than the end of the string.

Corrected SQL statement:

UPDATE RM00101 SET Address1='123 Main Street', CITY='VAL-D''OR', State='ND' WHERE CUSTNMBR='TECHNOLOGIES ELEMENT PSW INC.'

There are two ways to apply this fix in SmartConnect:

Option 1: Manually Update the Source File

If your source data is an Excel spreadsheet or CSV file, you can use the application’s built-in Find & Replace function to replace all single quotes with two single quotes before importing.

1. Open your source file in Excel or a text editor.
2. Open Find & Replace (Ctrl+H).
3. In the Find field, enter a single quote: '
4. In the Replace field, enter two single quotes: ''
5. Click Replace All.
6. Save the file and re-run the SmartConnect map.

Option 2: Use a SmartConnect Calculated Field

For a programmatic solution that handles the replacement automatically at runtime, create a calculated field in SmartConnect.

1. In your SmartConnect map, create a new Calculated Field.
2. Enter the following script, replacing _shipToCity with the name of your source field:

Return _shipToCity.Replace("'", "''")

3. Map the calculated field in place of the original source field in your mapping

Note: This same script applies to any field that may contain single quotes. For example, a customer name like Mike's Hot Fudge Sundae's contains two apostrophes, and the single line above will correctly replace all occurrences automatically.

Additional Notes

• This issue can affect any string field passed to SQL, not just city fields. Apply the calculated field fix to any field in your source data that may contain apostrophes (e.g., customer names, addresses, descriptions).
• The calculated field approach is recommended for ongoing imports where the source data cannot be easily pre-processed.

Have questions on resolving single quote errors when inserting data into Dynamics GP via SmartConnect? Please reach out to our support team at support@eonesolutions.com.