Re: Help Submiting Multiple Rows from Infopath to SQl Server 2008 via Sql-endpoint webservice using table-valued parameters

Patrick Halstead — Mon, 25 Oct 2010 07:31:49 GMT

Hi Daniloraf,

Qdabra Software funds this free forum. We guarantee that we will respond to 90% of all posts. It takes a tremendous amount of time to do that, but we are committed to helping people. To help defray some of the cost of running the forum and responding to users, Qdabra sells a couple very inexpensive tools - like qRules and DBXL. DBXL allows you to connect your InfoPath forms to a database. No code required and you get a data-driven web service that can be used for all of your form templates.

Direct SQL data connections are problematic because schemas change and they don't work for browser based forms. SQL endpoints are problematic due to bugs in Microsoft's implementation. Sorry I don't have more details.

Our stance is to help people. After helping many people solve problems, we develop solutions and suggest people use those because they will save time and money. The solutions aren't offered to make us a lot of money. They just help us defray the cost of the forum and they help you because they are fast, flexible, and follow best practices.

Help Submiting Multiple Rows from Infopath to SQl Server 2008 via Sql-endpoint webservice using table-valued parameters

daniloraf — Sat, 16 Oct 2010 02:56:17 GMT

Help Submiting Multiple Rows from Infopath to SQl Server 2008 via Sql-endpoint webservice using table-valued parameters

Background
I´m trying to submit an infopath(2007) form (an invoice) to sql server 2008. Form will be hosted on Infopath Forms Services (moss2007). There are many submit actions upon clicking the submit control (the button on the form), and one of them must submit a repeating table to a sqlserver endpoint webservice that uses table-valued parameters to insert the rows to a database table.

Problem
My current solution works, but only for the first row of the repeating table, and, of course, all rows must be submited.
-------------
The form:
The Main Data Source was created by me and has many other fields other than the repeating table. The repeating table itself has 5 columns. The Submit button on the form triggers several submit actions. One of these actions must submit the repeating table to a soap webservice (that is a sqlserver native endpoint, for those not familiar with it, it is native functionality that calls a stored procedure as a webmethod.. at least that is what i am doing)
--------------
The webservice (native sql server 2008 endpoint)
It is a standard template i found on the internet. it calls the

CREATE ENDPOINT insertdetails
STATE = STARTED
AS HTTP ( PATH ='/SQL/insertdetails', AUTHENTICATION=(integrated), PORTS=(clear) )
FOR SOAP ( WEBMETHOD 'insertsproc' ( NAME='databasename.dbo.insertsproc', SCHEMA=standard, FORMAT=rowsets_only), WSDL=default, BATCHES=enabled, LOGIN_TYPE=windows )

-------------------------
SQL Stored Procedures and Table-Valued Parameters (There are two sprocs)
insertsproc: exposed to webservice, to get data from the infopath form, and send it to a sql server table-valued type.

USE [databasename]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[insertsproc]
@idor int,
@idprodent int,
@qtde int,
@compr decimal (18,2),
@tipoesttransac varchar (50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @table AS tabletype3
INSERT INTO @table (idor, idprodent, qtde, compr, tipoesttransac)
VALUES (@idor, @idprodent, @qtde, @compr, @tipoesttransac)
EXECUTE submit2
@details = @table
SELECT * FROM @table
END
GO

Note that this sproc executes the next one (even tough they are created in reverse order than the one shown here)

USE [databasename]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[submit2]
(@details dbo.tabletype3 READONLY)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.test2 (idor, idprodent, qtde, compr, tipoesttransac)
SELECT idor, idprodent, qtde, compr, tipoesttransac FROM @details
END

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

and this is the table type creation query:

CREATE TYPE tabletype3 AS TABLE
(
[idor] [int] NULL,
[idprodent] [int] NULL,
[qtde] [int] NULL,
[compr] [decimal] (18,2) NULL,
[tipoesttransac] [varchar] (50) NULL
)
GO

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

It works, but only for the first row of the repeating table.
Form -> form connection -> endpoint webservice -> sproc-1 -> sproc-2 -> table
Where is the error? And how can I fix it?

--------------------------
Limitations:
1) i do not wish to use the parse xml approach, unless absolutely extremely necessary.
2) i do not wish to use those sql xml datatypes, since i do not know how they work, and would like to stick to the current approach.
3)I do not use visual studio, since i am not a programmer.
4)I do not wish to use an IIS webservice, since i do not know how to program it properly (at least without study).

Thank you!

SQL Database Integration

Re: Help Submiting Multiple Rows from Infopath to SQl Server 2008 via Sql-endpoint webservice using table-valued parameters

Help Submiting Multiple Rows from Infopath to SQl Server 2008 via Sql-endpoint webservice using table-valued parameters