<% '============================================================== ' TableEditoR 0.81 Beta ' http://www.2enetworx.com/dev/projects/tableeditor.asp '-------------------------------------------------------------- ' File: te_showrecord.asp ' Description: Displays the selected record for editing ' Initiated By Hakan Eskici on Nov 07, 2000 '-------------------------------------------------------------- ' Copyright (c) 2002, 2eNetWorX/dev. ' ' TableEditoR is distributed with General Public License. ' Any derivatives of this software must remain OpenSource and ' must be distributed at no charge. ' (See license.txt for additional information) ' ' See Credits.txt for the list of contributors. ' ' Change Log: '-------------------------------------------------------------- ' # Nov 16, 2000 by Kevin Yochum ' Added null value checks depending on the config switches ' # Nov 23, 2000 by Hakan Eskici ' Added replacing HTML Content ' # Nov 29, 2000 by Hakan Eskici ' Changed displaying boolean field which may cause incorrect ' checkbox view when HTMLEncoding is True ' # Feb 17, 2001 by Danival A. Souza ' Added Foreign Key Support ' # Mar 26, 2001 by Hakan Eskici ' Added support for multiple primary keys ' Changed request()'s to use form or querystring for fixing the ' unicode form submission bug in NT4 systems ' # Aug 16, 2001 by Brad Orgill ' Added check from non-auto incrament primary keys and forigne keys ' # Nov 14, 2001 by Jeff Wilkinson (jwilkinson@mail.com) ' security fix entered per Dilyias suggested fix 10/29/01 ' prevents nonadmin users from accessing the admin db (conn=0) ' # Nov 27, 2001 by Jeff Wilkinson (jwilkinson@mail.com ' added in some vbcrlf's and 's to make the html cleaner ' # April 20, 2002 by Rami Kattan ' Autoincrement can be ignored ' Option to autofill date fields ' # May 30, 2002 by Rami Kattan ' Security check updates, to allow adding/editing records ' Fixed Query record updates '============================================================== %> <% if len(request.querystring("q"))>0 then bQuery = True else bQuery = False iPage = request.querystring("ipage") sQuery = request.querystring("q") %>
<% if bPopUps then %> <% =arrDesc(Cint(request.querystring("cid"))) %> » Table [<% =request.querystring("tablename") %>] » <% if request.querystring("add") then response.write "Add" else response.write "Edit" %> Record <% else %> Home » Connections » "><% =arrDesc(Cint(request.querystring("cid"))) %> » &tablename=<% =server.urlencode(request.querystring("tablename")) %>&ipage=<% =request.querystring("ipage") %><% if bQuery then response.write("&q=" & request.querystring("q")) %>">Table [<%=request.querystring("tablename")%>] » <% if request.querystring("add") then response.write "Add" else response.write "Edit"%> Record <% if bProtected then response.write session("teFullName") response.write " (logout)" end if %> <% end if %>

<% 'disallow connections to admin table for nonadmin users 11/14/01 lConnID = request("cid") if (lConnID = 0) and bAdmin = False then response.write "Not authorized to view this connection." %><% response.end end if sTableName = trim(request.querystring("tablename")) sFieldNames = request.querystring("fld") sFieldValues = request.querystring("val") sFieldTypes = request.querystring("fldtype") if request.querystring("add") <> "" and request.querystring("cmdsave") <> "1" and not bRecAdd then response.write "

You have no pemissions to add records.

" %> <% response.end end if if request.querystring("add") then bAdd = True else bAdd = False sParentName = server.urlencode(sTableName) TableNameIsQuery = instr(1, ucase(sTableName),"SELECT") > 0 OpenRS arrConn(lConnID) ' the following if check if adding or editing, if editing it will build the where sql statement if not bAdd then 'Added by Hakan 'Support for multiple primary keys aFieldNames = split(sFieldNames, ";") aFieldTypes = split(sFieldTypes, ";") aFieldValues = split(sFieldValues, ";") select case arrType(lConnID) case tedbSQLServer sDateSeperator = "'" case else sDateSeperator = "#" end select for iFld = 0 to ubound(aFieldNames) sFieldName = aFieldNames(iFld) lFieldType = CLng(aFieldTypes(iFld)) sFieldValue = aFieldValues(iFld) select case lFieldType case adDate, adDBDate, adDBTime, adDBTimeStamp if isDate(sFieldValue) then sFieldValue = cDate(sFieldValue) sFieldValue = month(sFieldValue) & "/" & day(sFieldValue) & "/" & year(sFieldValue) end if if sWhereFields = "" then sWhereFields = "([" & sFieldName & "]=" & sDateSeperator & sFieldValue & sDateSeperator & ")" else sWhereFields = sWhereFields & " AND ([" & sFieldName & "]=" & sDateSeperator & sFieldValue & sDateSeperator & ")" end if case adTinyInt, adSmallInt, adInteger, adBigInt, adUnsignedTinyInt, adUnsignedSmallInt, adUnsignedInt, adUnsignedBigInt, adSingle, adDouble, adCurrency, adDecimal, adNumeric, adBoolean 'Added by Hakan 'Convert decimal point to dot if it's a comma sFieldValue = replace(sFieldValue, ",", ".") if sWhereFields = "" then sWhereFields = "([" & sFieldName & "]=" & sFieldValue & ")" else sWhereFields = sWhereFields & " AND ([" & sFieldName & "]=" & sFieldValue & ")" end if case else 'Added by Hakan 'Prepare SQL value by replacing single quote with two single quotes sFieldValue = replace(sFieldValue, "'", "''") if sWhereFields = "" then sWhereFields = "([" & sFieldName & "]='" & sFieldValue & "')" else sWhereFields = sWhereFields & " AND ([" & sFieldName & "]='" & sFieldValue & "')" end if end select next sWhere = " WHERE " & sWhereFields else sWhere = "" end if iPlace = instr(1, ucase(sTableName), " WHERE ", 1) if iPlace and TableNameIsQuery then sTableName = left(sTableName, iPlace) end if 'Added by Danival if TableNameIsQuery then if right(sTableName, 1) = ";" then sTableName = left(sTableName, len(sTableName)-1) sSQL = sTableName & sWhere else sSQL = "SELECT * FROM [" & sTableName & "]" & sWhere end if if te_debug then response.write sSQL & "
" rs.Open sSQL, , , adCmdTable 'Added By Brad Orgill 'Reads FK's and PK's into an array if arrType(lConnID) <> tedbDsn then Set rs3 = conn.OpenSchema(adSchemaForeignKeys) Dim fkeyary() Dim numRows numRows = 0 Do While NOT rs3.EOF numRows = numRows + 1 ReDim Preserve fkeyary(1, numRows) fkeyary(0, numRows - 1) = rs3("FK_COLUMN_NAME") 'reads FK names fkeyary(1, numRows - 1) = rs3("PK_Column_Name") 'reads PK names rs3.MoveNext Loop rs3.Close end if Dim h set adox = server.createobject("adox.catalog") adox.ActiveConnection = conn if request.form("cmdSave") <> "" and (bRecEdit or bRecAdd) then if bAdd then rs.AddNew end if for each fld in rs.fields ifield = ifield + 1 'If field is AutoIncrement, just skip it. 'Added By Brad Orgill donada = false fldName = fld.name 'on error resume next 'modified By Brad Orgill if arrType(lConnID) <> tedbAccess or TableNameIsQuery then isAutoIncrement = fld.properties("IsAutoIncrement") else set fldx = AdoX.Tables(sTableName).Columns(fld.name) isAutoIncrement = fldx.properties("Autoincrement") set fldx = nothing end if if (not isAutoIncrement) AND (not donada) then For h = 0 to numrows - 1 if (fldName = fkeyary(1, h)) then 'OR (fldName = fkeyary(0, h)) then remove the coment to disallow FK updates donada = true end if Next if true then ' By Rami, line was "if (not donada) then" select case fld.type case adBoolean if len(request.form(fld.name)) then rs(fld.name) = True else rs(fld.name) = False case adLongVarBinary 'noop case adDate if ( request.form(fld.name) = "" or request.form(fld.name) = "0" ) and (fld.attributes and adFldIsNullable) = adFldIsNullable and bConvertDateNull then rs(fld.name) = NULL else if isDate(request.form(fld.name)) then rs(fld.name) = request.form(fld.name) end if end if case adSmallInt, adInteger, adCurrency, adUnsignedTinyInt if ( request.form(fld.name) = "" or request.form(fld.name) = "0" ) and (fld.attributes and adFldIsNullable) = adFldIsNullable and bConvertNumericNull then rs(fld.name) = NULL else if isNumeric(request.form(fld.name)) then rs(fld.name) = request.form(fld.name) end if end if case else if request.form(fld.name) = "" and (fld.attributes and adFldIsNullable) = adFldIsNullable and bConvertNull then rs(fld.name) = NULL else rs(fld.name) = request.form(fld.name) end if end select if Err <> 0 then response.write "Error while updating field '" & fld.name & "'
" response.write "Description: " & err.description & "
" select case err case -2147352571 'Type mismatch response.write "If this is an auto increment field, you may ignore this error.

" case -2147217887 'Field cannot be updated response.write "If this is an auto increment field, you may ignore this error.

" case else response.write "
" bError = True end select err = 0 else 'Added to enable editing of first field. if iField = 1 then sFieldValue = request.form(fld.name) end if end if end if end if next rs.update if err <> 0 then response.write "Error while updating.
" response.write "Description: " & err.description & "
" bError = True err = 0 end if if not bError then response.write "Record saved" end if end if 'on error goto 0 if bRecEdit or bRecAdd then %>
" method="post" name="frm"> <% lFields = rs.Fields.count for each fld in rs.fields if not bAdd then sValue = rs(fld.name) end if if bEncodeHTML then if sValue <> "" then sValue = server.htmlencode(sValue) end if response.write vbcrlf & "" response.write "" & vbcrlf select case fld.type case adLongVarWChar 'memo response.write "" case adLongVarBinary 'ole 'response.write "" response.write "" case adBoolean response.write "" else response.write ">" end if case adDate response.write "" case else 'Replace quotation marks with html " if sValue <> "" then sValue = replace(sValue, """", """) select case fld.type case adSmallInt, adInteger, adCurrency, adUnsignedTinyInt, adDate, adDBDate, adDBTime, adDBTimeStamp if arrType(lConnID) <> tedbDsn then '-[2]- Added by Danival flag = false set rs2 = conn.OpenSchema(adSchemaForeignKeys) do while not rs2.eof if rs2("FK_TABLE_NAME") = sTableName and rs2("FK_COLUMN_NAME") = fld.name and relation then Set Consulta = Server.CreateObject("ADODB.Command") Set Consulta.ActiveConnection = conn consulta.CommandText = "SELECT * FROM " & rs2("PK_TABLE_NAME") set RSConsulta = consulta.execute response.write "" flag = true end if rs2.movenext loop set rs2 = nothing end if if arrType(lConnID) <> tedbAccess or TableNameIsQuery then isAutoIncrement = fld.properties("IsAutoIncrement") else set fldx = AdoX.Tables(sTableName).Columns(fld.name) isAutoIncrement = fldx.properties("Autoincrement") set fldx = nothing end if if flag = false then if not isAutoIncrement then response.write "" else response.write "" end if end if '-[2]- End Addition case else response.write "" end select end select response.write "" & vbcrlf next if bRecEdit or bRecAdd then %> " %>
" & fld.name & "" if (fld.attributes and 102) AND bRequiredField then response.write "*" response.write "Cannot be accessed. " if (fld.attributes and 102) AND bRequiredField then response.write "*" response.write "" if (fld.attributes = 102) AND bRequiredField then response.write "*" response.write "Auto Increment" if sValue <> "" then response.write " (" & sValue & ")" response.write "" if (fld.attributes = 102) AND bRequiredField then response.write "*" response.write "
<% if bPopUps then %>       <% end if %> <% end if if bRequiredField then response.write "
* means the field is required.
<% else response.write "

You do not have permissions to edit records.

" end if %>