PowerForms HTML supports direct SQL Queries that can be used to provide lookup data for lookup controls (such ComboBoxes).
For security reasons, the actual sql query is not defined inside PowerForms designer but another setup model has been adopted :
Add a new item inside the list. Set the title of the item.
Inside the QueryXML field, you should use the following xml schema to define the query.
The Elevated="true" parameter forces the execution of the query to be done under the application pool account. This is needed in case you need to access your database using windows authentication. If you are using SQL/native database authentication then this is not needed.
Note that the schema contains 3 main properties :
This contains the OLEDB connectionstring to the database
SQL
Contains the sql command of your query. If a stored procedure is to be called, you should add the attribute Type="SP" to the Query element. For example : <Query Type="SP">
Parameters
You can define unlimited number of parameters in your query. Values for these parameters are provided by the form during query execution.
Parameter types supported : "Text", "Number", "DateTime".
A new section inside the designer has been added to configure SQL Queries.
There you must reference the Query List URL and Query List Name, set the Query Name (which corresponds to the Title column) and setup parameters (that should be already defined in the query xml)
Add a combobox control on your form.
Inside the Looup Details section, you should select that the lookup values come from a SQL query, select the query from the provided list and set the Display and Value fields of the control.
If a single field is required (i.e. Title) use that field for both properties (Display and Value members)/
NOTE : PowerForms provide automatic refresh to the lookup control every time a control value that affects the query parameter, is changed by the users.
We assume we have a table (named Products) containing our product list.
Columns : ID (int), NAME (nvarchar), DESCRIPTION (nvarchar)
We create a stored procedure to retrieve products :
As you may notice, we have provided an optional parameter in order to retrieve a single record if necessary.
Now to define the query xml we would have to provide something like the following :
Notice the Type="SP" attribute of the query xml element. It is important to use this attribute in order to call a stored procedure.
Now we setup the query inside the PowerForms designer and test the results:
Test the optional parameter by setting "ID;1" inside the Parameters propery.
Now we bind the query to a ComboBox control to examine the results :
The combobox in action :
Instead of a ComboBox control, RadioButtons can be used to display SQL Query results :