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!