On a per form basis, you can write SQL transactions that fire run on submission. Use this functionality create unique ID's that can be bound to the transaction (Requsition ID, Expense Report ID, etc.), or to save data from the submission to a SQL Data source. Create a web form that captures any data of interest. In this example, inputs for First Name and Last Name on an Employee Resource are laid out in the form designer.
With the form created, click the Form Actions tab and choose SQL Query from the Select an Action drop down. Click the Add Action button to configure the Action.
Give the Action a descriptive Title. If you have multiple Actions, it is helpful to be able to easily identify them for maintenance and support. In the SQL Server Type drop down, choose Microsoft SQL Server. In the Query box, you can type a SQL statement to execute. In this example, First Name and Last Name are inserted into an Employees table. The table includes an auto-incrementing Identity column that will be used as an employee ID. The script to create that table is below for reference:
| Code Block | ||||
|---|---|---|---|---|
| ||||
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employees](
[ID] [int] IDENTITY(10001,1) NOT NULL,
[FirstName] [varchar](150) NULL,
[LastName] [varchar](150) NULL,
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
|