SQL Database Integration

Re: Main data connection using SQL view and problem with query data through stored proc

amitshrivastava — Thu, 05 Feb 2009 06:30:50 GMT

Hi Patrick,

Thanks for the response, I appreciate your help. I have not changed schema to cause this issue, I am still in development /test mode, for this I followed following steps -

1. Create table (T1) with two columns

2. Create a view (V1) to select recods from T1 (I need to use view because for my final design I need to merge multiple tables etc).

3. Create a simple stored proc to return all records from V1 (I need stored proc to return data to infopath as the data need to be filter on complex logic for my final design).

4. Create new Infopath with Main data connection based on view V1.

5. Customize Query Button to execute stored Proc of step-3 for querying data.

Strange observation is - above setup works fine if I base Main connection of Form on table T1 insted of view V1. Based on error message it seems some how stored proc is returning reference to table T1 insted of view V1 when I execute SPROC through XDocument.Query() as highlighted below -

Element '{http://schemas.microsoft.com/office/infopath/2003/ado/dataFields}Table1 is unexpected according to content model of parent element '{http://schemas.microsoft.com/office/infopath/2003/dataFormSolution}dataFields'.
Expecting: {http://schemas.microsoft.com/office/infopath/2003/ado/dataFields}View1.

Any thoughts ?

Thanks, again, appreciate your help greatly.

Amit

Re: Main data connection using SQL view and problem with query data through stored proc

Patrick Halstead — Thu, 05 Feb 2009 02:14:07 GMT

Hi AmitShirvastava and welcom to the forum,

This is most likely caused by a change in your database schema. Did you change your database columns recently? Open your XSN in Desgner mode. Go to Tools->Convert Main Data source and then republish the form. See if that solves the problem. If not, you've run into InfoPath's big limitation with SQL - when the schema changes your form breaks. That's why people use Web Services - they provide an abstraction layer between the DB and the Form and if this abstraction layer is defined appropriately (using loose coupling) it can allow for schema changes on the server or client without breaking your live form submission process. We provide a Web Service that solves this problem. So, if you can't get it working with Convert Main Data Source, it's likely you changed your DB in a non forward-migratable way and you are hosed. If that's the case, you may want to consider redoing your form to submit to a DB using a Web Service. Hope this helps.

Main data connection using SQL view and problem with query data through stored proc

amitshrivastava — Wed, 04 Feb 2009 23:57:55 GMT

Hello Team,

Hope someone can help me with this.

I have Infopath 2007 form based on a view ( View-1) in SQL2005, work without any issue when I query the data. Now I would like to return the data through stored proc which runs a select statment on View1 with some conditions in where clause. This is where I run into issue and get following error ---

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

The query cannot be run for the following DataObject: Main connection
The XML data returned from the data source does not match the XML Schema for this form. This discrepancy might be caused by the data source returning multiple rows when InfoPath expects only one.
Element '{http://schemas.microsoft.com/office/infopath/2003/ado/dataFields}Table1 is unexpected according to content model of parent element '{http://schemas.microsoft.com/office/infopath/2003/dataFormSolution}dataFields'.
Expecting: {http://schemas.microsoft.com/office/infopath/2003/ado/dataFields}View1.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

seems somehow stored proc is returing reference to table1 while form is expecting View1 as defined in Main data connection. Trying to make this work since 1:00 AM last night still no luck, any help will be apprecaited.

Thanks