Universal Table Editor

<%
'---------------------------------------------------------------------------
'
'   Project:    UTE - (U)niversal ASP (T)able (E)ditor
'
'   Module:     UTE class
'
'   Version:    2.10
'
'   Comments:   This module does the following things:
'                   1. Defines the class "clsUTE" with all it's 
'                      properties and functions.
'
'---------------------------------------------------------------------------
'
'   (c) in 2000-2002 by Tom Wellige                    
'   http://www.wellige.com  mailto:tom@wellige.com     
'                                               
'   This project is released under the "GNU General Public License (GPL)" 
'   http://www.gnu.org/licenses/gpl.html
'
'   and is maintained on SourceForge at
'   http://sourceforge.net/projects/ute-asp/
'
'   and can also be found on CodeProject at
'   http://www.codeproject.com/asp/ute.asp
'
'---------------------------------------------------------------------------
'
'   Public Properties    (R = read, W = write)
'
'      DBName      R/W  Name of Database. For display purpose only.
'
'      HeadLine    R    Headline of page (e.g. to be used as <title>)
'      TableName   R    Name of current table
'
'      ReadOnly    W    display table in readonly mode              (default: false)
'      ListTables  W    list all tables within DB when opening      (default: true)
'                       ute.asp without parameters
'      Definitions W    display link to show field definitions      (default: true)
'      Export      W    display link to export data                 (default: true)
'
'      ExportFile  W    name of export file (case sensitive)        (default: %t_%d_%s.csv)
'                         %d   YYYY-MM-DD-HH-mm-SS
'                         %t   Tablename
'                         %s   Session ID
'
'      ImageDir    R/W  name of image directory, must end with "/"  (default: images/ )
'      ExportDir   R/W  name of export directory, must end with "/" (default: export/ )
'      
'
'   Public Functions
'      Init    (sDSN)   must be called *before* any other HTML output
'      Draw    ()       writes complete HTML code
'      getHTML ()       returns complete HTML code
'
'---------------------------------------------------------------------------

Class clsUTE

	'-----------------------------------------------------------------------
	' Private Member Variables
	'
	Private m_DB					' database connection object
	Private	m_RS					' recordset object
	Private	m_RSForm				' recordset object for form view

	Private m_nMode					' View mode: mdTable, mdForm, mdExport
	Private m_nFormMode				' Form mode: mdEdit, mdInsert, mdDelete

	Private m_sDSN					' odbc connect string
	Private m_sDBName               ' database name (for display purpose only)
	Private m_sTable				' table name
	Private m_nPage					' current page
	Private m_nPageSize				' size of current page
	Private m_bSortFields			' sort fields alphabetically (columns) ?
	Private m_bViewDefinitions		' show field definitions ?
	Private m_bAutoPKDetection		' detect primary key fields ?
	Private m_bSubmitted            ' form was submitted

	Private m_bReadOnly				' display table in readonly mode
	Private m_bListTables           ' list all tables within DB
	Private m_bShowDefLink			' display link to show field definitions
	Private m_bShowExportLink		' display link to export data

	Private m_PrimaryKeyFields()	' array holding names of all primary key fields
	Private m_StandardFields()		' array holding names of all other fields

	Private m_SortFields()			' array holding names of the fields for the ORDER BY clause
	Private m_SortFieldsOrder()		' array holding the directions for the ORDER BY clause

	Private m_sHeadLine             ' Headline

	Private m_sUTEScript			' name of UTE script file
	Private m_sIMAGEDir				' name of image directory
	Private m_sEXPORTDir			' name of export directory

	Private m_sExportFile           ' name mask of export file

	Private m_nRecord               ' number of record to be edited/deleted
	Private m_ErrorField()
	Private m_ErrorMessage()


	'-----------------------------------------------------------------------
	' Property Functions
	'

	' ---- HeadLine (read) ----
	Property Get HeadLine()
		HeadLine = m_sHeadLine
	End Property

	' ---- DBName (read/write) ----
	Property Let DBName(s)
		m_sDBName = s
	End Property

	Property Get DBName()
		DBName = m_sDBName
	End Property

	' ---- TableName (read) ----
	Property Get TableName()
		TableName = m_sTable
	End Property

	' ---- ReadOnly (write) ----
	Property Let ReadOnly(b)
		m_bReadOnly = b
	End Property

	' ---- ListTables (write) ----
	Property Let ListTables(b)
		m_bListTables = b
	End Property

	' ---- Definitions (write) ----
	Property Let Definitions(b)
		m_bShowDefLink = b
	End Property

	' ---- Export (write) ----
	Property Let Export(b)
		m_bShowExportLink = b
	End Property

	' ---- ExportFile (write) ----
	Property Let ExportFile(s)
		m_sExportFile = s
	End Property

	Property Get UTEScript()
		UTEScript = m_sUTEScript
	End Property

	' ---- ImageDir (read/write)----
	Property Let ImageDir(s)
		m_sIMAGEDir = s
	End Property

	Property Get ImageDir()
		ImageDir = m_sIMAGEDir
	End Property

	' ---- ExportDir (read/write) ----
	Property Let ExportDir(s)
		m_sEXPORTDir = s
	End Property

	Property Get ExportDir()
		ExportDir = m_sEXPORTDir
	End Property



	'-----------------------------------------------------------------------
	' Private Member Functions
	'

    ''----------------------------------------------------------------------
    '' Name:     Class_Initialize
    ''           ================
    '' 
    '' Constructor.
	''
    ''----------------------------------------------------------------------
	Private Sub Class_Initialize()

		Set m_DB           = Server.CreateObject("ADODB.Connection")
		Set m_RS           = Server.CreateObject("ADODB.Recordset")
		
		m_nMode            = DEF_MODE
		m_nFormMode        = DEF_FORM_MODE

		m_sDSN             = ""
		m_sDBName          = ""
		m_sTable           = ""
		m_nPage            = DEF_PAGE
		m_nPageSize        = DEF_PAGE_SIZE
		m_bSortFields      = DEF_SORT_FIELDS
		m_bViewDefinitions = DEF_VIEW_DEFINITIONS
		m_bAutoPKDetection = DEF_PK_DETECTION
		m_bSubmitted       = False

		m_bReadOnly        = DEF_READONLY 
		m_bListTables      = DEF_LIST_TABLES
		m_bShowDefLink     = DEF_SHOW_DEF_LINK
		m_bShowExportLink  = DEF_EXPORT_LINK

		m_sExportFile      = DEF_EXPORT_FILE

		Redim m_PrimaryKeyFields(0)
		Redim m_StandardFields(0)

		Redim m_SortFields(0)
		Redim m_SortFieldsOrder(0)

		m_sUTEScript       = Request.ServerVariables("SCRIPT_NAME")
		m_sIMAGEDir	       = DEF_IMAGE_DIR
		m_sEXPORTDir       = DEF_EXPORT_DIR

		Redim m_ErrorField(0)
		Redim m_ErrorMessage(0)

	End Sub

    ''----------------------------------------------------------------------
    '' Name:     Class_Terminate
    ''           ===============
    '' 
    '' Destructor.
    ''
    ''----------------------------------------------------------------------
	Private Sub Class_Terminate()
		Redim m_PrimaryKeyFields(0)
		Redim m_StandardFields(0)
		Set m_RS = Nothing
		Set m_DB = Nothing
		if IsObject(m_RSForm) then
			Set m_RSForm = Nothing
		end if
	End Sub


	''----------------------------------------------------------------------
    '' Name:     SetURLParameter
    ''           ===============
    '' 
    '' Sets a given parameter to a URL parameter string. If the parameter is
	'' already present in the URL string it will be updated, otherwise it will
	'' simply be added.
	''
	'' Parameter: 
	''		sURL		URL string to set/update the parameter in
	''		sName		name of parameter to be set
	''		sValue		value of parameter to be set
	''
	'' return value:
	''		string		new URL parameter string
	''
    ''----------------------------------------------------------------------
	Private Function SetURLParameter (sURL, sName, sValue)
		Dim iPos
		Dim sLink, sReturn, sLeft, sRight

		sLink = sURL
		sReturn = ""

		if InStr(sLink, sName) <> 0 then
			' update exisiting parameter
			iPos = InStr(sLink, sName)
			sLeft = Left(sLink, iPos+Len(sName))

			sRight = Right(sLink, Len(sLink) - (iPos + Len(sName)))
			if InStr(sRight, "&") <> 0 then
				' at least one following parameter
				iPos = InStr(sRight, "&")
				sRight = Right(sRight, Len(sRight)-iPos+1)

				sReturn = sReturn & sLeft & sValue & sRight
			else
				' no following parameter
				sReturn = sReturn & sLeft & sValue
			end if
		else
			' add parameter
			if Len(sLink) <> 0 then
				sReturn = sReturn & sLink & "&" & sName & "=" & sValue
			else
				sReturn = sReturn & sName & "=" & sValue
			end if
		end if

		SetURLParameter = sReturn
	End Function


    ''----------------------------------------------------------------------
    '' Name:     buildLink
    ''           =========
    '' 
    '' Returns a string containing all UTE relevant URL parameters, such
	'' as tablename, page or primary key fields.
	''
	'' Parameter: 
	''		sCurrent	current URL string
	''
	'' return value:
	''		string		link
	''
    ''----------------------------------------------------------------------
	Private Function buildLink (sCurrent)
		Dim i
		Dim sReturn, sDefs, sSort, sSubmitted

		' preserve current URL string
		sReturn = sCurrent

		sDefs = "0"
		if m_bViewDefinitions then sDefs = "1"
		sSort = "0"
		if m_bSortFields then sSort = "1"
		sSubmitted = "0"
		if m_bSubmitted then sSubmitted = "1"

		' set all UTE URL params
		sReturn = SetURLParameter(sReturn, sParamTable,      m_sTable)
		sReturn = SetURLParameter(sReturn, sParamMode,       CStr(m_nMode))
		sReturn = SetURLParameter(sReturn, sParamFormMode,   CStr(m_nFormMode))
		sReturn = SetURLParameter(sReturn, sParamPage,       CStr(m_nPage))
		sReturn = SetURLParameter(sReturn, sParamPageSize,   CStr(m_nPageSize))
		sReturn = SetURLParameter(sReturn, sParamDefs,       sDefs)
		sReturn = SetURLParameter(sReturn, sParamSortFields, sSort)
		sReturn = SetURLParameter(sReturn, sParamSubmitted,  sSubmitted)
		sReturn = SetURLParameter(sReturn, sParamRecord,     CStr(m_nRecord))

		' add primary key fields
		for i = 1 to UBound(m_PrimaryKeyFields)
			sReturn = SetURLParameter(sReturn, sParamPKey & CStr(i), m_PrimaryKeyFields(i))
		next

		' add sort fields
		for i = 1 to UBound(m_SortFields)
			sReturn = SetURLParameter(sReturn, sParamSort & CStr(i),    m_SortFields(i))
			sReturn = SetURLParameter(sReturn, sParamSortDir & CStr(i), m_SortFieldsOrder(i))
		next

		buildLink = sReturn

	End Function


    ''----------------------------------------------------------------------
    '' Name:     GetLink
    ''           =======
    '' 
    '' Adds the given parameter to a compelte UTE link. An UTE link includes
	'' all possible URL parameters and is used to switch table pages or open
	'' the record form.
	''
	'' Parameter: 
	''		sScript		name of script to be called, e.g. ute.asp
	''		sCurrent    current link, if "" the function build a complete new link
	''		sParam		name of parameter to be set
	''		sValue		value to be set
	''
	'' return value:
	''		string		complete link
	''
    ''----------------------------------------------------------------------
	Private Function GetLink ( sScript, sCurrent, sParam, sValue )

		Dim iPos
		Dim sReturn, sLeft, sRight

		' check if we already have a complete UTE URL string ?
		if InStr(sCurrent, sParamMode) <> 0 then
			' use current link
			iPos = InStr(sCurrent, "?")
			sReturn = "&" & Right(sCurrent, Len(sCurrent)-iPos)
		else
			' build new link
			sReturn = "&" & buildLink(sCurrent)
		end if

		sReturn = SetURLParameter(sReturn, sParam, sValue)

		' add script name, repleace leading "&" by "?"
		sReturn = sScript & "?" & Right(sReturn, Len(sReturn)-1)

		GetLink = sReturn
	End Function

	
    ''----------------------------------------------------------------------
    '' Name:     RemoveCountedParameters
    ''           =======================
    '' 
    '' Removes so called "counted parameters" like "pkey[n]" or "sort[n]"
	'' from the given link. The start counter defines the start value for [n].
	'' E.g.: sParam = sort, nStartCounter = 2
	'' -> removes all sort2, sort3, sort4, ... from the link
	''
	'' Parameter: 
	''		sLink		string containing the link
	''		sParam		parameter name
	''		nStarCount  start counter
	''
	'' return value:
	''		string
	''
    ''----------------------------------------------------------------------
	Private Function RemoveCountedParameters ( sLink, sParam, nStartCount )
		Dim sLeft, sRight, sReturn
		Dim n, iPos
		n = nStartCount
		sReturn = sLink

		while InStr(sReturn, sParam & CStr(n)) <> 0
			iPos   = InStr(sReturn, sParam & CStr(n))
			sLeft  = Left(sReturn, iPos-1)
			iPos   = InStr(iPos, sReturn, "&")
			sRight = ""
			if iPos <> 0 then sRight = Right(sReturn, Len(sReturn)-iPos)
			sReturn = sLeft & sRight
			n = n + 1
		wend

		if Right(sReturn, 1) = "&" then sReturn = Left(sReturn, Len(sReturn)-1)

		RemoveCountedParameters = sReturn
	End Function

	
    ''----------------------------------------------------------------------
    '' Name:     GetParameter
    ''           ============
    '' 
    '' Gets all parameters from URL and throw excaption if neccessary.
	''
	'' Parameter: 
	''		none
	''
	'' return value:
	''		none
	''
    ''----------------------------------------------------------------------
	Private Sub GetParameter()

		Dim i
		Dim sTemp
		Dim sError

		' ---- ODBC connect string ----
		if m_sDSN = "" then
			'"Invalid ODBC Connection String"
			sError = STR_ERR_1001 
			err.Raise vbObjectError + 1001, "ute_table", sError
		end if

		' ---- Tablename ----
		if Request.QueryString(sParamTable) <> "" then
			m_sTable = Request.QueryString(sParamTable)
		else
			m_nMode = MD_DATABASE
		end if


		' ---- Tablename ----
		m_sTable = Request.QueryString(sParamTable)
		if m_bListTables then
			' if no table set, display all tables within database
			if Request.QueryString(sParamTable) = "" then
				m_nMode = MD_DATABASE
			end if
		else
			' if no table set throw error
			if Request.QueryString(sParamTable) = "" then
				'"Missing ""%1"" URL parameter."
				sError = Replace(STR_ERR_1002, "%1", sParamTable) 
				err.Raise vbObjectError + 1002, "ute_table", sError
			end if
		end if

		' ---- Mode ----
		if Request.QueryString(sParamMode) <> "" then
			sTemp = Request.QueryString(sParamMode)
			if not IsNumeric(sTemp) then
				'"Invalid ""%1"" URL parameter. Must be numeric."
				sError = Replace(STR_ERR_1003, "%1", sParamMode) 
				err.Raise vbObjectError + 1003, "ute_table", sError
			end if
			m_nMode = CInt(sTemp)
			if (m_nMode < MD_DATABASE) or (m_nMode > MD_EXPORT) then
				'"Invalid ""%1"" URL parameter. Must be ""1"", ""2"" or ""3""."
				sError = Replace(STR_ERR_1004, "%1", sParamMode)
				err.Raise vbObjectError + 1004, "ute_table", sError
			end if
		end if

		' ---- FormMode ----
		if Request.QueryString(sParamFormMode) <> "" then
			sTemp = Request.QueryString(sParamFormMode)
			if not IsNumeric(sTemp) then
				'"Invalid ""%1"" URL parameter. Must be numeric."
				sError = Replace(STR_ERR_1003, "%1", sParamFormMode) 
				err.Raise vbObjectError + 1003, "ute_table", sError
			end if
			m_nFormMode = CInt(sTemp)
			if (m_nFormMode < 1) or (m_nFormMode > 3) then
				'"Invalid ""%1"" URL parameter. Must be ""1"", ""2"" or ""3""."
				sError = Replace(STR_ERR_1004, "%1", sParamFormMode)
				err.Raise vbObjectError + 1004, "ute_table", sError
			end if
		end if

		' ---- Page ----
		if Request.QueryString(sParamPage) <> "" then
			sTemp = Request.QueryString(sParamPage)
			if not IsNumeric(sTemp) then
				'"Invalid ""%1"" URL parameter. Must be numeric."
				sError = Replace(STR_ERR_1003, "%1", sParamPage) 
				err.Raise vbObjectError + 1003, "ute_table", sError
			end if
			m_nPage = CInt(sTemp)
		end if

		' ---- Page Size ----
		if Request.QueryString(sParamPageSize) <> "" then
			sTemp = Request.QueryString(sParamPageSize)
			if not IsNumeric(sTemp) then
				'"Invalid ""%1"" URL parameter. Must be numeric."
				sError = Replace(STR_ERR_1003, "%1", sParamPageSize) 
				err.Raise vbObjectError + 1003, "ute_table", sError
			end if
			m_nPageSize = CInt(sTemp)
		end if

		' ---- Sort Fields Alphabetically ----
		if Request.QueryString(sParamSortFields) <> "" then
			sTemp = Request.QueryString(sParamSortFields)
			if (sTemp <> "0") and (sTemp <> "1") then
				'"Invalid ""%1"" URL parameter. Must be either ""%2"" or ""%3""."
				sError = Replace(STR_ERR_1005, "%1", sParamSortDir) 
				sError = Replace(sError, "%2", "0") 
				sError = Replace(sError, "%3", "1")
				err.Raise vbObjectError + 1005, "ute_table", sError
			end if
			m_bSortFields = (sTemp = "1")
		end if

		' ---- View Field Definitions ----
		if Request.QueryString(sParamDefs) <> "" then
			sTemp = Request.QueryString(sParamDefs)
			if (sTemp <> "0") and (sTemp <> "1") then
				'"Invalid ""%1"" URL parameter. Must be either ""%2"" or ""%3""."
				sError = Replace(STR_ERR_1005, "%1", sParamDefs) 
				sError = Replace(sError, "%2", "0") 
				sError = Replace(sError, "%3", "1")
				err.Raise vbObjectError + 1005, "ute_table", sError
			end if
			m_bViewDefinitions = (sTemp = "1")
		end if

		' ---- Submitted ----
		if Request.QueryString(sParamSubmitted) <> "" then
			sTemp = Request.QueryString(sParamSubmitted)
			if (sTemp <> "0") and (sTemp <> "1") then
				'"Invalid ""%1"" URL parameter. Must be either ""%2"" or ""%3""."
				sError = Replace(STR_ERR_1005, "%1", sParamSubmitted) 
				sError = Replace(sError, "%2", "0") 
				sError = Replace(sError, "%3", "1")
				err.Raise vbObjectError + 1005, "ute_table", sError
			end if
			m_bSubmitted = (sTemp = "1")
		end if

		' ---- Record ----
		if Request.QueryString(sParamRecord) <> "" then
			sTemp = Request.QueryString(sParamRecord)
			if not IsNumeric(sTemp) then
				'"Invalid ""%1"" URL parameter. Must be numeric."
				sError = Replace(STR_ERR_1003, "%1", sParamRecord) 
				err.Raise vbObjectError + 1003, "ute_table", sError
			end if
			m_nRecord = CInt(sTemp)
		end if


		' ---- Primary Keys ----
		i = 1
		while Request.QueryString(sParamPKey & CStr(i)) <> ""
			' switch off auto primary key detection 
			m_bAutoPKDetection = False
			AddPrimaryKeyField Request.QueryString(sParamPKey & CStr(i))
			i = i + 1
		wend


		' ---- Sort Field ----
		' This can be either "sort" (for compatebility purpose) or "sort[n]"
		if Request.QueryString(sParamSort) <> "" then
			AddSortField Request.QueryString(sParamSort)
			if Request.QueryString(sParamSortDir) <> "" then
				sTemp = LCase(Request.QueryString(sParamSortDir))
				if (sTemp <> SORT_ASC) and (sTemp <> SORT_DESC) then
					'"Invalid ""%1"" URL parameter. Must be either ""%2"" or ""%3""."
					sError = Replace(STR_ERR_1005, "%1", sParamSortDir) 
					sError = Replace(sError, "%2", SORT_ASC) 
					sError = Replace(sError, "%3", SORT_DESC)
					err.Raise vbObjectError + 1005, "ute_table", sError
				end if
				AddSortOrder sTemp
			else
				' default sort order
				AddSortOrder DEF_SORT_DIR
			end if
		else
			i = 1
			while Request.QueryString(sParamSort & CStr(i)) <> ""
				AddSortField Request.QueryString(sParamSort & CStr(i))

				if Request.QueryString(sParamSortDir & CStr(i)) <> "" then
					sTemp = LCase(Request.QueryString(sParamSortDir & CStr(i)))
					if (sTemp <> SORT_ASC) and (sTemp <> SORT_DESC) then
						'"Invalid ""%1"" URL parameter. Must be either ""%2"" or ""%3""."
						sError = Replace(STR_ERR_1005, "%1", sParamSortDir & CStr(i)) 
						sError = Replace(sError, "%2", SORT_ASC) 
						sError = Replace(sError, "%3", SORT_DESC)
						err.Raise vbObjectError + 1005, "ute_table", sError
					end if
					AddSortOrder sTemp
				else
					' default sort order
					AddSortOrder DEF_SORT_DIR
				end if

				i = i + 1
			wend
		end if 

	End Sub


    ''----------------------------------------------------------------------
	'' Name:     AddPrimaryKeyField
	''           ==================
	'' 
	'' Add's a primary key field to the array
	''
	'' Parameter:                
	''		sField		name of field
	''
	'' return value:
	''		none
	''
    ''----------------------------------------------------------------------
	Private Sub AddPrimaryKeyField (sField)
		Redim Preserve m_PrimaryKeyFields(UBound(m_PrimaryKeyFields)+1)
		m_PrimaryKeyFields(UBound(m_PrimaryKeyFields)) = sField
	End Sub


    ''----------------------------------------------------------------------
	'' Name:     AddStandardField
	''           ================
	'' 
	'' Add's a "standard" field to the array
	''
	'' Parameter: 
	''		sField		name of field
	''
	'' return value:
	''		none
	''
    ''----------------------------------------------------------------------
	Private Sub AddStandardField (sField)
		Redim Preserve m_StandardFields(UBound(m_StandardFields)+1)
		m_StandardFields(UBound(m_StandardFields)) = sField
	End Sub


    ''----------------------------------------------------------------------
	'' Name:     AddSortField
	''           ============
	'' 
	'' Add's a field the table should be sorted after to the array
	''
	'' Parameter:                
	''		sField		name of field
	''
	'' return value:
	''		none
	''
    ''----------------------------------------------------------------------
	Private Sub AddSortField (sField)
		Redim Preserve m_SortFields(UBound(m_SortFields)+1)
		m_SortFields(UBound(m_SortFields)) = sField
	End Sub


    ''----------------------------------------------------------------------
	'' Name:     AddSortOrder
	''           ============
	'' 
	'' Add's the sort order of a field to the array
	''
	'' Parameter:                
	''		sField		name of field
	''
	'' return value:
	''		none
	''
    ''----------------------------------------------------------------------
	Private Sub AddSortOrder (sOrder)
		Redim Preserve m_SortFieldsOrder(UBound(m_SortFieldsOrder)+1)
		m_SortFieldsOrder(UBound(m_SortFieldsOrder)) = sOrder
	End Sub


	''----------------------------------------------------------------------
	'' Name:     IsKnownPrimaryKey
	''           =================
	'' 
	'' Checks if the given field is already known as primary key
	''
	'' Parameter: 
	''		sField		name of field
	''
	'' return value:
	''		boolean
	''
    ''----------------------------------------------------------------------
	Private Function IsKnownPrimaryKey ( sField ) 
		Dim i
		Dim bReturn
		bReturn = False
		for i = 1 to UBound(m_PrimaryKeyFields)
			if m_PrimaryKeyFields(i) = sField then bReturn = True
		next
		IsKnownPrimaryKey = bReturn
	End Function


    ''----------------------------------------------------------------------
	'' Name:     IsPrimaryKey_inDBSchema
	''           =======================
	'' 
	'' Checks if the given field is defined in db schema
	''
	'' Parameter: 
	''		sField		name of field
	''
	'' return value:
	''		boolean
	''
    ''----------------------------------------------------------------------
	Private Function IsPrimaryKey_inDBSchema ( sField )
		Dim bReturn
		bReturn = False

		Dim rsSchema
		Set rsSchema = Server.CreateObject("ADODB.Recordset")
		rsSchema.CursorType = adOpenDynamic
		Set rsSchema = m_DB.openSchema(adSchemaIndexes)

		do while (not rsSchema.EOF) and (not bReturn)
			if LCase(rsSchema("TABLE_NAME")) = LCase(m_sTable) then
				if LCase(rsSchema("COLUMN_NAME")) = LCase(sField) then
					if rsSchema("PRIMARY_KEY") then
						bReturn = True
					end if
				end if
			end if
			rsSchema.MoveNext
		loop
	  
		rsSchema.Close
		Set rsSchema = Nothing

		IsPrimaryKey_inDBSchema = bReturn
	End Function

	
    ''----------------------------------------------------------------------
	'' Name:     SortFields
	''           ==========
	'' 
	'' Sort given array ascending
	''
	'' Parameter:
	''		arr			array including string to be sorted
	''
	'' return value:
	''		none
	''
    ''----------------------------------------------------------------------
	Private Sub SortFields ( ByRef arr )
		Dim pa
		Dim pb
		Dim temp
		' standard bubble sort
		for pa = 1 to UBound(arr) - 1
			for pb = 1 to UBound(arr) - pa
				if arr(pb) > arr(pb + 1) then 
					temp = arr(pb)
					arr(pb) = arr(pb + 1)
					arr(pb + 1) = temp
				end if
			next
		next
	End Sub


    ''----------------------------------------------------------------------
    '' Name:     AnalyzeTable
    ''           ============
    '' 
    '' Analyzing Table for Primary Key Fields and "normal" Fields.
	''
	'' Parameter: 
	''		none
	''
	'' return value:
	''		none
	''
    ''----------------------------------------------------------------------
	Private Sub AnalyzeTable()
		Dim fld
		Dim rsTemp
		Set rsTemp = Server.CreateObject("ADODB.Recordset")
		rsTemp.Open "[" & m_sTable & "]", m_DB, adOpenStatic, adLockReadOnly, adCmdTable

		for each fld in rsTemp.fields
			if m_bAutoPKDetection then
				' (KeyColumn) OR (Fixed and ((not Updateable) and not UnknownUpdateable))
				if ((fld.attributes and adFldKeyColumn) = adFldKeyColumn) or _
					(((fld.attributes and adFldFixed) = adFldFixed) and _
					((fld.attributes and adFldUpdatable) <> adFldUpdatable) and ((fld.attributes and adFldUnknownUpdatable) <> adFldUnknownUpdatable)) then
					if Not(IsKnownPrimaryKey(fld.name)) then 
						AddPrimaryKeyField fld.name
					end if
				else
					if IsPrimaryKey_inDBSchema(fld.name) then
						if Not(IsKnownPrimaryKey(fld.name)) then 
							AddPrimaryKeyField fld.name
						end if
					else
						if Not(IsKnownPrimaryKey(fld.name)) then 
							AddStandardField fld.name
						end if
					end if
				end if
			else
				if Not(IsKnownPrimaryKey(fld.name)) then 
					AddStandardField fld.name
				end if
			end if
		next

		rsTemp.Close
		Set rsTemp = Nothing

		' sort fields (ascending) in array if wanted
		if m_bSortFields then
			SortFields m_PrimaryKeyFields
			SortFields m_StandardFields
		end if

	End Sub


    ''----------------------------------------------------------------------
	'' Name:     getPoweredBy
	''           ============
	'' 
	'' Returns HTML code for "powered by UTE"
	''
	'' Parameter: 
	''		none
	''
	'' return value:
	''		string
	''
    ''----------------------------------------------------------------------
	Private Function getPoweredBy ()
		Dim sReturn
		sReturn = "<a href=""" & sUTELink & """ target=""_blank"" class=""ute_link"">" & sUTELongName & "</a>"
		sReturn = Replace(STR_POWERED_BY, "%1", sReturn)
		sReturn = Replace(sReturn,        "%2", sUTEVersion)
		getPoweredBy = "<div class=""ute_powered_by"">" & sReturn & "</div>"
	End Function


    ''----------------------------------------------------------------------
	'' Name:     getAllRecordsFromDB
	''           ===================
	'' 
	'' Creates SQL statement to get all records from table, opens
	'' and configures recordset.
	''
	'' Parameter: 
	''		none
	''
	'' return value:
	''		none
	''
    ''----------------------------------------------------------------------
	Private Sub getAllRecordsFromDB ()
		Dim i
		Dim s
		Dim sSQL
	
		sSQL = "SELECT * FROM " & m_sTable

		' if no sort field is given select one
		if UBound(m_SortFields) = 0 then
			if UBound(m_PrimaryKeyFields) <> 0 then 
				AddSortField m_PrimaryKeyFields(1) 
				AddSortOrder SORT_ASC
			elseif UBound(m_StandardFields) <> 0 then 
				AddSortField m_StandardFields(1)
				AddSortOrder SORT_ASC
			end if
		end if

		' add sort fields
		if UBound(m_SortFields) <> 0 then
			s = " ORDER BY "
			for i = 1 to UBound(m_SortFields)
				s = s & m_SortFields(i)
				if m_SortFieldsOrder(i) = SORT_DESC then s = s & " DESC"
				s = s & ", "
			next
			' cut trailing ", "
			s = Left(s, Len(s)-2)
			sSQL = sSQL & s
		end if 

		'response.write sSQL
		'response.end

		m_RS.Open sSQL, m_DB, adOpenStatic

		m_RS.PageSize  = m_nPageSize
		m_RS.CacheSize = m_nPageSize

		if m_nPage > m_RS.PageCount then
			m_nPage = m_RS.PageCount
		end if

		if m_nPage <> 0 then
			m_RS.AbsolutePage = m_nPage
		end if

	End Sub


	'-----------------------------------------------------------------------
	' Inlcude all mode specific private class functions
	'
%>
<!--#include file ="ute_class_database.inc"-->
<!--#include file ="ute_class_table.inc"-->
<!--#include file ="ute_class_form.inc"-->
<!--#include file ="ute_class_export.inc"-->
<%

	'-----------------------------------------------------------------------
	' Public Member Functions
	'

    ''----------------------------------------------------------------------
    '' Name:     Init
    ''           ====
    '' 
    '' Read all paramters, analyze table and prepares HTML output.
	''
	'' Parameter: 
	''		sDSN		ODBC connection string
	''		bReadOnly	Display table in readonly mode
	''
	'' return value:
	''		none
	''
    ''----------------------------------------------------------------------
	Public Sub Init( sDSN )

		m_sDSN = sDSN

		' read all other parameters from URL
		GetParameter()

		' calculate headline 
		select case m_nMode
			case MD_TABLE
				m_sHeadLine = m_sTable
			case MD_FORM
				select case m_nFormMode
					case MD_INSERT
						m_sHeadLine = STR_INSERT
					case MD_EDIT
						m_sHeadLine = STR_EDIT
					case MD_DELETE
						m_sHeadLine = STR_DELETE
				end select
		end select
		
		' open database connection
		m_DB.Open m_sDSN

		if m_nMode <> MD_DATABASE then
			' get all fields from table
			AnalyzeTable

			' load all records from db
			getAllRecordsFromDB

			if m_nMode = MD_EXPORT then	
				' create csv file and redirect to it
				ExportToFile
			end if

			if m_nMode = MD_FORM then
				' update record and redirect to table
				Update
			end if
		end if

	End Sub


    ''----------------------------------------------------------------------
    '' Name:     Draw
    ''           ====
    '' 
    '' Writes entire HTML code directly to stream.
	''
	'' Parameter: 
	''		none
	''
	'' return value:
	''		none
	''
    ''----------------------------------------------------------------------
	Public Sub Draw()
		select case m_nMode
			case MD_DATABASE
				Response.Write buildHTML_Database
			case MD_TABLE
				Response.Write buildHTML_Table
			case MD_FORM
				Response.Write buildHTML_Form
		end select
	End Sub


    ''----------------------------------------------------------------------
    '' Name:     getHTML
    ''           =======
    '' 
    '' Returns entire HTML code as string.
	''
	'' Parameter: 
	''		none
	''
	'' return value:
	''		string		entire UTE HTML code
	''
    ''----------------------------------------------------------------------
	Public Function getHTML()
		select case m_nMode
			case MD_DATABASE
				getHTML = buildHTML_Database
			case MD_TABLE
				getHTML = buildHTML_Table
			case MD_FORM
				getHTML = buildHTML_Form
		end select
	End Function


End Class

%>