Help needed in saving infopath form to xml column

[christine.freshour]

I am trying to save an InfoPath 2007 form to a SQL Server 2005 table
that has an XML column by following the steps in this article:

http://aspalliance.com/1106_Saving_InfoPath_Forms_to_SQL_Server_2005_as_XML

But, when I submit the form I get a “Column name or number of supplied
values does not match table definition error”. The table IPForms only
has 2 columns.

Id int
Form xml(DOCUMENT dbo.IPFormSchemaCollections)

This is the XML schema for the InfoPath form modified as suggested in
the article:

create XML SCHEMA COLLECTION IPFormSchemaCollections AS
'<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<xsd:schema targetNamespace="http://schemas.microsoft.com/office/
infopath/2003/myXSD/2008-08-21T13:28:43" xmlns:my="http://
schemas.microsoft.com/office/infopath/2003/myXSD/2008-08-21T13:28:43"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="myFields">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="my:field1" minOccurs="0"/>
<xsd:element ref="my:field2" minOccurs="0"/>
<xsd:element ref="my:field3" minOccurs="0"/>
<xsd:element ref="my:field4" minOccurs="0"/>
<xsd:element ref="my:field5" minOccurs="0"/>
</xsd:sequence>
<xsd:anyAttribute processContents="strict" namespace="http://
www.w3.org/XML/1998/namespace"/>
</xsd:complexType>
</xsd:element>
<xsd:element name="field1">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="10" />
<xsdattern value="\d\d\d\d-\d\d-\d\d" />
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="field2" type="xsd:string"/>
<xsd:element name="field3">
<xsd:complexType mixed="true">
<xsd:sequence>
<xsd:any minOccurs="0" maxOccurs="unbounded" namespace="http://
www.w3.org/1999/xhtml" processContents="skip"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="field4" nillable="true" type="xsd:base64Binary"/>
<xsd:element name="field5" nillable="true" type="xsd:base64Binary"/>
</xsd:schema>'

This is the code from the Page_load event:

protected void Page_Load(object sender, EventArgs e)
{
System.Xml.XPath.XPathDocument xDoc =
new System.Xml.XPath.XPathDocument(Request.InputStream);
System.Xml.XPath.XPathNavigator navigator = xDoc.CreateNavigator();

using (System.Data.SqlClient.SqlConnection conn =
new System.Data.SqlClient.SqlConnection(
"Data Source=MyServerHere;Initial
Catalog=MyDBNameHere;Integrated Security=True"))
{
conn.Open();

if (!String.IsNullOrEmpty(navigator.InnerXml))
{
System.Data.SqlClient.SqlCommand cmd =
new System.Data.SqlClient.SqlCommand(
@"INSERT INTO dbo.IPFORMS VALUES (@Form)", conn);
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter(
"@Form", navigator.InnerXml));
cmd.ExecuteNonQuery();
}

conn.Close();
}
}

To configure the InfoPath form to submit the data, I did the
following:

Tools\Submit Option
Checked Allow Users to submit this form
Selected Web Server (HTTP)
Entered the HTTP post URL to the ASP.NET page

Does anyone see anything wrong or missing? Any help will be deeply
appreciative.

 

[S.Y.M. Wong-A-Ton]

The only thing I can think of by looking at that error message is that you
did not make the "id" field and "identity" field, so numbers for the "id"
field are not automatically generated, so you would need to specify the "id"
field and its values explicitly in your INSERT statement or make the "id"
field and "identity" field (take a look once again at the CREATE TABLE
statement in the article).