Change an Imported Stored Procedure
Once you have created a Datasource and Dataset(s) for an Imported Stored Procedure, you may need to change it later if the Stored Procedure is modified. You may also need to change the connection string for the database housing the Stored Procedure, if this changes or you have separate test and live systems. You can change the connection string in the Datasource properties, but we recommend that you change it when you publish your form using the Publishing Profile settings instead. The Publishing Profiles allow you to easily use different SQL databases for the same form, for example to switch between running the form in your test, development and live environments. See the
For information about changing a Digitise Forms or Imported Datasource rather than an Imported Stored Procedure Datasource, see Change a Datasource instead.
Changes can be made in the properties of a Datasource and in the Project Explorer's Project and Datasources tabs:
To display an Imported Datasource's properties, select the Datasource under either the Datasources or Project tab.
Selecting the Datasource in the Datasources tab will also display any Datasets, associated with the selected Datasource, in the Form Design workspace area of the Form Studio window.
The following properties are available:
Name
A name identifying this Datasource.
We recommend that you don't change the name for Imported Stored Procedure Datasources.
Note that right-clicking on this field and choosing Reset to default will clear the field.
Code Name
This is the name of the Datasource's global object as you need to refer to it within any custom JavaScript functions you add to the form. Note that the name spelling here is case-sensitive and must be used in your JavaScript exactly as it appears here.
Connection Category
The options in this section can be used to enter, view and edit the connection string for the Datasource. However, as mentioned above you can also enter and edit the connection string within the Publishing Profile for the form, when you publish the form, and we recommend that this is where you do this.
However, if you want to enter, view or edit the connection details here, click on the browse button, 
, at the end of the Connection String text box to display the SQL Database Connection dialog.
Below the Connection String property, you will see the Catalog and Server Instance properties. The Catalog property displays the database name for the Datasource - 'catalog' is just another name for databases in SQL Server - and the Server Instance property displays the name of the machine hosting the SQL Server you want to use. These properties are for information only and cannot be edited separately from the SQL Database Connection dialog.
Right-clicking on an Imported Stored Procedure Datasource in either the Datasources or Project tabs will display a context-sensitive menu.
The Datasources tab context menu has the following options:
This option is only available for Imported Stored Procedure Datasources.
It allows you to change the Dataset(s) for a Stored Procedure if the Stored Procedure has been modified since it was first imported or last refreshed.
To refresh an existing Imported Stored Procedure Datasource, right-click on the Datasource under the Datasources tab in the Project Explorer and choose Refresh Stored Procedure Import.
Form Studio will query the database using the Datasource's current connection details to check for any changes in the first Result Set and/or in the parameters required since the Stored Procedure was imported or last refreshed. The Import Stored Procedure dialog box will be displayed showing any parameters at the top of the dialog followed by a list of the columns contained in the first Result Set, if there is one.
If there are no changes, the dialog box will look something like this, depending upon whether the Stored Procedure has parameters and/or Result Sets:
You will notice that the dialog box looks similar to when you import a new Stored Procedure. The only difference is that now it has a new option, This Result Set is unchanged from a previous import and is already mapped to a dataset. No Action required. This option should be selected by default, so you can simply click the OK button to dismiss the dialog.
If the Stored Procedure only requires parameters and doesn't return any Result Sets and the parameters have changed, the dialog will look like this:
In this situation, the dialog box will just show the current parameter set.
To update the existing Parameters Dataset with the new parameters, select Correct - this Stored Procedure does not return a Result Set. Create a new Dataset for the parameters. and then click OK. The existing Dataset will be updated with the new values.
If you don't want to update any Datasets, click on Cancel to close the dialog without making any changes.
On the other hand, if the Stored Procedure does actually return one or more Result Sets and you want to update an existing Result Set Dataset, see later in this topic.
If the Stored Procedure requires parameters and returns a Result Set, but only the parameters have changed, the dialog will look like this:
If you want to update the SQL queries for all existing Datasets in the current Datasource to match the new parameter set, select This Result Set is unchanged from a previous import and is already mapped to a dataset. Just update the parameters. and then click OK. Otherwise, click Cancel to close the dialog box without making any changes.
This dialog box will only look at the first Result Set it finds in the Stored Procedure. If the Stored Procedure returns multiple Result Sets and there may have been changes to other Result Sets which you want to update, continue reading for information about how to update these other Result Sets.
If the Stored Procedure returns one or more Result Sets and there have been changes to one or more Result Sets since the last import or refresh, the Import Stored Procedure dialog will be displayed explaining the changes, for example:
In this instance the Stored Procedure has a set of required parameters and returns a Result Set and both the required parameters and the Result Set have changed.
If the Stored Procedure returns a Result Set but has no parameters, the Parameters section will be empty and the Result Sets section will look as it does in the picture above.
If the Stored Procedure both requires parameters and returns a Result Set, but only the Result Set has changed, the Parameters section will only display the parameters without any further explanation and the Result Sets section will look as it does in the picture above.
If the Stored Procedure returns a Result Set and the Result Set has changed since the last import or refresh, Form Studio will compare the Result Set against its records of previously found Result Sets for the Stored Procedure, looking for any Result Sets which contain between one and ten differences from the new Result Set. If it finds any, it will add their names to the drop-down list and display the contents of one of the Result Sets to the right of the current Result Set. You can display the contents of the other Result Set records, by selecting them, one by one, from the drop-down list. This allows you to compare previous Result Sets against the new Result Set and determine whether the new Result Set is an update to an existing Result Set or is a new Result Set which requires a new Dataset.
Note, that if the Result Set list includes a Result Set called ErrorSet, this means that when the Stored Procedure was originally imported or last refreshed, Form Studio was unable to correctly identify any Result Sets and may have displayed an error message before displaying the Import Stored Procedure dialog box. In this situation, we recommend that you create a new Dataset for the Result Set, edit it if necessary, and then delete the original Dataset created, if there is one. You may need to check appropriate data mappings after deleting the dataset and remap any relevant Elements to the new Dataset.
- The Import Stored Procedure dialog compares the current Result Set against previously known Result Sets for this Datasource. It doesn't compare the current Result Set against the existing Datasets, since you may have edited the Datasets after they were created.
If you want the new Result Set to update an existing Dataset, first select the original Result Set from the drop-down list to display its columns on the right-hand side. Then select The new Result Set is a revision of the previously imported result set called <result set name>... and click OK. The Dataset, created from the original Result Set, will be modified to match the new Result Set. If there are any changes to the parameters required by the Stored Procedure, selecting this option will also cause Form Studio to update the SQL queries for all Datasets in the current Datasource to match the new parameters.
If the new Result Set isn't an update to an existing Dataset, select None of the similar Result Sets are a revision of the new Result Set... and click OK. A new Dataset matching the new Result Set will be added to the Datasource. If there are any changes to the parameters required by the Stored Procedure, selecting this option will also cause Form Studio to update the SQL queries for all Datasets in the current Datasource to match the new parameters.
If you aren't interested in the new Result Set, click Cancel to end the refresh without making any changes.
If the Stored Procedure returns more than one Result Set, just as when you import a new Stored Procedure, only the first Result Set found will be notified to Form Studio. Once you have dealt with the first Result Set as described above, you may want to refresh one or more Datasets which corresponds to other Result Sets. In this case, if you are familiar with SQL and able to edit the Stored Procedure, you can comment out the SELECT statements in the same way as for importing a new Stored Procedure and then choose the Refresh Stored Procedure Import option again so that a different Result Set is displayed in the Import Stored Procedure dialog. You can then handle this Result Set in the same way as the first Result Set. You can repeat this process until you have refreshed all the relevant Datasets. Remember to restore your Stored Procedure to its original state once you have refreshed all the Datasets.
If you are not able, or don't want, to edit the Stored Procedure, you can manually edit the existing Datasets to match the changes in your Stored Procedure.
Note that just as with importing a Stored Procedure for the first time, when refreshing a Datasource, Form Studio may not always be able to correctly or fully interpret the Result Sets returned by a Stored Procedure. If the Result Set displayed in the Import Stored Procedure dialog box is not quite what you expect, you can either use it as the basis for updating a Dataset and then edit the Dataset so that it matches the columns from the Result Set that you want to use or, alternatively, you can edit the existing Dataset manually to update it to match the changes to the corresponding Result Set.
In some cases, Form Studio may be unable to correctly identify any result sets and might be unable to decide if there actually are any. In this situation you may get an error message displayed when Form Studio attempts to import the Stored Procedure. If an error message appears, click Continue to continue with the import or Close to cancel the import.
If you click Continue, or no error message displays, and there are parameters required by the Stored Procedure, the Import Stored Procedure dialog box will appear, looking something like this:
The Parameters section at the top of the dialog will just show the current parameter set.
If the Stored Procedure doesn't return any Result Sets and you want to update the existing Parameters Dataset with the new parameters, select Correct - this Stored Procedure does not return a Result Set. Create a new Dataset for the parameters. and then click OK. The existing Dataset will be updated with the new values and the Dataset's SQL Query will be updated.
If you don't want to update any Datasets, click on Cancel to close the dialog without making any changes.
On the other hand, if the Stored Procedure does actually return one or more Result Sets and you want to update an existing Result Set Dataset, select No - this Stored Procedure does return a Result Set. Show me the options. instead. The Import Stored Procedure dialog will change to display the following options:
If the Stored Procedure doesn't require any parameters, after you click Continue in any error message which is displayed, or if no error message is displayed, the Import Stored Procedure dialog will be displayed immediately with the same options. In this case, however, the Parameters section at the top of the dialog will be empty.
The Import Stored Procedure dialog box includes guidance notes to help you choose the best option for your needs.
Create a dataset for me to complete from table:
This option is intended to be used where you want to update a Dataset whose corresponding Result Set is similar to, or based on, the columns in an existing database table. This option allows you to create a Dataset matching that table and then amend it manually to match the actual Result Set.
To do this, select this option and then choose the table you want to base your Dataset on from the drop-down list beside the option. Click OK. Form Studio will create a new Dataset based on the selected table and add it to the current Datasource. The Dataset will be named after the database table. If there is already a Dataset defined with this name, even if it is in another Datasource, a consecutive sequence of numbers will be appended to the end of the second and subsequent Dataset names, e.g. MyDataset2. An appropriate SQL query will be generated for the new Dataset.
This option creates a new Dataset and don't update an existing Dataset. If you are refreshing an existing Dataset, after the new Dataset is created, you can edit the new Dataset so that it matches the Result Set that will actually be returned, using the original Dataset as a guide, if appropriate. Once your new Dataset is complete, you can delete the original Dataset, if you no longer need it. You will then need to check your form for data mappings to the original Dataset and remap them to the new Dataset.
Create an empty dataset for me to complete.
This option is intended to be used where you want to update an existing Dataset but the new Result Set doesn't really match any specific database table's columns sufficiently to warrant using an existing database table as the basis for the Dataset for this Result Set. This option allows you to create an empty Dataset and then amend it manually to match the actual Result Set.
To do this, select this option and click OK. Form Studio will create a new empty Dataset, named after the Stored Procedure, and add it to the current Datasource. If there is already a Dataset defined with this name, even if it is in another Datasource, a consecutive sequence of numbers will be appended to the end of the second and subsequent Dataset names, e.g. MyDataset2. An appropriate SQL query will be generated for the new Dataset.
This option creates a new Dataset and don't update an existing Dataset. If you are refreshing an existing Dataset, after the new Dataset is created, you can edit the new Dataset so that it matches the Result Set that will actually be returned, using the original Dataset as a guide, if appropriate. Once your new Dataset is complete, you can delete the original Dataset, if you no longer need it. You will then need to check your form for data mappings to the original Dataset and remap them to the new Dataset.
Once you have updated a Dataset, or created a new Dataset, for a Result Set, you can subsequently edit the Dataset if necessary to add missing columns or remove unwanted columns so that it matches the Result Set that will actually be returned. You can remove any columns that you don't want or need to download for your form. Likewise, if you have created a new empty Dataset or the Result Set has columns which don't appear in the Dataset, you only need to add a data field to the Dataset for those columns in the Result Set that you want to use in your form. You don't have to allow for all columns in the Result Set within your Dataset, if you don't want or need them. Note, however, that you must not add any data fields to a Dataset for which there are no corresponding columns in the Result Set. Likewise, if the Dataset contains columns which are not in the actual Result Set, you will need to delete these columns from the Dataset.
To add a column to the Dataset, click on the Add Column button; to remove a column, deselect the Mapped checkbox for that column in the Dataset grid under the Mappings tab.
Remember that the names and data types of the data fields in the Dataset must match those of the corresponding columns in the Result Set.
If the Result Set column names are different to those found in the actual Database table, e.g. where the SELECT statement reads something like SELECT [Reporter_Surname]AS Surname, ..., use the Result Set column names when adding data fields to a Dataset. If Form Studio creates a Dataset for you in this situation, the FX Name column in the Dataset will contain the Result Set column name and the SQL Name column will contain the SQL database table column name. The Code Name column shows the column name converted to be compatible for use when referencing the column within custom JavaScript. This name will be generated automatically by Form Studio, can't be edited, is case-sensitive and shows the column name exactly as it must be used in custom JavaScript. Note that when adding a new column to a Dataset, the Code Name will only be displayed after you close the Dataset and re-open it.
If you are editing or adding Dataset data fields based on the columns in a SQL database table, you will notice that the data types available in Form Studio don't exactly match the data types used in SQL Server. This is because Digitise Forms uses C#/.NET data types rather than SQL Server data types. If you're not sure what data type you need to select, you can find conversion tables online, such as the following Microsoft web page:
https://docs.microsoft.com/en-us/sql/relational-databases/
clr-integration-database-objects-types-net-framework/
mapping-clr-parameter-data?view=sql-server-ver15&redirectedfrom=MSDN
&viewFallbackFrom=sql-server-2014
For text string fields, the max length property of the field should also match between the Result Set and the Dataset field. Note, however, that because of the way the length values are read, when displayed in the Result Sets section of the Import Stored Procedure dialog, the Length property for columns which have a data type of nvarchar or nchar is twice the actual Max Length value required in an FX Dataset.
If you are not sure, what columns the Result Set will return, try running the Stored Procedure in SSMS if you can, otherwise you will need to speak to your SQL administration team. Note that if you want to remove a data field designated as a Primary Key field, you will need to deselect the Primary Key checkbox for that data field before you can deselect its Mapped checkbox.
Parameter Datasets should not be edited - you must include a data item in the Dataset for each of the parameters required by the Stored Procedure.
Deletes the current Datasource and all its Datasets from your project.
The Projects tab context menu has the following options relating to changing an Imported Stored Procedure Datasource:
Delete
Delete the current Datasource and its Dataset(s).
Rename
Rename the current Datasource. We do not recommend that you change the name of Imported Stored Procedure Datasources.
The other options on this menu are not relevant to Imported Stored Procedure Datasources.
When you publish a project which has an Imported Stored Procedure Datasource, the publish process will check whether the Stored Procedure has changed since the last refresh or import, and, if it has, warning messages will be displayed describing the changes. In this situation you should cancel the publish and update the relevant Datasets before publishing again, otherwise your form may not work correctly.
See also: