Solved

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

Posted on 2008-10-09
3
1,705 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 28

Expert Comment

by:clayfox
ID: 22684036
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
ID: 22898787
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
ID: 26937205
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

717 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