.Net, ADO.NET, ASP.NET, Error Handling, Programming, SQL, XML

InvalidCastException – Failed to convert parameter value from a XmlDocument to a String

InvalidCastException
InvalidCastException was unhandled by user code

If you are working with XML data types in your .NET and SQL Server code, then you will most likely encounter this error message at some point.

Specifically, this error is related to improperly passing an XML value as a SQLParameter to your database.

Since most of the work one does with XML in .NET is done using the XmlDocument object, it is certainly surprising when Visual Studio complains when one tries to save the XML object as an XmlDocument to the database (say to a stored procedure or directly to a database table).

The bottom line is that you must adjust your  XmlDocument object  to a format compatible with the SqlDbType.XML accepted by your SqlParameters.

There are a few ways that you can use to pass your XML object to your database:

1) The first way would be simply to convert the object to a String and then receive the object as a string parameter in a stored procedure which can then cast it to the XML data type, and save it to a table.

2) The second way is to use the XmlNodeReader object. This is quite straightforwad and can be done as follows:
‘ Create the XmlDocument object (in this case from a session object)
Dim xmlDocSave As New XmlDocument()
xmlDocSave.LoadXml(CStr(Session(“xml”)))

‘ Set up a new XmlNodeReader populated with the XmlDocument object
Dim xdrSave As New XmlNodeReader(xmlDocSave)

‘ Add the XmlNodeReader to your XML parameter
Dim paramSave As New SqlParameter()
paramSave.DbType = DbType.Xml
paramSave.ParameterName = “SaveMyXML”
paramSave.Value = xdrSave

3) A third way is to import the XmlDocument’s OuterXml into a StringReader and then to populate an XmlTextReader with the contents of the String

' Create the XmlDocument object (in this case from a session object)
Dim xmlDocSave As New XmlDocument()
xmlDocSave.LoadXml(CStr(Session("xml")))

' Set up a new StringReader populated with the XmlDocument object's outer Xml
Dim srObject As New StringReader(xmlDocSave.OuterXml)

' Set up a new XmlTextReader populated with the Xml from the StringReader
Dim xtrSave As New XmlTextReader(srObject)
' Add the XmlTextReader to your XML parameter
 Dim paramSave As New SqlParameter()
 paramSave.DbType = DbType.Xml
 paramSave.ParameterName = "SaveMyXML"
 paramSave.Value = xtrSave
Advertisements

1 thought on “InvalidCastException – Failed to convert parameter value from a XmlDocument to a String”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s