Advanced Database Connectivity with NiceForm

NiceForm, a module found within NiceLabel Suite, is a Rapid Application Design tool that allows users to create applications that facilitate label printing. Using NiceForm, the final application design can be very complex or very basic depending on the final requirements of the customer. One of the common questions when developing a NiceForm application is about database connectivity; specifically, what options are there to connect to and/or present data within the application.

General Database Connectivity

General database connectivity is defined as a way to make a database connection for the entire form to use. A general database connection can be used to show a database table, create radio buttons or dropdown boxes linked to a database column, or perform a filtered search for data; essentially, any object that requires a database resource would use this kind of database connection.

Creating a general database connection can be achieved by clicking on the Data menu option and selecting Database Access. The Database Access window allows for the creation of database connections and has the option to use a wizard (primarily for "direct" database connections to MS Access or Excel) and ODBC connections, as well as the ability to edit existing database connections. For more advanced OLE DB connections, select the New button to have a more advanced view of the setup your database connection (Image 1).

Image 1- View of Advanced DB Connection Interface

Action Based Database Connections

When creating a form, there may be situations where the application design does not justify a full and complete database connection, some examples being:

• A single query to lookup details for a single part number.
• A complex query containing multiple JOIN statements.
• A conditional lookup where the query cannot be executed until a certain amount of steps have been completed.

In all cases, the database lookup can be linked to an action through the Execute SQL Statement action.

Image 2- The view of the Execute SQL Statement action within the Actions Editor pane.

Actions within NiceForm can be executed through the click of a button, the selection of a field or focus on a field, the loading or closing of a form, etc. Since actions within NiceForm can be utilized in a number of ways, if a database lookup action is required, the designer/developer has control over when and where the database connection and query are executed. Within the details of the Execute SQL Statement action, a custom query can be executed using standard database query statements with the ability to use all variables within the form.

In the example below, the Execute SQL Statement action is connecting to an MS Access database and selecting all records from a table where the PARTNO (db column) equals the form variable "IncomingPartNum" (placing a colon before the variable name signifies that it is a NiceForm variable).

Image 3- The details of the Execute SQL Statement Action

Conclusion

In conclusion, using a database connection within a NiceForm application allows users to create very dynamic, database driver applications. Furthermore, even though the Execute SQL Statement action is a very small component in comparison to the vast array of features within NiceForm, it serves as another way to create more advanced, database driven, applications.

For more information about NiceForm, please visit our NiceLabel product page www.nicelabel.com as well as the NiceForm production page www.nicelabel.com/Products/NiceLabel-Standard-Series/NiceLabel-Suite/NiceForm for more information.