show / hide menu

MS SQL

You can connect an AppForm to any number of datasources. One of the datasource types supported is MS SQL. You can have the datasource configured with a service account or windows authentication. This datasource will allow you to work with the Tables and Views inside the MS SQL database.

SQL

  1. Create/Add a data source: Click on Data source and then on ‘Create Data source’ to Create/Add a data source to a form.

On clicking ‘Create DataSource’ a pop up comes up to select the type of data source being used.

On selecting MS SQL, a list of all the available data bases in the site is displayed. On selecting a data base and clicking Next button, all tables available in the corresponding data base will be displayed from which a table can be selected.

On clicking Next, page with data source commands ‘Insert’, ‘Update’, ‘Delete’ will be displayed. Select a command from the drop down and click the Add button to configure the command for the form.

Insert

On selecting Insert command and clicking Add, a pop up is brought up to map controls in the form with the table columns to be inserted into table.

Update
On selecting Update command and clicking Add, a pop up with two sections will be displayed.
Query Value Mapping: The first area with table columns (DataSource columns)mapped with the controls(Mapping value) in the form to which the update is to be applied. Select Static and a static value for the column can be provided in the Mapping Value field.
Query Criteria Mapping: Click on Add button to add Criteria. The criteria section has to be specified with the condition or value in a control in the form based on which Update has to occur in the table.
The first drop down will contain all the column names of the table into which Update is to be applied, second drop down will contain a list of operators that are applicable for criteria satisfaction and the third auto complete field when typed in will display the control name from which the table value is to be obtained.

NB: All the Mapping value fields are drop downs which when text is typed in, the control names in the form are shown.

Select
On selecting Select command and clicking Add, a pop up same as the update will be displayed.
Query Value Mapping: The first area with list columns (DataSource columns) mapped with the controls(Mapping value) in the form to which the select is to be applied
Query Criteria Mapping: The criteria section has to be specified with the condition or value in a control in the form based on which Update has to occur in the list. Click on Add button to add Criteria.
The first drop down will contain all the column names of the list into which Select is to be applied, second drop down will contain a list of operators that are applicable for criteria satisfaction and the third auto complete field when typed in will display the control name from which the list value is to be obtained.

The Criteria tab displays the query generated for the command configured.

Click on Ok and the command will be seen as added under the Commands Configured section.

Edit Command
The commands configured initially can be edited by clicking 

Delete Command
The commands configured initially can be deleted from the configured command section by clicking 

Manage Execute Rule
On clicking Manage Execute Rule, a pop up with the list of all the controls and default triggers is shown. On selecting a control/ default trigger and clicking the button Create Rule a rule is created with selected trigger(s) for the selected command. 

On clicking Finish and clicking Rules Tab, all the auto generated execute command rules can be viewed.

View DataSource
The data source added can be viewed from clicking the DataSources side menu.

Edit
On clicking icon on the share point datasource created, the datasource properties window will pop up on the right of the screen.

Delete
On clicking icon on the share point datasource created, the datasource can be deleted