Docstoc

SQLGridSelectedView V4.1 For DotNetNuke 3+

Document Sample
SQLGridSelectedView V4.1 For DotNetNuke 3+ Powered By Docstoc
					                    SQLGridSelectedView V4.1
                              For
                        DotNetNuke 3+


                                               By
                                          Paul Scarlett

                                      TressleWorks.ca




                                  Document Version 4.1
                                    September 2007


                                          Table of Contents



PDF created with pdfFactory trial version www.pdffactory.com
         Introduction......................................................................................................................................... 1
         Options Reference ................................................................................................................................ 8
            Command Links................................................................................................................................. 8
            SQL Statement.................................................................................................................................. 8
            Connection String .............................................................................................................................. 9
               Sample Connection Strings .............................................................................................................. 9
            Processing Options .......................................................................................................................... 10
            Top Count ...................................................................................................................................... 14
            Text Search Selections ..................................................................................................................... 15
            Date Range Selections ..................................................................................................................... 16
            Date List Selections ......................................................................................................................... 18
            Filter Selections............................................................................................................................... 19
            Conditions ...................................................................................................................................... 22
            Template Based Display ................................................................................................................... 23
            Sorting ........................................................................................................................................... 27
               Sorting and Stored Procedures....................................................................................................... 27
            CSS Classes .................................................................................................................................... 28
            Column Formatting and Summary ..................................................................................................... 30
            Export............................................................................................................................................ 31
            Edit Records ................................................................................................................................... 32
            Variable Substitution........................................................................................................................ 37
            Inline Functions............................................................................................................................... 38
               HTMLDECODE.............................................................................................................................. 39
               MAKELINK ................................................................................................................................... 40
               MAILTO....................................................................................................................................... 41
               ENCRYPT..................................................................................................................................... 43
         Error Handling.................................................................................................................................... 44
         Examples........................................................................................................................................... 45
            Setup ............................................................................................................................................. 45
            NorthWind Example 1 ...................................................................................................................... 46
            NorthWind Example 2 ...................................................................................................................... 47
            NorthWind Example 3 ...................................................................................................................... 48
            NorthWind Example 4 ...................................................................................................................... 49
            NorthWind Example 5 ...................................................................................................................... 50
         Revision History:................................................................................................................................. 51
         Acknowledgements:............................................................................................................................ 51


         TressleWorks.ca                                                            ii                                           1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         TressleWorks.ca                             iii       1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                         TressleWorks.ca



         Introduction
         After attempting to use several existing SQL Select Viewers, it became obvious that I needed a rather complex
         or advanced module to support the various options I required. The solution is SQLGridSelectedView. This
         module allows the user to execute a simple SQL select statement, or even the most complex of statements
         and still allows the addition of paging, column sorting, or limit the number of records returned.


         Features of SQLGridSelectedView:
             •   Designed for simple or complex selects
             •   Supports Stored Procedures
             •   Allows for selection based on Date from Date List
             •   Allows for selection based on Text value (up to 5 different selections)
             •   Allows for selection based on Populated Dropdown values
             •   Allows for selection based on defined conditions
             •   Optionally Sortable columns
             •   3 Different Types of Paging
             •   Export to Excel via CSV or Text file
             •   User definable delimiter in export file
             •   Optional Column Headers and Column Formating
             •   Group by phrase support for complex Selects
             •   Parameter substitution for DotNetNuke®, Session, or Query String Values
             •   Connection String may be defined in Web.Config
             •   Will work with any database that support an OLEDB or ODBC interface
                 (MSSQL, Access, MySQL, Oracle, Firebird, ….)
             •   Header, Footer and Direction Sections
             •   Definable display classes - Module.css included with default classes
             •   Calendar Select Option on Date Selection Filter
             •   Filter Selection allows different Display and Selection and/or Multiple-Select values
             •   Single "Go" Button with the ability to define the button label text
             •   "Reset" Button option
             •   Allow users to Create/Update/Delete selected records
             •   Column Summarization


         This version of SQLGridSelectedView is built on ASP.NET V1.1. However the version will work with DotNetNuke
         Version 3.5+ based on ASP.Net V1.1 and Version V4.3+ based on ASP.Net V2.0.




         TressleWorks.ca                                        1                                        1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                       TressleWorks.ca


         Getting Started – Simple Grid Example
         The following section will introduce SQLGridSelectedView to new users, if you have used the Module before,
         please refer to the Reference Section
         Note: The Simple Grid example uses the NorthWind Database provided with SQL 2000 and other databases.
         If the NorthWind database is not available, then use any similar table from other sample databases.
         Install Module on DNN Site – SQLGridSelectedView is shipped as a private assembly (PA) that can be
         installed on a DNN site using the Upload New Module link under Host Menu Selection of Module Definitions.
         You will be promoted to supply the location of the Module. The Module will typically have a name like
         SQLGridSelectedView_PA_v4_0x.zip (where x is a letter(s) indicating version)
         When uploaded, DNN will unzip and load the module into DNN based on the manifest file contained in the Zip
         file. DNN will report the progress on the install. If no errors (lines reported in RED), click on the Return link
         found a the end of the page. This will return you to the Module Definitions page. You will now be able to
         locate SQLGridSelectedView in the list.


         Add Module to Page – Create a new page and add SQLGridSelectedView as a new module – Set the
         page title to Simple Grid Display – and click Add. The following will be displayed on the page.




         Click on the Edit SQLGridSelectedView Settings link to gain access to the Module Settings. The initial Setting
         screen will be displayed as shown below:




         TressleWorks.ca                                         2                                     1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                   TressleWorks.ca




         This form expands the SQL Statement section by default, the remaining sections are collapsed to reduce the
         form size. To see all sections, Click on the “Expand All Sections” command link.
         Define connection to database – An explicit connection string can be used such as:
                 Server=(local);Database=NorthWind;uid=uuuu;pwd=pppp;
         The definition of the connection string to the Northwind database can be defined in the Web.Config in the
         AppSettings. This is a feature of SQLGridSelectedView to allow access to the database and “hide” the
         connection details. The example uses the NorthWind AppSetting define Connection string – ‘@Northwind”



         TressleWorks.ca                                      3                                    1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                   TressleWorks.ca


         Once a connection string is defined, a SQL selection command is needed. Enter “Select * from Customers” as
         the SQL Select Statement.




         Once the Select statement is entered, clicking on Update, will cause SQLGridSelectedView to process all the
         Selected options and generate the Data view.




         Since no options were selected, the generated report has some issues:
             •   No real formatting has been applied
             •   All the records are displayed.
             •   Total number of records unknown




         TressleWorks.ca                                       4                                    1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                     TressleWorks.ca

         To correct the noted issues, let’s apply some default options:
              •   No real formatting - add default CSS style to display
                         o   Open CSS Classes Section (click on the +)
                         o   Select (check) the Use Default Module Classes
              •   All the records are displayed – add paging to limit the number of rows displayed per page
                         o   Open the Paging Section (click on the +)
                         o   Enable default paging by selecting (check) Enable
              •   Total Number of Records unknown
                         o   Open the Processing Options (click on the +)
                         o   Select (Check) the Display Record Count
              •   Click on Update to Save and process the options.


         The result is




         The report now is nicely formated, paged (<< Prev Next >>) and displays the total count of 91 records




         TressleWorks.ca                                         5                                   1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                      TressleWorks.ca


         To Add a Filter for Countries – To select records based on Country add the filter selection as follows:
              •   Open Filter Selections Section (click on the +)
              •   Select (Check) the Enable box on the first filter selection
              •   Enter “Country” (no-quotes) into the Parm Name field
              •   Enter “Select Country” into Promt field
              •   Enter “Select distinct Country from Customers order by Country” into Select field
              •   Enter “Country = '[Parm:Country]'” into Clause
              •   Select (Check) the “Allow All” - this will allow all records to be selected.
              •   Select (Check) the “Required” – this specifices that a selection must be made


         The Filter setting should look like this




         Click on Update command link at either the top or bottom of the module page to Save and process the
         settings. The result is




         Notice the addition of the Select Country Label and DropDown box. Since “Allow All” and “Required” options
         were checked, all records are reported.




         TressleWorks.ca                                         6                                    1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                     TressleWorks.ca


         By Clicking on the Dropdown, you can see that it has been populated by the results of the filter select “Select
         distinct Country from Customers order by Country”.




         Selecting a country results in




         TressleWorks.ca                                        7                                    1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                      TressleWorks.ca


         Options Reference
         Command Links
         On the SQLGridSelectView Settings Page, there are two sets of Command Links. One located at the top of
         the form and other at the bottom. Both sets of Command Links operate the same.



         The “Update” Link will save the settings as defined, and will cause the display to be processed.
         The “Apply” Link will save the settings as defined and return to the Settings form.
         The “Cancel” Link will return to the display form without saving the most recent changes.
         The “Expand All Sections” appears only on the upper set of links. Clicking the link will expand all the sections
         of the Settings form and the link will then change to “Collapse All Sections”. Clicking the link will close the
         open sections and revert the link back to “Expand All Sections”



         SQL Statement




         The SQL Statement field allows the user to enter the Primary Select statement used to generate the resulting
         display. The select statement should take the form of
                 Select <fields> From <tables> [ where <where clause> [ Order By <sort fields> ]]
         The select statement can be a maximum of 16000 characters (!)
         Stored Procedures are supported. To use a stored procedure start the statement with "EXEC". For example:
                 Exec MyStoredProcedure '[DATE:To]', '[DATE:From]'
         To pass values to the stored procedure, just place the appropriate substitution string as you would for the
         Select statement
         SQLGridSelectedView will remove all SQL comments prior to processing your command so you can leave
         in-line comments (/* ... */) and end of line comments ( -- ... eol ) in the SQL Statement if you wish.
         In the event of an error during the processing of SQL Statement, SQLGridSelectedView will only display the
         error message provided by SQL.




         TressleWorks.ca                                        8                                     1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                      TressleWorks.ca



         Connection String
         The Connection String field of the SQL Statement Section should contain the appropriate connection string to
         connect to an alternate database. To reference tables in database used by DotNetNuke, leave the field empty.
         Any connection string should define the provider being used. A typical connection string has the format of:
                 server=ssssss;uid=uuuuu;pwd=pppppp;database=dddddd;
         SQLGridSelectedView supports native SQL Client connections, plus OLEDB or ODBC connections. If a
         “Provider=” is supplied in the connection string, an OLEDB connection will be attempted. If a “Driver=” is
         supplied then a ODBC connection will be attempted. The default is of SQL Native connection will be used if
         neither “Provider=” or “Driver=” is supplied. SQL Native connections will work in Medium Trust, where OLEDB
         and ODBC required FULL trust in a .Net V2.0 environment.
         The user can reference an AppSetting from the Web.Config file. For example, the Default DNN AppSetting for
         the database connection string is called SiteSqlServer. To reference this AppSetting, set the connection string
         to "@SiteSqlServer". A defined AppSetting can be used simply by placing an "@" character prior to the desired
         AppSetting. This feature is handy when exporting examples to other sites where the connection string may be
         different, but the AppSetting is the same.
         The following snippet from the Web.Config shows the NorthWind AppSetting along with the DotNetNuke
         defined SiteSqlServer Setting.

                 <appSettings>
                   <add key="SiteSqlServer" value="Server=(local);Database=DotNetNuke;uid=???;pwd=???;" />
                   <add key="NorthWind" value="Server=(local);Database=NorthWind;uid=???;pwd=???;" />
                   …
                 </appSettings>



         Sample Connection Strings
         To access a Microsoft SQL Express database, use a connection string similar to the following:
                 Provider=SQLNCLI; Server=.\SQLEXPRESS; Database=YourDB; uid=xxxx; pwd=yyyy;
         To access a Microsoft Access database use, a connection string similar to the following:
                 Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\yourdirectory\accessdb.mdb;
                 User id=;Password=;
         To access a Microsoft Excel spreadsheet use, a connection string similar to the following:
                 Provider=Microsoft.Jet.OLEDB.4.0;Data Source= D:\yourdirectory\YourExcel.xls;
                 Extended Properties="Excel 8.0;HDR=No;IMEX=1"




         TressleWorks.ca                                        9                                     1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                      TressleWorks.ca



         Processing Options




         The option "Select before First Display" when checked, delays the display of the output Grid until the user has
         a chance to select from the various options provided.

         The user can select whether one "Go" button will be shown. The default is a "Go" button after each section.
         By checking the "Single Go Button" check box, a single "Go" button will be shown after the filter selections.
         The "Go" label text can be defined by entering the desired phase in the text box provided. The default is "Go".

         The user can select to “Display Reset button”. This button when present will reset the display fields back to
         the original values defined when the module was first displayed. The reset button label text can be defined by
         entering the desired phase in the text box provided. The default is "Reset".

         The option "Display Record Count" when checked directs the module to display the total number of records
         returned on a line below the displayed grid. The you can define the text displayed when the Record Count is
         requested. The "{1}" string defines the location for the value of the Record count. The default is text is "{1}
         Total Records".

         The option "Display Time Stamp", when checked will direct the module to display the current server time on
         the same line as the record. Then you can define the text displayed when the Time Stamp is requested. The
         "{1}" string defines the location for the value of the current server time. The default is text is "As of {1}"

         The “Check for Injection” option, when checked, will scan the user input on the display form for possible
         injection attacks. Certain keywords will be deleted from the commands if found such as SELECT, INSERT,
         DELETE, EXEC, “—“, “XP_”. Also any single quote will be changed to two single quotes.




         TressleWorks.ca                                        10                                    1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                       TressleWorks.ca

         The two options "Display SQL Command" and “Display SQL Command on Error” when checked directs the
         module to display the resulting SQL Command based on all the user selected options. This is typically used to
         test the processing and to ensure the appropriate options are being processed as expected. Also displayed
         are the Substitution Variable defined by user input. Additionally, the user can define a message to be used
         when an error is encounter by enabling "Display User Defined SQL Error Message". The substitution variable
         [SQLError] can be used to insert the error into the User Defined message.

         The "Drop Down Labels" allow the user to define the string used to define "--ALL--" and "--Not Applicable--".
         This is especially useful in localization of the module display.

         The Grid Options allow the user to alter the appearance of the Grid. Specifically, “Enable Grid Header” allow
         the user to control the appearance of the Grid Column Header. The default is enable, but disabling
         (unchecking) will hide the Grid Header. Similarly, “Enable Gridlines” allows the user to control the appearance
         of the gridlines within the Grid. The default is to enable gridlines. The "Grid Width" allows the user to define a
         specific with grid. Leave this value blank to allow a full width grid.

         You can specific the color of the Gridlines using the define HTML color names (See
         http://www.w3schools.com/html/html_colornames.asp ) or via the numeric equivalent (ARGB) in hexidecimal
         form. The default is “LightGray”.




         TressleWorks.ca                                        11                                     1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                       TressleWorks.ca


         Header/Directions/Footer
         This section defines text statements that can be displayed along with the results.




         The Header appears immediately before the selections (if any).
         The Directions appears immediately after the selection and before the results
         The Footer appears at the end of the display. If the Grid is not display due to the Select Before First Display
         option being enabled, the footer will not be displayed until the Grid is displayed.
         You may use HTML formatting within the sections. Where possible, Substitution Variables are resolved.
         Please note that some values are not known at the time the various sections are processed.
         The above settings will generate the following display:




         TressleWorks.ca                                        12                                     1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                    TressleWorks.ca



         When Enabled and no data is returned by the primary select, the message defined in the No Data Message
         will be displayed on the form in place of the empty data grid.




         The following example is based on the settings above when no data was returned. Notice that HTML
         formatting can be applied and Substitution will be performed.




         Additionally, the record count and timestamp if requested will not be displayed when no data is returned.




         TressleWorks.ca                                      13                                    1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                       TressleWorks.ca



         Top Count




         When "Enabled", directs the module to display a selection of values in a drop down list that will limit the total
         number of records returned by the Query by using the "Top <n>" feature of Microsoft T-SQL. Note: Not all
         Databases support this feature – you may encounter an error in such a case.
         The "Allow All" option when checked, directs the module to place an entry in the Top Count Selection to allow
         the user to select all records to be returned.
         The "Label" option allows the user to define the associated label with the Top Count drop down list.
         The "Values" textbox allows the user to define the selection values the user can choose from. The value must
         be numeric and separated by the semi-colon (";"). For example "10;20;30;100" will place the values 10, 20,
         30, and 100 in the drop down list. The user must provide the values for the field. You can select a default
         value by adding a "*" to the end of a value. For example: "10;20;30*;100" would make 30 the default value.
         The "Remove" check box will clear the Top Count options and disable the option on the next Update or Apply.


         The above settings will add the following selection to the display. Notice that 50 is defined as the default.




         TressleWorks.ca                                        14                                     1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                      TressleWorks.ca



         Text Search Selections




         When "Enabled", directs the module to perform filtering based on the clause defined.
         The "Numeric" check box will force the value entered to be numeric, otherwise an error will occur. This helps
         when the target of the search is a numeric type column such as INT.
         The "Ignore if Empty" check box will skip the addition of the Search clause when the Text box is empty.
         The "Label" option allows the user to define the associated label with the Text Search Textbox.
         The "Default Value" option allow the user to define a value to be initially displayed in the Text Search Textbox.
         This works well where a condition is to be searched on the initial display of the Grid.
         The "Search Clause" defines the clause that will be added to SQL Select statment. The user entered value will
         be substituted where [SEARCH:Text]' is found in the search clause. For example:
                           Employees.LastName like '%[Search:Text]%'
         Since the Text Search selection clause will be added to other selection clauses, the user must not include
         conjunction operation of "AND or "OR". The conjunction operator of "AND" is assumed and will be
         automatically added if required.
         The additional Text Searches use [Searchn:Text] where n = 2, 3, 4. For example [Search3:Text] would
         reference the 3rd Text Search value.
         The "Remove" check box will clear the Text Search options and disable the option on the next Update or Apply.




         TressleWorks.ca                                       15                                     1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                      TressleWorks.ca



         Date Range Selections
         The Date selection options allows the user to define a date range for inclusion in the generated where clause.




         The "Parm Name" field defines the name of the range being used.
         The "Label" option allows the user to define the associated label that appears prior to the From date selection
         textbox on the generated form.
         The "From Date" textbox defines the starting date (and optionally time) period, where the "To Date" defines
         the ending date (and time). The value supplied can be an explicit date or a relative date in the form of "unit ;
         units ; display-format".
         The unit field defines the date/time unit ("m=months, d=days, h=hours, n=minutes, s=seconds"). The units
         define the number of units relative to today, so "d;-1" refers to today less 1 day. The display-format defines
         how the relative date will be displayed in the selection textbox. The format is based on the .NET datetime
         format. (See ASP.NET Date Format Specification - http://msdn2.microsoft.com/en-us/library/8kb3ddd4.aspx )
         For example, "yyyy-MMM-dd HH:mm:ss" will display the date like "2005-Jan-17 21:33:18" This relative date
         format string will work in both From Date and To Date textboxes.
         Also allowed are several predefined keywords


                 Date Value            Result
                 Now                   Will display today’s current date and time
                 Yesterday             Will display yesterday’s date with a time of 00:00
                 Today                 Will display today’s date with a time of 00:00
                 Tommorrow             Will display tomorrow’s date with a time of 00:00
                 D;-1                  Will display 24 hours previous – yesterday’s date with current tiime
                 M;-1;yyyy-MMM-dd      Will display one month earlier – with no time shown.
                                       SQL wil assume a time of 00:00
         The format characters are based on the standard VB.NET date format strings. Please note that what ever
         value is entered into the date boxes on the generated page will be passed directly to the SQL Select statement
         for processing. SQLGridSelectedView does not validate the entered values.
         The above settings will generate a user prompt of:




         TressleWorks.ca                                        16                                    1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                      TressleWorks.ca


         Since the creation of a date range where "Clause" can be very difficult, the appropriate where clause is left for
         the user to define. The values selected by the user will be placed in to substitution values defined as
         "[DATE:from]" and "[DATE:to]". A typical where clause would look like
            RequestDateTime between '[DATE:from]' and '[DATE:to]'
         The second date clause will use [Date2:From] and [Date2:To] parameters.
         Since the Date selection where clause will be added to other selection clauses, the user must not include
         conjunction operators of "AND” or "OR". The conjunction operator of "AND" is assumed and will be
         automatically added, if required.
         The “Show Calendar” checkbox will enable the display of a calendar link for both the From and To dates.




         The “With Time” checkbox will enable the entry of Time in the Calendar box.
         The "Remove" check box will clear the corresponding Date Range options and disable the option on the next
         Update or Apply.




         TressleWorks.ca                                        17                                    1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                       TressleWorks.ca



         Date List Selections
         When enabled, allows the selectio of data based on the user selection of a date from a list of dates.




                                                   The “Start Date” field defines the starting date for the list, while the
                                                   “Stop Date” defines the end date. Start and Stop dates support valid
                                                   date and specific Keyword as described in the Date Range Selection.

                                                   The “Prompt” field defines the user prompt given to display with the
                                                   Date List.

                                                   If entered, the ”Default Date” determines the pre-selected date in
                                                   the list.

                                                   The “Display Format” is the date format displayed in the date list
                                                   dropdown, while the “Parameter Format” is the date format used in
                                                   the parameter substitution.

                                                   The “Clause” defines the where clause that will be added to the SQL
                                                   Select statment. The user entered value will be substituted
                                                   whereever [DATELIST:Date] is located in the clause. The second
                                                   occurence will substitue [DATELIST2:Date].
                                                   Since the Date selection where clause will be added to other
                                                   selection clauses, the user must not include conjunction operators of
                                                   "AND” or "OR". The conjunction operator of "AND" is assumed and
                                                   will be automatically added if required.
                                                   The "Remove" check box will clear the corresponding Date List
                                                   options and disable the option on the next Update or Apply.




         TressleWorks.ca                                       18                                      1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                      TressleWorks.ca



         Filter Selections
         SQLGridSelectedView defines up to 5 filters selection that can be added to the generated SQL Select. Each
         enabled selection will direct the module to create a Drop Down List containing appropriated values to be
         selected. The following description is valid for any one of the 5 Selections.




         The "Parm Name" defines the name of the parameter used in the selection of the value for the selection Drop
         Down List.
         The "Label" option allows the user to define the associated label what that appears prior to the selection's
         Drop Down List.
         The "Select" textbox defines the selection statement used to define the values placed in the Drop Down List.
         The selection statement is typically something like "Select distinct <field> as <parm name> from <table>
         order by <parm name>" for example;
                 Select distinct ZoneNumber as Zone From Deliveries order by Zone
         NOTE: The "as Zone" is only required as the field name if it is not the same as the Parm Name defined.
         The table referenced in this Select Statement will use the same connection string as defined for the primary
         Select statement.
         The "Clause" textbox defines the where clause to be added to the select state. The user selected value will be
         placed in a substitution value defined as [PARM:<parm name>]. For example, based on the example select
         statement, the substitution value will be [PARM:Zone]. The Selection where clause could look like
         "Deliveries.ZoneNumber = '[PARM:Zone]' ". The conjunction operator of "AND" is assumed and will be
         automatically added, if required.
         The "Required" option, when checked, requires the user to select a value prior to displaying the Grid.




         TressleWorks.ca                                       19                                     1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                            TressleWorks.ca

         The "Allow All" option will allow the user to select all of the values in the selection. Basically, if the All option is
         selected by the user, the where clause is omitted from the generated SQL.
         The "Wait on Above" option will combine the prior cause to this entry's select clause to create a drill down
         capability. This option is rather complicated and needs some explaining. See the document called "Drill down
         functionality of SQLGridSelectedView"
         The "Multi-Select" option when checked allows the user to select more than one item from the displayed list
         box. When the Items are placed in the Select area, each Item is enclosed in single quotes and separated by
         commas. This feature is especially useful with the T-SQL "in" parameter.
                 NOTE: The SQL Select statement must support the the return of multiple values. The use of the SQL
                 keyword “IN” is recommend. For example: Select * from mytable where ID in ([Parm:ID])
         The "Remove" check box will clear the corresponding Filter options on the next update or apply.
         The above selection results in the following prompt:




         The Select may contain a list of values to be used. In certain cases, the values are known and fixed and there
         is no reason to select the values from the database. To define a list of values, the Select textbox must start
         with "Values:" followed by a comma-delimited list of values. For example: Values: Jan, Feb, Mar, Apr, May,
         Jun, Jul, Aug, Sep, Oct, Nov, Dec will result in a selection of the 12 values listed.




         The above setting will result in the following prompt:




         The Displayed Dropdown text may be different from the selected Values. By providing two parameter names
         in the form of Display parameter name followed by Value parameter name - separated by a semi-colon –
         along with a corresponding select statement that returns two columns of data with the same parameter
         names, will allow SQLGridSelectedView will display the appropriate text, and allow the values selected to
         be used in the Clause.




         TressleWorks.ca                                           20                                       1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                      TressleWorks.ca

         The above setting will result in the following prompt:




         An equivalent capability for the static values exists. To define a list of values, the Select textbox must start
         with "DisplayValues:" and each selection value must consist of the display text followed by the selection value
         separated by a semi-colon. Each selection must be separated by a comma.




         The above setting will result in the following prompt:




         TressleWorks.ca                                          21                                  1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                   TressleWorks.ca



         Conditions
         Conditions are defined requirements that must exist for the data to be displayed.




         When a Condition is "Enabled", the module will display a checkbox with the defined label. The Condition
         clause will be added to the Select if the user checks the conditions.

         The user can define if the condition is to be "checked" on launch of the module by setting the "Checked" box
         as required.

         The "Remove" check box will clear the corresponding Condition options on the next update or
         apply.

         The above settings defines a report like the following:




         Hint: Ensure at least one of the Conditions is “Checked”. When conditions are such that if none are selected,
         some data will appear.




         TressleWorks.ca                                       22                                  1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                     TressleWorks.ca



         Template Based Display
         In some cases, the use of a Grid to display the data is not appropriate. This section allows the user to define
         a template to be used as the display area instead of the Grid. Note: This functionality was once part of
         SQLPageSelectedView, which is no longer supported)




         The following image is an example of the default template created when no template file is defined. A simple
         two column grid is created with the Data Column name and the Data Column Value. Up to 3 additional rows
         are added – one each for Create, Update, and Delete if Editing is enabled.




         So what is a "page"? Well that is up to the user. SqlGridSelectedView provides the user with a way to
         provide a template that will be used to display the results of the SQL Select Statement. The user can provide a
         prefix that will be executed prior to the data template and/or provide a suffix that will be executed after the
         data template. So, the user could open a table in the prefix, display the rows and details in the data template
         and close the table in the suffix.
         The template files should be placed in portal root – no pathing is requred. (eg. Mytemplate.txt) The user can
         define 3 different template files that will be used to generate the page.
         Each template file is basically a text file containing HTML code plus placeholders to data insertion.
         SQLGridSelectedView provides the following placeholders that can be use in the various template files


               Placeholder                 Desciption                                      Prefix   Data    Suffix
               SGSV:GridClass              Inserts the Grid   Class                          X       x        x
               SGSV:GridHeader             Inserts the Grid   Header class                   X       X        X
               SGSV:GridFooter             Inserts the Grid   Footer class                   X       X        X
               SGSV:GridItem               Inserts the Grid   Item class                     X       X        X



         TressleWorks.ca                                        23                                   1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                              TressleWorks.ca


               SGSV:GridAlternatingItem       Inserts   the   Grid Alternating Item Class              X      X       X
               SGSV:GridPage                  Inserts   the   Grid Page class                          X      X       X
               SGSV:GridColumn                Inserts   the   Grid Column class                        X      X       X
               SGSV:HeaderClass               Inserts   the   Grid Header class                        X      X       X
               SGSV:PromptLabelClass          Inserts   the   PromptLabel class                        X      X       X

               SGSV:ColumnName:n              Inserts   the   ColumnName for column n                         X
               SGSV:ColumnValue:n             Inserts   the   ColumnValue for column n                        X
               SGSV:ColumnValue:Name          Inserts   the   ColumnValue for Column Name                     X
               SGSV:CurrentPage               Inserts   the   current page number                      X      X       X
               SGSV:PageCount                 Inserts   the   total page count                         X      X       X
               SGSV:RecordNumber              Inserts   the   current record number                           X


         The Prefix Template file contains
                 <center>
                 <table border="1" width="600px" class="SPSV_Grid" bordercolor="lightgrey" >
                 <tr>
                     <td colspan=2><h1>This is from the Prefix File - [SPSV:ColumnValue:LastName]</h1></td>
                 </tr>

         While the Data Template file contains
                 <tr class="SPSV_GridItem" >
                     <td class="SPSV_GridHeader" width="300px">Record Number</td>
                     <td>[SPSV:RecordNumber]</td>
                 </tr>
                 <tr class="SPSV_GridAlternateItem" >
                     <td class="SPSV_GridHeader" width="300px">[SPSV:ColumnName:0]</td>
                     <td>[SPSV:ColumnValue:0]</td>
                 </tr>
                 <tr class="SPSV_GridItem" >
                     <td class="SPSV_GridHeader" width="300px">[SPSV:ColumnName:1]</td>
                    <td>[SPSV:ColumnValue:1]</td>
                 </tr>
                 <tr class="SPSV_GridAlternateItem" >
                     <td class="SPSV_GridHeader" width="300px">[SPSV:ColumnName:SettingValue]</td>
                     <td>[SPSV:ColumnValue:SettingValue]</td>
                 </tr>
                 <tr>
                     <td colspan=2>&nbsp;</td>
                 </tr>

         And lastly the Suffix Template file contains
                 <tr>
                     <td colspan=2><h1>This is from the suffix File - [SPSV:ColumnValue:LastName]</h1></td>
                 </tr>
                 </table>
                 </center>

         The above template generated the following display.




         TressleWorks.ca                                             24                                       1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                        TressleWorks.ca




         The [SPSV: …] substitution variable were used is a prior module called SQLPageSelectedView and are
         maintained for legacy reasons.

         The general format is

                 [SPSV:ColumnName: {# | <column name> } ]

         for example:

                 [SPSV:ColumnName:1] returns the name of the column 1. (Column numbers start at 0)
                 [SPSV:Name:LastName] returns the column name of column named “LastName”
                 [SPSV:N:1] returns the column name of column 1.

         Similarly, the data base values are substutited using the following general format:

                 [SPSV:ColumnValue : {# | <column name> } : format ]

         where forma tis optional. For example:

                 [SPSV:ColumnValue:1] returns the value of the column 1.
                 [SPSV:Value:LastName] returns the value of column named “LastName”
                 [SPSV:V:1:BOOLEAN] returns the Boolenavalue of column 1.
                        if column value any of "1", "T", "TRUE", "YES", "Y", "OK", "OUI", "ON", "SET"
                        will return "True" else "False"

         Other valid formats are:
                 DATE:format for example
                         [SPSV:Value:2:DATE:yyyy/MM/DD] will format the date value as yyyy/MM/DD
                 INTEGER:format
                         [SPSV:Value:2:INTEGER:x] will format the integer value as Hexidecimal
                 SINGLE:format
                         [SPSV:Value:2:SINGLE:c] will format the single value as Currency

         When enabled “Display Paging Above Template” will cause the paging to appear above the template display, if
         disabled the paging will appear at the bottom of the template section. If fewer records exist than defined for
         a page then no paging will appear.



         TressleWorks.ca                                             25                                 1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                       TressleWorks.ca


         Paging
         When Paging is "Enabled", the module will display only a set number of resultant records.




         The "Page type" defines 3 choices: "Prev-Next", "Numeric", and "Custom". Prev-Next and Numeric are the two
         available formats defined for the data grid and will provide either a "Prev" and "Next" links or a numeric list of
         pages as links. The "Custom" page type allows SQLGridSelectedView to manage the paging.
         Under the Custom Page type there are several formats allowed:
             •   Prev-Next: Similar in nature to the Datagrid's Prev-Next format, however the user can define the
                 labels used. The default labels are "<" for Prev and ">" for Next.
             •   First-Last: Useful for 1 to 2 page results. User can define the labels. The default are "<<" for first
                 and ">>" for Last.
             •   First-Prev-Next-Last: Combines the above two formats.
             •   Numeric - Similar in nature to the Datagrid's Numeric format.
             •   Numeric-First-Last: Combines the First-Last format and the Numeric format.
             •   All: Combines all formats together - Very good for large results
         Also under custom format, you can define the horizontal alignment of the paging links.
         The default “Rows per Page” is 25, but the user can set the number by supplying a "Rows per Page" numeric
         value. When checked, the "Display Page Count" will direct the module to report the current page number and
         number of total pages. If the number of records selected creates only one page then the page links are not
         displayed.
         The user has the ability to define the text displayed with the Page Count. {1} will be replaced with the page
         and {2} will be replaced with the total number of pages.


         The following report shows the most complex custom paging settings with Timestamp, Row Count, Export
         and Footer all displayed.




         TressleWorks.ca                                        26                                     1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                         TressleWorks.ca



         Sorting
         By selecting the "Sortable" option, the user directs the module to generate a Grid where the columns are
         sortable.




         Links will be created in the column headers to allow the user to sort the column. Clicking the column once will
         perform an ascending sort, clicking the same column again will cause the column to be sorted indescending
         order.
         The "Initial Sort" clause will be appended to the generated select statement, if no pre-existing sort clause
         exists. If a column sort is selected, then the initial sort will be ignored along with any pre-existing sort clause.
         The "Fixed Sort" clause will be appended to all generated select statements. Specifically, it will be added to
         any pre-existing sort clause or initial sort clause or column sort as the least sort field. Use the option to
         display the generated select statement to verify the sort clause generated.
         Group By clause allows the user to define a Group By clause that will be maintained by the module for
         processing.



         Sorting and Stored Procedures
         It is possible to implement sort with stored procedures, however the procedure must support the passing of a
         sort parameter. SQLGridSelectedView defines a substitution variable called [SortExpression] that must be
         passed to sorted procedure. The Stored Procedure must then sort the resultset based on the value.




         TressleWorks.ca                                         27                                      1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                     TressleWorks.ca



         CSS Classes




         This section allows the user to define the appropriate CSS classes used to display the various parts of the Data.
             •   Grid Class: sets the DataGrids class
             •   Header Class: sets the class of the column headers
             •   Item Class: sets the class of the items displayed.
             •   Alternate Item Class: sets the class of the alternating items
             •   Page Class: sets the class used by the page links when paging is enabled
             •   Footer Class: sets the class used by the footer containing the record count, timestamp and page
                 counts
             •   Label Prompt Class: sets the class used by the Label prompts
             •   Default Column Class: set the class used by all columns
         The check box “Use Default Module Classes” will populate the fields with the default SQLGridSelectedView
         Classes when the data is saved (Update or Applied). The default styles are defined by the Module.css.
         SQLGridSelectedView now ships with it's own Module.css file. This file will be loaded by DotNetNuke when
         the module is used. The user may select other styles or just update the module.css located in the
         DesktopModules/SQLGridSelectedView directory. The use of the module.css class style is optional.


                                 Important Note: If you have created a Module.css for a prior
                                 version of SQLGridSelectedView, please backup the file prior to
                                 uploading this new version as the file will be overwritten.




         TressleWorks.ca                                       28                                    1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                    TressleWorks.ca


         SQLGridSelectedView now supports “Style Expressions”, which allows applying of a CSS Class when a
         specific data value is reached. In the above example, if the value of the Country column is equal to “UK” then
         the specific Row has the NormalRed Style applied. The format for the expression is:
                 IF <columnname> <condition> “<value>” SET <cssclass>
                 IF <columnname> <condition> “<value>” SETCOLUMN <Column> AS <cssclass>
         Each expression must start on a new line, and the value must be enclosed in double quotes or square
         brackets. Valid conditions are “=”, “<>”, “>” , “<”, “>=”, “<=” and “like”. The Like condition will match
         partical string using the “*” as a wildcard match. Additionally. the columnname (and optionally the column)
         must exist in the selected data.
         You may also compare two columns values – If the value is enclosed in square brackets then value is assumed
         to be a column value. The column must exist in the selected data.
         When SETCOLUMN format is used, the only the defined column in the row will be assigned the CSS class




         The above example shows the NormalRed style applied to the row where Country is equal to “UK”
         The user is cautioned to limit the number of expressions when a large number of expected rows are returned
         as each row is tested for each expression resulting in sluggish response times.




         TressleWorks.ca                                      29                                    1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                            TressleWorks.ca



         Column Formatting and Summary




         The Column Format is defined as a string with format: <column 1 setting>/<column 2 setting>/...where
         each column setting has the format of:
                 @Item Class[,@Header Class]
                 or
                 width [,halign [,height [,valign [,format string]]]]
         If the string starts with the @ character then a class name is assumed, otherwise the simple format is
         assumed. Width is the column width - if omitted will default to best fit; halign is the horizontal alignment of
         the column - "L" for left, "R" for Right, "C" for center, "J" for justified - defaults to Left; height defines the cell
         height for the column; valign defines the vertical alignment in the item cell - "T" for Top, "B" for bottom, "M"
         for middle - defaults to Top; format string defines the possible format string for the column as defined by
         DateFormatString method
          (See ASP.NET Date Format Specification @ [http://msdn2.microsoft.com/en-us/library/8kb3ddd4.aspx)
         For example: "100,R/100,L/100,C///,,,,{0:C}" defines column1 as 100px wide and right-aligned, column2 as
         100px wide and left-aligned, column3 is 100px wide and center-aligned, column4 and column5 are defaulted,
         and lastly column6 has a format string for currency display. Another handy use of the format string is for date
         display - for example {0:dd-MMM-yy hh:mm}.
         The Column Format Delimiter defines the character that separates the columns. The default is “/”, however if
         a date format uses the “/” as a separator, you must select a different separator. Suggest “;” as shown in the
         Setting above.
         Note: Column Width and Horizontal alignment will also be applied to the Column Header.
         The “Columns To Sum” field defines the column names that SQLGridSelectedView are to sum. An additional
         row is added to the display results that contain the total value for the columns defined. Separate each column
         by a semi-colon (“;”) .
         The “Summary Label” is used to place a word or phrase indiciating a total is present. The “Column Location”
         refers to the column where the label is placed. The column must have a string data type.
         The above settings generated the folllowing result – just the last few lines are displayed. Note the Totals
         Label has been bolded by adding the appropriate HMTL code to the label.




         TressleWorks.ca                                           30                                       1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                     TressleWorks.ca



         Export
         When "Enabled", this section will allow the user to export the data displayed in the Data Display.




         Please note: Only the data is exported along with the column names. There are currently two formats
         available: CSV File (Default Delimiter:Comma) associated with Excel, or Text File (Default Delimiter:Tab).
         You may override the default delimiter with a character or string of your choice.
         For some users, the column names are not required, so the option to include or exclude the column names is
         available.
         The File name created will default to "SqlGridSelectedView_Export_mmmdd_hhmm" where "mmmdd_hhmm"
         refers to current date and time the export file was created. The user can change the Filename Prefix from
         "SqlGridSelectedView_Export" to better describe the file contents. For example, the prefix of
         "Example3_Export" for a Comma-delimited export will result in a file like "Example3_Export_Aug28_1611.csv",
         while a Tab-Delimited export would be "Example3_Export_Aug28_1611.txt"


         NOTE: Dotnetnuke version V4.5 allows for the specific modules to be “wrapped” in a AJAX panel update. This
         feature can be enabled by setting the “Partial Rending” option in the Module Definitions for the
         SQLGridSelectedView view screen. If Partial Rending is enabled, the export feature will not work. This is an
         issue of how AJAX is implemented and not an issue with DotNetNuke or SQLGridSelectedView. Since
         SQLGridSelectedView is based on the ASP.Net V1.1 environment, the module cannot detect the AJAX rending
         is enabled. The issue will be address once SQLGridSelectedView is based on the ASP.NET V2.0 environment.




         TressleWorks.ca                                       31                                    1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                   TressleWorks.ca



         Edit Records
         When Enabled, allows the user to define the ability to Create, Update, and/or Delete a record based on
         records originally selected. NOTE: the module must give EDIT rights to the user.




         The following comments are true for Create, Update and Delete. Where difference exist, they will be
         explained.
         You must “Enable” each major functionality you require.
         The “Key Column” defines the column from the original selection that will provide the key to the specific
         record to be actioned. For example, User_ID is the unique column that identifies a specific record. You can
         reference the Key Column Value in the SQL Command statement as [EDIT:KeyValue]
         The “Key Column Assigned” checkbox when checked indicates that the database will be assigning the value
         and the module is not to prompt for the value. The Key Column can be hidden by checking the “Key Column
         Hidden” Checkbox – this will cause the module not to display the Key Column.
         The “[Action] Confirm Statement” is used to prompt the user to confirm the action prior to proceeding. This is
         typically used on the Delete command.



         TressleWorks.ca                                      32                                    1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                      TressleWorks.ca

         The “[Action] Label” is used to define the column name and command link. For those that perfer
         “Add/Edit/Delete” instead of “Create/Update/Delete”, or where localization is required.
         If the user wants to use the default DNN action icons, select (check) the “Use DNN Icon” box.
         The SQL Command “[Action] Statement” defines the command to be executed when the user selects the
         corresponding action. The command will be preprocessed to substitute the following values to create a valid
         SQL Command:


              Parameter                      Value
              [EDIT:ColumnName:n]            Inserts the column name where n = the column ordinal number (0
                                             = first column)
              [EDIT:ColumnValue:n]           Inserts the column value where n = the column ordinal number
              [EDIT:ColumnName:Name]         Inserts the column name
              [EDIT:ColumnValue:Name]        Inserts the column value for the specific column name
              [EDIT:Name:n]                  Inserts the column name where n = the column ordinal number
              [EDIT:Value:n]                 Inserts the column value where n = the column ordinal number
              [EDIT:Name:Name]               Inserts the column name
              [EDIT:Value:Name]              Inserts the column value for the specific column name
              [EDIT:N:n]                     Inserts the column name where n = the column ordinal number
              [EDIT:V:n]                     Inserts the column value where n = the column ordinal number
              [EDIT:N:Name]                  Inserts the column name
              [EDIT:V:Name]                  Inserts the column value for the specific column name
              [EDIT:ColumnID:n]              Inserts the column ID used to define the field in a template
              [EDIT:ID:n]                    Inserts the column ID used to define the field in a template
              [EDIT:ColumnID:Name]           Inserts the column ID used to define the field in a template
              [EDIT:ID:Name]                 Inserts the column ID used to define the field in a template
              [EDIT:KeyValue]                Inserts the KeyValue associated with this record


         The create specific option of “Ensure Unique Key” when checked will Execute the Select Statement to verify
         that no record exists where the Key Column value exists.
         The create specific option of “Clear Fields” when enabled will clear the column values from the row prior to
         displaying the entry form.
         The “Select Statement” defines the fields that will be shown as part of the Edit form. This allows the user to
         display specific columns and values to define the entry form.
         If the default generated entry form is not as required, the Edit Records functionality allows the user to define
         a Edit form via the “Template File” field in the same manners as the Template Display. The form is parsed by
         ASP.NET before being display so ASP.NET controls may be used. The above parameters are available to
         populate the form. Caution should be taken when using this method as it can make the module unstable.
         Significant testing is recommended once the form is thought to be working. A Edit Template file requires that
         each field use an ID field of [EDIT:ColumnID:Name] or [EDIT:ColumnID:n]. The Template can only have
         ASP:TextBox or ASP:Checkbox fields for data entry at this time.




         TressleWorks.ca                                       33                                     1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                              TressleWorks.ca


         The following is a example of a Edit Template:

              <table>
                 <tr valign="top">
                      <td align="right">
                          <strong>[EDIT:ColumnName:0]:</strong></td>
                      <td align="left">
                         <asp:Textbox id="[EDIT:ColumnID:0]" runat="Server" cssclass="NormalTextBox"
                                 Text="[EDIT:ColumnValue:0]" /></td>
                      <td align="right">
                          <strong>[EDIT:ColumnName:1]:</strong></td>
                      <td align="left">
                          <asp:Textbox id="[EDIT:ColumnID:1]" runat="Server" cssclass="NormalTextBox"
                                 Text="[EDIT:ColumnValue:1]" /></td>
                      <td align="right">
                          <strong>[EDIT:ColumnName:2]:</strong></td>
                      <td align="left">
                          <asp:Textbox id="[EDIT:ColumnID:2]" runat="Server" cssclass="NormalTextBox"
                                  Text="[EDIT:ColumnValue:2]" /></td>
                 </tr>
                 <tr valign="top">
                      <td align="center" colspan="2">
                          <strong>[EDIT:ColumnName:3]</strong></td>
                      <td align="center" colspan="2">
                          <strong>[EDIT:ColumnName:4]</strong></td>
                      <td align="center" colspan="2">
                          <strong>[EDIT:ColumnName:5]</strong></td>
                 </tr>
                 <tr valign="top">
                      <td align="center" colspan="2">
                          <asp:Textbox id="[EDIT:ColumnID:3]" runat="Server" cssclass="NormalTextBox"
                                  Text="[EDIT:ColumnValue:3]" /></td>
                      <td align="center" colspan="2">
                          <asp:Textbox id="[EDIT:ColumnID:4]" runat="Server" cssclass="NormalTextBox"
                                  Text="[EDIT:ColumnValue:4]" /></td>
                      <td align="center" colspan="2">
                         <asp:Textbox id="[EDIT:ColumnID:5]" runat="Server"
                                  cssclass="NormalTextBox" Text="[EDIT:ColumnValue:2]" /></td>
                 </tr>
                 <tr valign="top">
                      <td align="right">
                          <strong>[EDIT:ColumnName:6]:</strong></td>
                      <td align="left" colspan="5">
                         <asp:Textbox id="[EDIT:ColumnID:6]" runat="Server" cssclass="NormalTextBox"
                                  Text="[EDIT:ColumnValue:6]" maxlength="1000" TextMode="MultiLine"
                                 columns="75" rows="4"/></td>

                 </tr>
                 <tr valign="top">
                     <td align="right">
                         <strong>[EDIT:ColumnName:7]:</strong></td>
                     <td align="left" colspan="5">
                        <asp:checkbox id="[EDIT:ColumnID:7]" runat="Server" cssclass="NormalTextBox"
                         Checked="[EDIT:ColumnValue:7:Boolean]" /></td>
                 </tr>
              </table>




         TressleWorks.ca                                   34                                 1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                   TressleWorks.ca


         The “Header Message” defines a message that will appear above the Record Edit Grid.
         The checkbox “Remain on Create Form after Successful Add” when checked will return user to a new Create
         Form after a successful record has been added to the database. The user must use “Cancel” to return to the
         display form.


         The following form is generated based on the above settings




         In this case, clicking on Update will prompt the use with the confirmation message:




                                                          à




         If a Header message is defined it will appear above the Edit grid as shown: Click “Update” to Save Changes




         TressleWorks.ca                                      35                                   1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                               TressleWorks.ca




         TressleWorks.ca                             36        1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                     TressleWorks.ca



         Variable Substitution
         SQLGridSelectedView will perform substitutions for the following defined DNN values:
             •   PortalID will be substituted for "[DNN:PortalID]"
             •   ModuleID will be substituted for "[DNN:ModuleID]"
             •   TabID will be substituted for "[DNN:TabID]"
             •   UserID will be substituted for "[DNN:UserID]"
             •   The current date will be substituted for "[DNN:Date]" An optional date format can be append as
                 follows: "[DNN:Date:MMM-dd]" will display the date like “Mar-22”
             •   The current date will be substituted for "[DNN:Time]" An optional date format can be append as
                 follows: "[DNN:Time:HH:mm]" will display the time like “21:40”
             •   Current User’s Roles will be substituted for “[DNN:Roles]” The “[DNN:Roles]” substitution will return a
                 comma separated single quoted list of User Roles. In the case of a SuperUser, the role will be
                 ‘SuperUser’ and if not logged on, the role returned is ‘None’. The “[DNN:Roles]” substitution is
                 designed to be used with the “IN” keyword of T-SQL.


         Additionally, SQLGridSelectedView will substitute query variables where "[QUERY:variablename]" is found
         in the Select statement. Aslo see the inline function Encrypt.


                                    SQLGridSelectedView will substitute session variables where
                                    "[SESSION:variablename]" is found in the Select statement. One predefined
                                    session variable called "SessionID" is available without pre-defining any session
                                    variables and will return the current session ID string. Session variables do not
                                    work well in a Web-farm environment.
                                    You may now use any defined selection parameters anywhere in the SQL
                                    Command. For example, in some complex selects, a sub-select may be present
                                    that requires the same date range selection as that used on the primary select.
                                    You may now reference the [Date:to] and [Date:From] in that sub-select. Only
                                    those enabled selections will be available.
                                    To view the defined dynamic substitution variables, enable the “Display SQL
                                    Command” in the Process Options section. The known dynamic substitution
                                    variable are display at the bottom of the SQL Display window.
                                    In the example at the left, the selected Country has a dynamic substitution variable
                                    of [PARM:Country] which has a value of “Denmark”




         TressleWorks.ca                                       37                                    1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                    TressleWorks.ca



         Inline Functions
         SQLGridSelectedView supports specific inline functions that will convert the data prior to display. The
         functions perform post data selection processing to the resulting data returned from the generated Select.
         The syntax of the Inline function is
                 [@<FunctionName>:<SourceColumn><:Options>]
         Where
                 <FunctionName> defines one of the functions described below
                 <SourceColumnName> defines one of the resulting columns from the select.
                 <:Options> defines the optional parameters used by specific functions.

         Inline functions are processed in two parts. The first part alters the SQL Select to ensure the requested data
         will be present in the selected data. The second part combines the selected data into the appropriate column
         of data as requested. Please keep in mind this two-part processing when using in-line functions.




         TressleWorks.ca                                      38                                    1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                 TressleWorks.ca


         HTMLDECODE – This function removes the HTML encoding used              to store HMTL data in certain
         database tables. For example, the Events table encodes the description column. To display the contents of
         the column use a select syntax like:
                           [@HTMLDecode:ColumnName]
                For example:
                           select EventDateBegin,[@Htmldecode:EventDesc] as [Description] from events
                The follow will post process the selected data and HTMLDecode the Description column.
                 The SQL command processed by SQLGridSelectedView will not contain the function reference – just
                the column name. See the included “Primary Select” command as processed.




         TressleWorks.ca                                     39                                  1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                     TressleWorks.ca


         MAKELINK – This function renders the contents as a link. To display the contents of the column as a link
                use the following syntax:
                           [@MAKELINK:ColumnName<<:LinkText>:NewWindow>]
                Where
                           <LinkText> is an optional parameter to define the text associated with the link – if omitted
                                 then the contents of the column will be repeated as the link text.
                           <NewWindow> is an optional parameter of either TRUE or FALSE indicating whether to
                                open the link in a new window – if omitted, the default is false. Note: if you wish to
                                indicate open in a new window without defining a LinkText you must supply two
                                colons. For example
                                           [@MAKELINK:url::TRUE]
                For example:
                           select DateTime, [@MAKELINK:Description:Desc:FALSE] from events
                MakeLink also check for a trailing ColumnName on a selected column. For example
                           …, [@MakeLink:url] as Link, …
                will take the contents of the Column “url”, make a link from it, and place the results in Column “Link”.
                If the trailing ColumnName is not present, then the result will be placed in the source Column (“url”).
                The following select uses two MakeLink functions. The first will populate the Link column with a link
                using the contents of the url as the link text. The second example uses the contents of the column
                “Title” for the link text and also defines the link will open in a new window. To use the contents of
                the database enclose the columnname in square brakets “[title]”, otherwise the word “title” will be
                used.




                Results in the following display:




         TressleWorks.ca                                        40                                   1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                   TressleWorks.ca


         MAILTO – This function renders the contents as a MAILTO: link. To display the contents of the column as a
                MAILTO: link use the following syntax:
                           [@MAILTO:EmailAddress<<:DisplayName>:Subject>]
                Where
                           <EmailAddress> is an required parameter to define the Email Address. EmailAddress is
                                 assumed to be a column name in the selected data.
                           <DisplayName> is an optional parameter to define the text associated with the Email
                                 Address. To use a column in the selected data, enclose the column name in square
                                 brackets, otherwise the text will be used.
                           <Subject> is an optional parameter that defines the Subject of the Email. To use a column
                                 in the selected data, enclose the column name in square brackets, otherwise the text
                                 will be used.
                           <Body> is an optional parameter that defines the Body of the Email. To use a column in the
                                selected data, enclose the column name in square brackets, otherwise the text will be
                                used.
                For example:




                Results in the following display:




                Additional columns will be added to the select to extract the information requested. In the above
                example, several additional columns were added, but donot display in the resulting grid. These are
                used by SQLGridSelectedView to gather the data needed generate the Mailto link.




         TressleWorks.ca                                       41                                  1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                   TressleWorks.ca


                Clicking on the Administrator Account launched Outlook with the follow:




                The mail client used will depend on the client installed on the end user system.
                CAUTION: The maximum length of the MailTo link is 256 characters – this is a limit of the HTML
                standard. If the combined data is very long, it is probable the MailTo: will fail.




         TressleWorks.ca                                      42                                   1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                   TressleWorks.ca


         ENCRYPT – This function renders the associated value in Encrypt form base on the defined
                MachineDecryptionKey located in the WebConfig:
                           [@ENCRYPT:<value>]
                Where
                           <value> is a string that is to be encrypted.


                The ENCRYPT function is different than other inline functions in that is does not have a database
                column reference. The function assumes that <value> will be defined by the creation of a string in
                the Select Statement.
                For example




                The Encrypt inline function is embedded in a string defined as “Link”. Once the SQL Statement is
                executed, the value of the ModuleID will be inserted into the href string as
                           http://localhost/DNNV335/Default.aspx?tabid=134&ItemID=[@Encrypt:503]
                The function will be processed and the end result is a href string of
                           http://localhost/DNNV335/Default.aspx?tabid=134&ItemID=ZWC%2fI3rEM9M%3d
                The function call of [@Encrypt:503] string is replaced the encrypted string of ZWC%2fI3rEM9M%3d
                that can be used only on sites with the exact MachineDecryptionKey. In this example the ItemID is
                used to pass a key value to another module on this same site, so all that is need is a way to Decrypt
                the value.
                The [Query:name] substitution variable has been extended to support Decryption. The [Query-
                Decrypt] Substution variable has the format of
                           [Query-Decrypt:<queryitem>[:defaultvalue] ]
                In the example above “[Query-Decrypt:ItemID:503]”, the Query-Decrypt substitution variable is used
                with a default value of 503. The substitution will take the value from the Queryitem call “ItemID” and
                decrypt it. If “ItemID” does not exist as a Queryitem, then the value of 503 is used.




         TressleWorks.ca                                        43                                 1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                     TressleWorks.ca



         Error Handling
         In the event of an error in the processing of either the primary SQL Statement or the Filter Select statements
         SQLGridSelectedView will display the associated SQL error message.

         The first example show how an error in a Filter will be displayed. The Filter's parameter name is referenced
         so you can determine which filter caused the error.




         In the second example, an error exists in the Primary Select and the processing option "Display SQL Command
         on Error" was selected. The processed command is shown. In the above example, the error is due to the
         missing count required by the "TOP" qualifier.

         NOTE: The line number referenced is based on the command as processed by SQLGridSelectedView. The
         format of the processed command will probably differ from the SQL command provided.




         TressleWorks.ca                                       44                                    1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                       TressleWorks.ca



         Examples
         Setup
         Included in the SQLGridSelectedView Private Assembly (PA) are several import files that use the NorthWind
         database to show the features of SQLGridSelectedView.
         Included in the PA are several files called content.SQLGridSelectedView.NorthWind_ExampleN.xml.
         (where N = 1,2,...) These files will work with the sample database called NorthWind that is included with SQL
         Server.
         BEFORE you will be able to use these files you must perform the following steps:
             1. Copy the file to the root directory of your portal. (.eg: c:\dotnetnuke\portals\0 )


             2. (Optional) Edit the file content.SQLGridSelectedView.NorthWind_ExampleN.xml using
                Notepad or similar and revise the connection string appropriately so that the NorthWind database can
                be accessed. At minimum the password for the "sa" account must be supplied. Find "?????" in the file
                and replace the password and if needed the userid. Several examples use the @NorthWind
                AppSetting for the connection string. You can replace this with a valid connection string to the
                NorthWind database if you choose.
                 If this step is skipped, the samples will probably fail on a SQL Error related to the Connection String.
         Once these steps are completed, add SQLGridSelectedView to a page and select the import option from the
         module menu. The import content page will open and the appropriate xml file should be displayed. Click on
         Import, and SQLGridSelectedView will be loaded with the appropriate setting to display data from the
         NorthWind database.




         TressleWorks.ca                                        45                                     1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                 TressleWorks.ca



         NorthWind Example 1
         This example defines a selection of the orders by Employees where you can select the Date Range, the
         Country and the Lastname of the Salesman.
         Import File: content.SQLGridSelectedView.NorthWind_Example1.xml




         Of Interest:
             •   SQL Commands are displayed in the bottom scroll window. The two Filter selects are shown with the
                 row count found for each




         TressleWorks.ca                                     46                                  1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                  TressleWorks.ca



         NorthWind Example 2
         This example defines a selection of the orders by Employees where you can select the Date Range, the
         Country and the Lastname of the Salesman with the addtion of the ability to enter the lastname of the
         salesman. If is a redundant selection criteria, but it shows how to perform the selection of the same
         information in two ways.
         Import File: content.SQLGridSelectedView.NorthWind_Example2.xml




         TressleWorks.ca                                     47                                   1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                    TressleWorks.ca



         NorthWind Example 3
         This example defines a selection of the Orders Table by Customer ID. While it is a rather simple filtered
         selection, it does feature the Export to Excel option and uses the "@SiteSqlServer" AppSetting for the
         Connection String. DotNetNuke will define the SiteSqlServer AppSetting as part of the default install – you do
         not have to create the setting.
         NOTE: The SQL Select statement assumes that the NorthWind database is on the same SQL Server instance
         as the DotNetNuke database. If this is NOT the case, then this example will fail to find the required Customer
         Table.
         Import File: content.SQLGridSelectedView.NorthWind_Example3.xml




         TressleWorks.ca                                      48                                    1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                  TressleWorks.ca



         NorthWind Example 4
         This example defines a selection of the Orders Table by Customer ID and uses the "@NorthWind" AppSetting
         for the Connection String. Also this example features both column formatting, conditions, altered drop down
         labels and uses the default styles.
         Import File: content.SQLGridSelectedView.NorthWind_Example4.xml




         TressleWorks.ca                                     49                                   1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                TressleWorks.ca



         NorthWind Example 5
         This example defines a selection of the Customers and uses the "@NorthWind" AppSetting for the Connection
         String. Also this example uses the Multi-Select feature of the Filter selections.
         Import File: content.SQLGridSelectedView.NorthWind_Example5.xml




         TressleWorks.ca                                    50                                  1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com
         SQLGridSelectedView 4.1                                                                    TressleWorks.ca



         Revision History:

                 Version            Date           Comments
                   V3.0        March 5, 2005       Initial Release of Version V3.0 for DNN V3
                  V3.0.1        May 14, 2005       Minor bug fixes and addition of default Top Count, Group By
                                                   clause and Session Variable substitution.
                   V3.1       August 28, 2005      Minor bug fixes and addition of Export to Excel, "Values:" in
                                                   Filters, and AppSetting Connection Strings
                   V3.2     September 17, 2005     Added support for Stored Procedures and selection parameter
                                                   substitution anywhere in select.
                   V3.3       January 1, 2006      Added column formatting and selection parameter substitution
                                                   anywhere in select.
                   V3.4       March 18, 2006       Added Calendars to Date Selection and the ability to used
                                                   Display text and Values in the filtered dropdowns. Also added
                                                   the Multi-Answer and Multi-Select features
                   V4.0      November 1,2006       Complete Overhaul of Modules – many new additions
                   V4.1      September 1, 2007     Final Version for ASP.Net V1.1 – maintenance release



         Acknowledgements:
         I have built this module with help from several people, specifically the members of the DotNetNuke forum
         where I was able to learn so much. However, I did use specific code examples from the AdvancedDataGrid
         from www.efficionsoftware.com and operational features of www.DNNStuff.com SQLView.
         For Version 3.1, I would like to especially acknowledge:
           Steve from Code5Systems.com for the help with the Export to Excel feature
           Ben S for his suggestion to allow AppSetting access from WebConfig for the Connection String
         For Version 3.2 I would like to thank Gregory H. Taylor for his help.
         For Version 3.4 I have used the DateTimePicker popup Calendar developed by TengYong Ng at
         http://www.rainforestnet.com. I have made several minor changes for use with SQLGridSelectedView. I’d
         also like to thank Paul Bennett for the suggestion of Multi-Answer and inspiration of Multi-Select.
         Since I built the software based on work of others, this module is free and can be used on any DNN V3.0 site
         as long as the Standard DotNetNuke licence is followed.
         Comments or questions can be E-mailed to SQLGridSelectedView@TressleWorks.ca.




         TressleWorks.ca                                       51                                   1 September 2007

PDF created with pdfFactory trial version www.pdffactory.com

				
DOCUMENT INFO