SPARKnit

​​​How to add new record to an external database using database procedures?

Last Update: January 18, 2018

Here are the steps on how to add a new record to an external database using database procedures assuming that we are using MS SQL Server and have created a procedure named "P_Add_Country" to add a new country to the related database table.

1. Open the form in design mode.
2. Add the following to the form design workspace:
   
   where "AddCountry" is a SQL Connector control.
3. Select AddCountry control and under Control Properties, click on the Control Settings icon and do the following:
   1. Type the Connection String and click on Connect.
      
   2. Under the Database Content section, do the following:
      
      
      • ​In the Database Object Type, choose Procedures.
      • In the SQL Objects, choose P_Add_Country.
      • In the Procedure Parameters, type #Control(txtCountryCode) for the parameter @Country_Code and #Control(txtCountryName) for the parameter @Country_Name. You can click on fx to help you adding the parameters' values.
      • For the Request Method, choose Post.
      • For the Execute on, choose onLoad.
      • Click OK.
        Note: When click OK, the system will automatically create a new rule to the control named "QueryRule1", remove this rule as it will be added it to the BtnAdd control as appears below.
4. Select BtnAdd control.
5. In the Rules pane, click on the Add icon to add a new rule, the Rule Manager dialog will open.
   
6. Add a new rule as follows:
   A. Change the Rule Name as desired.
   B. Change the Rule Type to Action.
   C. Change the Event Type to onClick.
   D. In the Action section, type the following script:
        executeSQLQuery(AddCountry)
   E. Click on the Save button to save the rule.
   Note: You can use Assistance Panel to help you adding functions and related parameters, form variables and form controls to Conditions and/or Actions sections.
7. Test the form by clicking on the Preview button in the View Group.
   Note: You can apply same steps for SQL procedures which delete or update external database records.

Related Resources:
These resources may not reflect the same exact case steps.
https://www.youtube.com/embed/91gvVO4JYPE