InfoPath Dev

Hi,

I created a web service in SQL Server 2008 by using an EndPoint with my stored procedure.  That worked great with my InfoPath 2010 form.  However, the use of endpoints for web services is discontinued in SQL Server 2012, which I will have to use in production.

So, I created an asmx web service in Visual Studio 2010, and used the same query as I did with my stored procedure, except it is in C#:

string cmdString = @"SELECT ""ITL-ITEM"", ""ITE-DESCRIPTION"", ""ITE-DESCRIPTION2"", ""ITE-STOCK-UOM"", ""ITE-USER-FIELD2"", ""ITE-USER-FIELD3"", ""ITL-LAST-REC-COST"" from Catalog WHERE ""ITE-DESCRIPTION"" LIKE  '" + '%' + searchWord + '%' + "' ";

This returned the results I expected in Visual Studio and then after I deployed it to the server, it still gave me the correct results when I invoked the method on the web service.  However, when I made that new C# web service my data source in my InfoPath form, my data fields returned only allowed for one result.  These were non-repeating fields.  My datafields looked like this:

 queryFields
dataFields
   tns.GetProductsResponse
     GetProductsResult
       :NewDataSet
         (Choice)
            Catalog
               ITL-ITEM
               ITL-Description
               ITL-Description2...

With that arrangement, I received an error when trying to place the datafields, like ITL-ITEM, on my InfoPath form.  They were labeled as Choice field types, and the error message said that the field could not store the data correctly.  I made the Choice a repeating Choice field, but that didn't help either.

When I went back and looked at my SQL Server web service I used initially with InfoPath, I had data fields like this:

(Choice)
sqlresultstream:SQLRowSet
  ns1:SqlRowSet1
    row
         ITL-ITEM
         ITL-Description
         ITL-Description2

  Note that I had a row and the fields were repeating. 

Do you know where I am going wrong?  I seem to be missing something that is telling InfoPath that I want the entire dataset result, with many rows returned.

Thanks,

William H

 I found the answer in a very lucky Google search where Clayton Cobb explained the answer.  For others, here is what he said:

 

1

Instead of creating your own repeating table, just go to your secondary data source in the data source pain, drill down to the repeating group that has the data set nodes of the web service, and drag that group to the canvas.  It will prompt you to make a repeating table and will place all those fields as columns in a repeating table.  When you query the web service, the repeating table will fill up with all records from the data set returned by the web service.

---

SharePoint Architect - Planet Technologies - 4-Year Microsoft Federal Partner of the Year (2005-2008)

 http://social.msdn.microsoft.com/forums/en/sharepointcustomizationlegacy/thread/48c3a7fa-1119-4d0f-ba1e-947490786ffa

Thanks again for providing this valuable resource!

William H.

Very glad to hear that you found a solution.

A bit of advice: if you're going to go as far as writing your own code to query your database, please use SqlParameters and save yourself the horror of having your database wiped out with SQL injection.

And if you find that you need to query your database with web services a lot, please consider checking out the QueryDB web service included in our DBXL web service suite, which can save you loads of time you'd otherwise need to spend writing, maintaining, and debugging your own special-purpose code.