This is a reposting of an article Chris Roehrich originally wrote on my Developing for Dynamics GP blog.
I had a recent support incident where the customer was trying to update a field to a blank value using the eConnect serialization assembly and the value was not being removed. Specifically, they were attempting to remove the existing value in the ADDRESS2 column of the RM00101 table by setting the string value to a blank value. The reason why this was happening was really by design with the eConnect stored procedures. If you do not pass a value, eConnect will use the setup in Dynamics GP to default the value if one exists. This works great because then as a developer you do not have to worry about passing everything in with the XML document. That would be painful to have to pass everything in the XML document for being fearful of having data being removed.
The problem here is how can you create a XML document so you can remove a value like the ADDRESS2 value? The answer is with a CDATA tag. The eConnect help file contains a section called Special characters in eConnect XML documents that discusses this scenario. Here it mentions that you can use a CDATA tag to remove data from a field. To clear data from a field, create an eConnect XML document that updates the targeted record. Use a CDATA tag that contains a blank space to populate the eConnect element that represents the field. The following example uses a CDATA tag to clear the ADDRESS2 field of a customer. Notice how the CDATA tag contains a single blank space:
The goal is to be able to serialize a XML document with using the CDATA tag that contains a space like above. Unfortunately, you cannot just set the ADDRESS2 property to a string value like “<![CDATA[ ]]>” otherwise the XML document ends up looking like the following:
This will produce an ADDRESS2 string value of <![CDATA[ ]]> for the customer record in the database instead of a blank value! The solution to this problem is to load the XML document before you pass it to the eConnect_EntryPoint method. Using the System.Xml namespace you can create a XmlElement called ADDRESS2, and set the InnerXml property to a string value of “<![CDATA[ ]]>” like the following strategy:
//Add blank CDATA tag to ADDRESS2 property //Add a using directive to System.Xml namespace string customerDocument; string sConnectionString; eConnectMethods eConCall = new eConnectMethods(); XmlDocument xmldoc = new XmlDocument(); xmldoc.Load("Customer.xml"); XmlElement myElement = xmldoc.CreateElement("ADDRESS2"); myElement.InnerXml = "<![CDATA[ ]]>"; XmlNode xmlNode = xmldoc.SelectSingleNode("//taUpdateCreateCustomerRcd"); xmlNode.AppendChild(myElement); xmldoc.Save("Customer.xml"); customerDocument = xmldoc.OuterXml; sConnectionString = "data source=SQL2008;initial catalog=TWO;integrated security=SSPI;persist security info=False;packet size=4096"; eConCall.eConnect_EntryPoint(sConnectionString, EnumTypes.ConnectionStringType.SqlClient, customerDocument, EnumTypes.SchemaValidationType.None, "");
When you implement this strategy of creating an XmlElement for the desired node, be careful not to also set a value for the property in the eConnect serialization object otherwise the XML document will have duplicate elements.
I hope you find this information useful in attempting to blank out existing values with eConnect.
This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.