sqlweb

• A solution for quickly creating sites as single page applications (all in one), includes reports with filters, create or change forms, menu.

• The framework is primarily intended for those who have a database, but do not have a web interface to it.

• Logic is based on SQL queries and variables.

• The user cannot change the design and layout of the elements, but has full control over the content and data source.

• The video on YouTube shows a simple report creation, where the database is an excel document.

• All logic is written in ASP Classic, design elements are written in pure html/javascript/css. No additional javascrit libraries are used.

Sample Page and Code

Page

Code

case "2"
	
		g_Table_Caption_and_Info = "Yearly statistics by the departments"
		g_Form_Info_Help = ""
		
		g_SQL =         " select a.fiscal_year, a.purch_dept,b.deptname, a.po_count, a.encum_amount, '<a href=''sqlsite.asp?fiscal_year='+a.fiscal_year+'&purch_dept='+a.purch_dept+'&p=3''>...</a>' Info from ("
        g_SQL = g_SQL & " select [fiscal_year],[purch_dept], count([purchase_order]) po_count,sum([enc_amount]) [encum_amount] from ( "
        g_SQL = g_SQL & " select CAST(DATEPART(yyyy, [post_date_orig]) AS varchar(4) ) [fiscal_year],[purch_dept],[purchase_order],sum([encumbered_amount]) [enc_amount]   "
        g_SQL = g_SQL & " from [test_sqlweb_db].[dbo].[purchasing_commodity]  "
        g_SQL = g_SQL & " group by CAST(DATEPART(yyyy, [post_date_orig]) AS varchar(4) ),[purch_dept],[purchase_order] "
        g_SQL = g_SQL & " ) x group by [fiscal_year],[purch_dept] ) a inner join (select * from dbo.depts) b "
        g_SQL = g_SQL & " on a.purch_dept = b.deptcode "
        g_SQL = g_SQL & "  "
        		
		g_FilterDropdownsAllowed = "YES"
		g_FilterDropdownsColumns =  "select '%' as fiscal_year , 'All years' fiscal_year  union  select distinct CAST(DATEPART(yyyy, [post_date_orig]) AS varchar(4) ) fiscal_year,CAST(DATEPART(yyyy, [post_date_orig]) AS varchar(4) ) fiscal_year from [dbo].[purchasing_commodity];" _ 
								  & "select '%' as purch_dept , 'All depts' purch_dept  union  select distinct purch_dept,purch_dept + ' - ' + b.deptname from [dbo].[purchasing_commodity] a inner join (select * from dbo.depts) b on a.purch_dept = b.deptcode;"
			
		g_FilterDatalistsColumns = ""								
		g_FiltersDefaultValues = "select '' fiscal_year,'' purch_dept"
		
		g_TableColumnsSortingAllowed = "YES" 
		g_TableColumnsDefaultSorting = "fiscal_year asc"
        
	    g_TableRowsUpdateAllowed = "NO" : g_TableRowsInsertAllowed  = "NO" : g_TableRowsDeleteAllowed = "NO"
		g_DBTableForInsertUpdate="dbo.purchasing_commodity"
		g_DBTableIdColumn="id"
		g_DBTableFieldsListForInsertUpdate=""
		g_DBTableDropdownsForInsertUpdate = ""
		g_DBTableDatalistsForInsertUpdate = ""
		g_TableUpdateInsertLayoutVerticalHorizontal="V"

Video Tutorial: Simple web page from Excel file

Environment and knowledges to start

• You know what is the database, may write SQL queries and understand tables relations and keys
• Has PC/Server with operating system where Internet Information Server (IIS) may be installed
• Has any database to which you can connect using 32bit driver and connection string listed on connectionstrings.com
• Any code editor with asp/sql syntax highlight

What You get from the box?

• single page application (all reports/forms/filters in one page)
• each page may have it's own database (but you can't mix databases on one page)
• unified style for html elements (5 color schemas - 1 style)
• vulnerability check (URL and forms check)
• navigation menu
• data table
• data filters
• pagination
• totals for numbers
• columns sort
• export to excel
• data operations (add one or multiply records, update/delete one record)
• database names/fields substitution (beautifier)
• debug
• multiinsert (while filind form you select X values from first dropdown and Y from second values and as result get x*y rows generated)

Benefits

• The simpliest page needs only one page variable which is also SQL query
• The hardest page with full possible functionality You may get using 21 page variables (each variable is query/constraint/hybrid)

Limitations

• Only 32bit odbc drivers supported
• All tables must have 1 ID column with autogenerated value (id name can be anything) for ADD/EDIT/DELETE operations.
• Special local symbols not supported by the default

First configuration

Before you start, check if your database has 32-bit drivers. Without them, all further actions are meaningless.

IIS

Install IIS for Windows XP, 2000, 2003,Windows 7,Vista,8,8.1, for Windows Server 2008, 2008 r2, 2012, 2012 r2 and later.
Windows 10.

Open IIS, find default application pool and set parameter "using 32 bit applications" to True.

sqlweb files

Copy sqlsite.asp and global.asa to iis directory c:\inetpub\wwwroot. This is default IIS directory, but you can setup other folders. Current content of application is configured to use one of the available preconfigured databases Excel,Access or create scripts SQLite,Oracle or database backup MSSQL datasource. You can choose one and set up it, then run application.

ODBC

Install 32 bit odbc driver for Your database. Check if it is present in odbc drivers list:

• For 32 bit OS go to Control Panel\All Control Panel Items\Administrative Tools\Data sources ODBC,
• for 64 bit OS run this file C:\WINDOWS\syswow64\odbcad32.exe

Connection string

Try to make test connection to your database using ODBC driver. If test is OK then visit connectionstrings.com and try to find correct string and write it to the global.asa.

Global variables

Open sqlsite.asp and change variables, listed below, to proper values you want in '// --- USER AREA START ----- and '\\ --- USER AREA END -----. Before each variable there is short help attached directly in code.

• g_page_datasource
• g_PortalName
• g_DefaultPageCode
• g_MENU
• g_columns_start_bracket
• g_columns_end_bracket
• g_DateFromTextToSQL
• g_DateTimeFromTextToSQL

Pages

Block of pages starts on '// --- PAGES AREA START ----- construction and ends on '\\ --- PAGES AREA END ----- sqlsite.asp single page application may have unlimited pages count in it. Each page has it's own code. In sample page code above there is case "2". This mean the page code is "2".

Page variables

These variables contain SQL which is DataTable or Filter values, or predefined values YES/NO which Enable/Disable page functionality or blocks, or hybrid which has it's own format as "variablename;sql construction" or "column/s list". Pages variables describe one separate page data and functionality.

• g_Table_Caption_and_Info - Page Name

• g_Form_Info_Help - use this to show additional information about page. This may be description, comments, or columns formats or other. By the default this info is hidden. To show content use main menu info/help item

• g_SQL - SQL query to database on which is based page datatable

• g_FilterDropdownsAllowed - Filter enabled or not (YES/NO). Each filter contain predefined values in dropdown.

• g_FilterDropdownsColumns - Dropdown has id and description. For user we show description, but send to database id value. Example; select '%' as Vendorid,'All vendors' as Vendor from dual union select Vendorid,VendorName as Vendor from Vendors'

• g_FilterDatalistsColumns - Is part of g_FilterDropdownsColumns variable. Default type for filter dropdown is <SELECT>, but you can change it to <DATALIST>. For more info Google it. Example Vendorid

• g_FiltersDefaultValues - Default values for filters to be activated on page first generation for user. Oracle: select '' as Vendorid,'' as Vendor from dual. MSSQL: select '' as Vendorid,'' as Vendor

• g_TableColumnsSortingAllowed - Allow Columns Sorting by clicking on them (YES/NO)

• g_TableColumnsDefaultSorting - Default sorting on datatable view (example: "ColumnName1 ASC, ColumnID2 DESC,datecolumn3 ASC")

• g_TableRowsInsertAllowed - Allow Insert operation on table (YES/NO)

• g_TableRowsUpdateAllowed - Allow Update operation on table (YES/NO)

• g_TableRowsDeleteAllowed - Allow Delete operation on table (YES/NO). Works if g_TableRowsUpdateAllowed = YES

• g_DBTableForInsertUpdate - For Insert/Update/Delete we need real database table name (it may be only one, unique table name)

• g_DBTableIdColumn - Table ID column name. For Update we need real database table id column name (it may be only one, unique column)

• g_DBTableFieldsListForInsertUpdate - List of columns You allow to insert/update. Always exclude id column. It is assumed that the ID column value is autogenerated at the db level always. Example: "artistname,created_on,album_id"

• g_DBTableDropdownsForInsertUpdate - This is replacement of id columns in table to the readable values on insert/update. Example: idcolname1;select idcolname1,idcolname1textvalue from sometable1;idcolname2;select idcolname2,idcolname2textvalue from sometable2;. Read func_GetFilterDropdownsIfExist info for this variable.

• g_DBTableDatalistsForInsertUpdate - Is part of g_DBTableDropdownsForInsertUpdate variable. Example: idcolname1,idcolname2. Place table columns names there with "," separator and while insert/update operation default type for dropdown html <SELECT> will be changed to <DATALIST> which support search in it. Very good for long lists.

• g_DBTableMultipleDropdownsFieldsForInsert - Is part of g_DBTableDropdownsForInsertUpdate variable. Example: idcolname1,idcolname2. List of these values will be repeated N times while inserting rows - Multiinsert. Can be used only on ADD/INSERT operation.

• g_TableUpdateInsertLayoutVerticalHorizontal - For Operations Update and Insert data layout vertical or horisontal (V/H). For tables containing more than 10 columns, may be very useful

• g_AfterInsertScript - script or query will be executed after each insert operation and on multiple inserts also

• g_AfterUpdateScript - script or query will be executed after each update operation

Debug

Errors checking is divided into 2 parts. First part can be accessed from Debug menu item while the application is running. And other errors you can get directly from iis logs in folder C:\inetpub\logs\LogFiles\W3SVC1 or W3SVC2 and so on.