Form Actions‎ > ‎

Data

Run SQL Query

This action executes an SQL statement, optionally capturing the output. The SQL runs in the context of the DNN database, but there are plans to extend it to also allow a connection string or a connection string name that will make it possible to run in other databases as well – not restricted to SQL Server either. This action supports context tokens and My Tokens inside the SQL query.
  
Here are some common scenarios when you would use this action:
  • Use an UPDATE statement to calculate some statistics for large databases on an interval, instead of calculating them on every call. 
  • Execute an SELECT statement to retrieve data to be used with other actions down the stack. 
  • Flush old temporary data using a DELETE statement. 
  • Execute a stored procedure that calculates commissions paid through a referral program. 
Currently, only one field can be captured from an SQL action, so make sure that your query returns the data you need on first column of the first row. We may extend this in the future to be able to store multiple columns. As a workaround, either create multiple SQL actions, or produce the final text output directly from the SQL query. For example, if you need the full name of a user, you can use something like:

SELECT FirstName + ‘ ‘ + LastName from Users where UserId = [UserId].

Update a table with changed form data

If you want to update a table with changed form data, what you have to do is create an SQL action on the button with insert statement. Then, you can reference fields using token syntax. For example:

INSERT INTO MyTable(FirstName, LastName) VALUES('[FirstNameFieldId]',
'[LastNameFieldId]').

If you need the ID, also do a select scope_identity() and store the output in a token for later use.

Here's basic example on how to insert values in the database using Action Form:

1. create a form with let's say three fields: Product, Color and Size (these fields can be either drop downs or text boxes or whatever fields you need) - notice that the form fields should exist as columns in the table you want them to be inserted;
2. add a button with Run SQL Query action on which add the insert statement as follows:

INSERT INTO Products
           (Product
           ,Color
           ,Size)
     VALUES
           ('[Product]'
           ,'[Color]'
           ,'[Size]')


where Products is the name of the table from the database, the values are placed between square brackets because here I reference the form fields using token syntax, value [Product] means that whatever value I set in the form field Product, it will be inserted in the column Product.

Server Request

This action has the purpose of posting the form data to a different server, you can use the URL field to determine the location for where the data will be posted, in this box you can also use tokens, and there's another useful box, the Post Data box, where you can put key = value pairs on separate lines and you can also add additional HTTP headers, in case you need it, and you can specify the HTTP method by selecting it from the "Choose an HTTP Method" drop down list.

The action can be used to make an HTTP request to a different server, optionally sending data. Often, this means invoking a web service. Note that if you don’t run in Full Trust, this action requires that the Application Pool identity has Web Permission. The following fields can be configured:

  • URL. This represents the URL to make the request to. A common mistake is to forget to include the protocol. For example www.domain.com/webservice is wrong. Instead, use http://www.domain.com/websservice. Optionally, append the query string directly to the URL after the question mark. For example http://www.domain.com/websservice?q=test&p=1. This field supports context tokens and My Tokens.
  • POST Data. This is data to send to the URL using POST operation. Put key=value pairs, each on a separate line. It’s also possible to post whole messages, for example and XML (that SOAP-like services expect) by simply putting the XML without any lines. This field supports context tokens and My Tokens.

A basic example of using the Server Request action on a form button would be to convert Celsius degrees to Fahrenheit degrees. For this we'll need to call a web service with which we'll make the conversion and display the result on a Display Message action, here are the steps:

1. add a form on your page > access Manage form option;
2. add a text box and label it Celsius
3. add a button and on the button add Server Request action
4. in URL field paste the following: http://www.w3schools.com/webservices/tempconvert.asmx/CelsiusToFahrenheit;
5. in Post Data box set Celsius=[Celsius] - meaning that the form field Celsius will be used as token to take any value inserted in the form;
6. on Choose an HTTP Method drop down select the POST option;
7. store the answer in a token bu using the Output Token Name section - set for example "test";
8. add a Display Message action and on Message field put the stored token [test];
9. after saving the form and getting back on the page, insert in Celsius box a value, and click on Submit.

As expected result, the value 10 should be converted and the result should be displayed in the message action.


Repost Data 


This action re-posts the form to a different URL, ending execution of any actions that follow. This action can be used to make an HTTP request to a different server, optionally sending data. Often, this means invoking a web service. The following fields can be configured:

  • URL. This represents the URL to make the request to. A common mistake is to forget to include the protocol. For example www.domain.com/webservice is wrong. Instead, use http://www.domain.com/websservice. Optionally, append the query string directly to the URL after the question mark. For examplehttp://www.domain.com/websservice?q=test&p=1. This field supports context tokens and My Tokens.
  • POST Data. This is data to send to the URL using POST operation. Put key=value pairs, each on a separate line. It’s also possible to post whole messages, for example and XML (that SOAP-like services expect) by simply putting the XML without any lines. This field supports context tokens and My Tokens.


Clear Cache (by Key)