Querying and submitting to a database is a common scenario using InfoPath. In fact, it is very easy to develop a solution by designing a form from a SQL Server data connection, and then dropping the desired fields from Data Source task pane into the view. Then we can query the database and modify the desired records, or create and fill in a new record to submit to the database.
Following is an example of a form based on Employees table of the Northwind database:
While connecting to a database is quite simple, suppose you want to add a simple insert or update trigger to the database table to identify whether LastName is unique:
CREATE TRIGGER [Surname Check] ON [dbo].[Employees]
FOR INSERT, UPDATE
AS
update employees
set LastName = LastName + ' (not unique)'
where LastName in
(select LastName
from employees
group by LastName
having count(*) > 1) |
But now, if we attempt to add an employee with a non-unique LastName (e.g. “Davolio”) we get the following error message:
Clicking Show Details on this error message reveals that the information on primary key are not sufficient, because there are too many rows affected by the update. While we might be adding just one row, the trigger could update more than one row. InfoPath catches that the total number of rows affected by the modification does not match what it expected, and concludes that the information on the primary key is not enough. Because of this, the solution fails. What we need to do is disable the trigger’s count option, so that the total number of modifications returned from the database is equal to the number of rows submitted from InfoPath. This is easy to do using the nocount command.
CREATE TRIGGER [Surname Check] ON [dbo].[Employees]
FOR INSERT, UPDATE
AS
set nocount on
update employees
set LastName = LastName + ' (not unique)'
where LastName in
(select LastName
from employees
group by LastName
having count(*) > 1)
set nocount off |
With this modification in place, it is now possible to add and update rows without errors. InfoPath correctly matches the total number of rows affected by the database as equal to the number of rows the form submitted.
Special thanks to my associate, Davide Tarfanelli, that spent his time with me to solve this task.