<% '============================================================== ' TableEditoR 0.81 Beta ' http://www.2enetworx.com/dev/projects/tableeditor.asp '-------------------------------------------------------------- ' File: te_showtable.asp ' Description: Displays the selected table contents (IE Mode) ' 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: '-------------------------------------------------------------- ' # Mar 26, 2001 by Hakan Eskici ' Added support for automatic primary key detection ' Added support for multiple primary keys ' # Mar 28, 2001 by Hakan Eskici ' Modified the recordset paging control ' # Mar 29, 2001 by Hakan Eskici ' Added support for SQL Server boolean values ' Modified request's to .form or .querystring ' Added support for deleting multiple records ' # Apr 20, 2002 by Rami Kattan ' IE mode, using client-side data viewer ' More dynamic navigation ' Combo Selector for databases/tables ' # May 11, 2002 by Hakan Eskici ' Added page selector max check ' Added button for exporting the whole table ' # May 21, 2002 by Rami Kattan ' Fixed navigation of Stored Procedures ' Options for working with no-popups '============================================================== %> <% ' Get the requested number of records per page cPerPage = request.QueryString("cPerPage") if cPerPage="" then cPerPage = iDefaultPerPage 'If cPerPage = 0 Then cPerPage = 10 lConnID = request("cid") sTableName = request("tablename") sQuery = request("q") if (instr(sU, "MSIE")=0 OR instr(sU, "OPERA") > 0) then response.redirect "te_showtable2.asp?cid=" & lConnID & "&tablename=" & sTableName '------------------------------ 'added 8/10/01 by j.wilkinson, jwilkinson@mail.com 'added a check for nonAdmin users trying to view the admin table 'This is just checking that the connection ID = 0, assumes that 'non-admin users have no legitimate reason to get to that db at all. ' note that this may not protect against using queries to view ' this db and table if lConnID=0 and not bAdmin then response.redirect "te_admin.asp" end if '------------------------------ dim bOnPageLoad ' to tell header to add onload code to body tag or not. bOnPageLoad = true %> <% if sQuery <> "" then bQuery = True sTableName = replace(sTableName, """", "'") end if SendQueryString = "" htmlSorter = "" 'For each varr in Request.form ' if ucase(varr) <> "CID" and ucase(varr) <> "TABLENAME" then ' SendQueryString = SendQueryString & "&" &varr & "=" & Request.form(varr) ' end if 'next %> Please wait While loading data...

Home » Connections » <% allTablesCombo() %> » <%if bQuery then response.write "Query" else response.write "Table: [" allTablesCombo2(lConnID) response.write "]" end if%>

<% if bProtected then response.write session("teFullName") response.write " (logout)" end if %>

<% function isPrimaryKey(sFieldName) bPrimaryKey = False for iPK = 0 to ubound(aPrimaryKeys) if LCase(sFieldName) = LCase(aPrimaryKeys(iPK)) then bPrimaryKey = True exit for end if next isPrimaryKey = bPrimaryKey end function OpenRS arrConn(lConnID) 'Added by Hakan 'Find the primary key of the given table dim aPrimaryKeys if arrType(lConnID) = tedbDsn then 'response.write "Automatic primary key detection is not possible for DSN Connections. " & sSoWhat & "

" else sSoWhat = "(So What?)" set rsX = conn.openSchema(adSchemaPrimaryKeys) do while not rsX.eof if (rsX("table_name") = sTableName) then if sPrimaryKeyFieldName = "" then sPrimaryKeyFieldName = rsX("column_name") else sPrimaryKeyFieldName = sPrimaryKeyFieldName & "," & rsX("column_name") end if end if rsX.movenext loop rsX.close if (sPrimaryKeyFieldName = "") and (bQuery = False) then if arrType(lConnID) = tedbDsn then response.write "Automatic primary key detection is not possible for DSN Connections. " & sSoWhat & "

" else response.write "This table does not have any primary keys. " & sSoWhat & "

" end if else 'response.write "Primary key(s): " & sPrimaryKeyFieldName & "

" end if end if 'Set the primary key field to first field in the list by default if sPrimaryKeyFieldName = "" then sPrimaryKeyFieldName = 0 'Search Support Added by Kevin if request.form("cmdSearch") <> "" then bQuery = True 'Renamed cmdSubstring to chkSubstring if request.form("chkSubstring") <> "" then bSubstring = True end if 'For different data types, added enuming fields 'rather than form fields as Kevin did sSQL = "SELECT * FROM [" & sTableName & "] " on error resume next rs.Open sSQL,,,adCmdTable for each fld in rs.fields if request.form(fld.name) <> "" then sOP = " = " select case fld.type case adBoolean 'BUG: What if the user dont want to perform a distinction on the boolean field? 'Added by Hakan select case arrType(lConnID) case tedbSqlServer bTrue = "1" bFalse = "0" case else bTrue = "True" bFalse = "False" end select if len(request.form(fld.name))>0 then sFieldVal = bTrue else sFieldVal = bFalse case adLongVarBinary 'no search on OLE fields case adDate if isDate(request.form(fld.name)) then sFieldVal = "#" & request.form(fld.name) & "#" case adSmallInt, adInteger, adCurrency, adUnsignedTinyInt if isNumeric(request.form(fld.name)) then sFieldVal = request.form(fld.name) case else sFieldVal = "'" & replace(request.form(fld.name),"'", "") & "'" if bSubstring then sOp = " LIKE " sFieldVal = "'%" & request.form(fld.name) & "%'" else sFieldVal = "'" & request.form(fld.name) & "'" end if end select iSearchFieldCount = iSearchFieldCount + 1 if iSearchFieldCount = 1 then sWhere = " WHERE " & fld.name & " " & sOp & sFieldVal else sWhere = sWhere & " AND " & fld.name & " " & sOp & sFieldVal end if end if next sTableName = "SELECT * FROM [" & sTableName & "] " & sWhere rs.close end if if request.querystring("orderby") <> "" then sOrderBy = " ORDER BY [" & request.querystring("orderby") & "] " sOrderByLink = "&orderby=" & request.querystring("orderby") select case request.querystring("dir") case "desc" sOrderBy = sOrderBy & " DESC" sOrderByLink = sOrderByLink & "&dir=desc" case "asc" sOrderBy = sOrderBy & " ASC" sOrderByLink = sOrderByLink & "&dir=asc" case else sOrderBy = sOrderBy & " ASC" sOrderByLink = sOrderByLink & "&dir=asc" end select end if if instr(lcase(sTableName), "order by") <> 0 then sOrderBy = "" end if 'Added by Danival 'Modified by Hakan bProc = request("proc") if instr(1, ucase(sTableName), "SELECT") then sSQL = sTableName & sOrderBy else if bProc <> "" then bRecAdd = False bRecEdit = False bRecDel = False sParamString = request("paramstring") sProcURL = "&proc=1¶mstring=" & sParamString sSQL = "EXEC [" & sTableName & "] " & sParamString else sSQL = "SELECT * FROM [" & sTableName & "]" & sWhere & sOrderBy end if end if on error resume next rs.CursorLocation = adUseServer rs.Open sSQL, conn, adOpenStatic if err <> 0 then response.write "Error: " & err.description & "

" if bQuery then response.write "SQL : " & sSQL & "

" end if response.write "Click here to go back.

" CloseRS %><% response.end end if on error goto 0 'Performance Issue: 'Getting the recordset properties may take long time for tables with many records lRecs = rs.RecordCount lFields = rs.Fields.Count if cPerPage = 0 then RSPagesize = lRecs else RSPagesize = cPerPage end if if isNumeric(request("ipage")) then iPage = CLng(request("ipage")) rs.PageSize = RSPagesize rs.CacheSize = RSPagesize iPageCount = rs.PageCount if iPageCount = 0 then iPageCount = 1 if iPage < 1 then iPage = 1 if lRecs > 0 then rs.AbsolutePage = iPage if bQuery or te_debug then response.write sSQL & "

" end if 'Added by Rami Kattan 'for each fld in rs.fields ' htmlSorter = htmlSorter & "" & fld.name 'next for each fld in rs.fields 'Added by Hakan 'Support for automatic primary key detection 'Support for multiple primary keys aPrimaryKeys = split(sPrimaryKeyFieldName, ",") sPKFieldNames = "" sPKFieldValues = "" sPKFieldTypes = "" for iPK = 0 to ubound(aPrimaryKeys) if isNumeric(aPrimaryKeys(iPK)) then aPrimaryKeys(iPK) = 0 set fld = rs.fields(aPrimaryKeys(iPK)) if sPKFieldNames = "" then sPKFieldNames = fld.name else sPKFieldNames = sPKFieldNames & ";" & fld.name if sPKFieldTypes = "" then sPKFieldTypes = fld.type else sPKFieldTypes = sPKFieldTypes & ";" & fld.type next next if request.querystring("cid") = "0" AND request.querystring("tablename") = "Databases" then EditScriptName = "te_addDB" else EditScriptName = "te_showrecord" end if mainFrmExt_str = "?cid=" & lConnID & "&tablename=" & server.urlencode(sTableName) if request.querystring("proc") <> "" then mainFrmExt_str = mainFrmExt_str & sProcURL end if if request.querystring("q") <> "" then mainFrmExt_str = mainFrmExt_str & "&q=1" end if AddRecURL = EditScriptName & ".asp?cid=" & lConnID & "&tablename=" & server.UrlEncode(sTableName) & "&add=1&recs=" & RSPagesize & "&ipage=" %> <% if bRecAdd then %> <% end if %>

	<%if bQuery then response.write "Query" else response.write sTableName%>	<%=lRecs%> records	Add Record	First Previous Next Last     Refresh	Page <% if bPageSelector then if iPageCount < iPageSelectorMax then %><% For i = 1 to iPageCount response.write "" & i Next %><% else %>»<% end if else %>1<% end if %> of <%=iPageCount%>	Show >5 >10 >15 >20 >30 >40 >All records per page
Filter records      field: <% =htmlSorter %> Go

DataSrc="#DynamicData"> <% response.write "" for each fld in rs.fields if fld.type <> adLongVarBinary then if request("orderby") = fld.name then if request("dir") = "asc" then sDirection = "desc" else sDirection = "asc" end if else sDirection = "asc" end if response.write "" else response.write "" end if response.write vbCrLf 'Added by Hakan 'Support for automatic primary key detection 'Support for multiple primary keys aPrimaryKeys = split(sPrimaryKeyFieldName, ",") sPKFieldNames = "" sPKFieldValues = "" sPKFieldTypes = "" for iPK = 0 to ubound(aPrimaryKeys) if isNumeric(aPrimaryKeys(iPK)) then aPrimaryKeys(iPK) = 0 set fld = rs.fields(aPrimaryKeys(iPK)) if sPKFieldNames = "" then sPKFieldNames = fld.name else sPKFieldNames = sPKFieldNames & ";" & fld.name 'if sPKFieldValues = "" then sPKFieldValues = fld.value else sPKFieldValues = sPKFieldValues & ";" & fld.value if sPKFieldTypes = "" then sPKFieldTypes = fld.type else sPKFieldTypes = sPKFieldTypes & ";" & fld.type next next response.write "" response.write "" 'Key Field form elements for Multiple delete response.write "" response.write "" & vbCrLf response.write "" & vbCrLf response.write "" & vbCrLf for each fld in rs.fields response.write "" & vbCrLf next response.write "" CloseRS %>

	Action	" ' response.write "" response.write fld.name ' response.write "" response.write " " response.write "	" response.write fld.name response.write "

<% if bRecDel and bBulkDelete then %> <% end if if (bExportExcel or bExportXML) and bAllowExport then %> <% end if check1 = inStr(replace(ucase(sU)," ",""), "MOZILLA/4.0(COMPATIBLE;MSIE") = 0 check2 = inStr(replace(ucase(sU)," ",""), "OPERA") > 0 if check1 OR check2 then %> <% end if %>

<% if bExportExcel then %>Export to Excel  <% end if if bExportXML then %> Export to XML <% end if %>

If you don't see the table, or if it is mixed, click here.