| When setting up the Data Source property of Grid and Record
forms, the norm is to have a database table as the Data Source Type.
However, situations arise whereby it would be more convenient to use a database
stored procedure as the form data source. Configuring a form to use a stored
procedure as the data source is not much different than configuring a table
data source. Obviously, the database you are using must support stored procedures
and the particular stored procedure to be used must already exist in the database.
To configure a grid form to use a stored procedure, click on the […]
button of the Data Source property for the grid. In the Data
Source window, set the Data Source Type field to Procedure.
After doing this, the Procedure dropdown list
will contain a list of the stored procedures available in the current connection.
From this list, select the procedure you want to use.
After selecting the procedure, click on the Get Parameters
button to retrieve any parameters defined for the procedure. In most cases,
the parameters will be retrieved with their proper properties such as Direction
and Data Type. You can double-click on any parameter and change
its properties if the default ones are not correct. In the case of a grid form,
the parameters are used to determine what records will be displayed in the grid
so it is important that the Direction, Data Type,
Type and Source properties be configured correctly.
After configuring the properties, click Ok to close the Data
Source window.
With the Data Source thus setup, you can then add fields to
the form based on the fields returned by the procedure. Bear in mind that using
a stored procedure has some limitations which don’t apply when a table
is used instead. For instance, a grid cannot have a navigator when a stored
procedure is used and neither does sorting of the form fields work. This is
because the stored procedure is used ‘as-is’. On the contrary, when a table is used
as the data source, SQL queries can be created programmatically to facilitate
navigation and sorting.
A stored procedure can also be used to insert, update or delete records using
a record form. You will notice that in the Properties window, a record form
has the following properties:
- Custom Insert Type
- Custom Insert
- Custom Update Type
- Custom Update
- Custom Delete Type
- Custom Delete
If for instance you want to use a stored procedure to insert records, you would
set the Custom Insert Type property to Procedure
then use the Custom Insert property to configure
the procedure in a similar manner as was done using the Data Source
property. Similarly, the other 4 properties can be used to configure stored
procedures to update and delete records respectively.
Note that a record form can still have its Data Source
property configured to use an ordinary table and yet have procedures for the
insert, update and delete operations. This is because the Data Source property is different from the 6 properties mentioned above. Essentially, the Data Source
property determines the means by which data will be retrieved to appear in the
form while the other six properties mentioned above determine the means by which
the data is manipulated depending on the operation (Insert, update, delete).
Finally, stored procedures can also be used to populate ListBox and Radiolist
controls. In this case, the List Source Type
property is set to Procedure then the List
Data Source property is used to configure the stored procedure
and its parameters. |