Solved

Submitting an InfoPath 2007 repeating table to a sql 2005 web service / end point

Posted on 2008-10-09
3
1,699 Views
Last Modified: 2011-04-14
I'm working on a poc using MOSS 2007, InfoPath 2007 and SQL 2005.   The need is an InfoPath web form retrieving and submitting data using SQL 2005 web service.  Web service in SQL is first choice in our environment to avoid "double hop" issue and keep development contained to exisitng tools or layers as possible.  Everything works fine except that when the InfoPath form is submitted only the first row of the repeating table is updated.  From the research I've done it seems that an answer has something to do with datasets or ADO.datasets but I don't think that will work with SQL 2005 only.  Thanks in advance!
CREATE ENDPOINT [OcpEp] 

	AUTHORIZATION [domain\username]

	STATE=STARTED

	AS HTTP (

		PATH=N'/SqlWs', 

		PORTS = (CLEAR), 

		AUTHENTICATION = (NTLM), 

		SITE=N'*', 

		CLEAR_PORT = 80, 

		COMPRESSION=DISABLED

	)

	FOR SOAP (

				WEBMETHOD 'OrgDescriptionsUpdate'( 

					NAME=N'[BI_OCP_D].[dbo].[prOrgDescriptionsUpdate]',

					SCHEMA=STANDARD, 

					format = ROWSETS_ONLY

				), 

				WEBMETHOD 'OrgDescriptionsSelect'( 

					NAME=N'[BI_OCP_D].[dbo].[prOrgDescriptionsSelect]',

					SCHEMA=STANDARD, 

					format = ROWSETS_ONLY

				), 

				BATCHES=DISABLED, 

				WSDL=DEFAULT, 

				SESSIONS=ENABLED, 

				SESSION_TIMEOUT=60, 

				DATABASE=N'BI_OCP_D', 

				NAMESPACE=N'http://servername/SqlWs/BI_OCP_D', 

				SCHEMA=STANDARD, 

				CHARACTER_SET=XML

	)

 

 

 

CREATE PROCEDURE [dbo].[prOrgDescriptionsUpdate] 

	@OrgDescriptionId int, 

	@OrgID varchar(10), 

	@OrgDescription varchar(100), 

	@UpdateBy varchar(10) = 'domain\username'

AS

BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from

	-- interfering with SELECT statements.

	SET NOCOUNT ON;

 

	IF @OrgDescriptionId > 0

		UPDATE ORG_DESCRIPTIONS SET 

				ORG_ID = @OrgID, 

				ORG_DESCRIPTION = @OrgDescription, 

				UPDATE_DATETIME = getdate(), 

				UPDATE_BY = @UpdateBy

		WHERE 

				ORG_DESCRIPTIONS_ID = @OrgDescriptionId

	ELSE

	INSERT INTO ORG_DESCRIPTIONS (ORG_ID, ORG_DESCRIPTION, CREATED_DATETIME, CREATED_BY) 

				VALUES (@OrgID, @OrgDescription, getdate(), @UpdateBy)

END

 

 

 

CREATE PROCEDURE [dbo].[prOrgDescriptionsSelect]

AS

SELECT   ORG_DESCRIPTIONS_ID, ORG_ID, ORG_DESCRIPTION, CREATED_DATETIME, CREATED_BY

FROM     ORG_DESCRIPTIONS

Open in new window

0
Comment
Question by:KPEEEOBI
3 Comments
 
LVL 28

Expert Comment

by:clayfox
Comment Utility
You might want to check out qdabra's web service suite. - Database Accelerator.

It can do everything you need with one install or you can use it as a sample.

It has the webservice to submit and query data with multiple tables via web services.
0
 

Accepted Solution

by:
KPEEEOBI earned 0 total points
Comment Utility
The answer was to Use XML from Infopath to Web Service to SQL. In SQL Procedure OUTPUT using FORXML and when updating use XML as input parameter.
0
 

Expert Comment

by:ADSBIT
Comment Utility
Could someone clarify, or elaborate on, the soltuion please? I am having a simliar issue and am interested in  this solution, however I'm not sure how to go about it.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Microsoft SharePoint Foundation 2010 and Microsoft SharePoint Server 2010 do not offer the option to configure the location of the SharePoint diagnostic trace log files during installation.  This can, however, be configured through Central Administr…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now