<% ' Generic ASP Editor for Access Tables ' Version 1.01 - 9 November 1999 ' Roman Koch ' ===================================================================== ' Your customisations go here ' ===================================================================== ' Enter the name of the system DSN Session("myDSN") = "MP3" ' Enter the name of the table you want to edit ' Note: For text fields, the "Allow Zero Length" attribute must be set to YES Session("myTable") = "Customer" ' Enter the name of the Primary Key field ' Note: The Primary Key MUST be a "Autonumber"-type field Session("myKey") = "CustomerID" ' ===================================================================== ' End of the customisation section ' ===================================================================== Dim objConn If isObject(Session("dsnDefined")) Then Set objConn = Session("dsnDefined") Else Set objConn = Server.CreateObject("ADODB.Connection") objConn.Open Session("myDSN") Set Session("dsnDefined") = objConn End If strMyOwnPath = Request.Servervariables("PATH_INFO") intStart = InstrRev(strMyOwnPath,"/",-1,1) strMyName = Mid(strMyOwnPath,intStart+1) Session("myName") = strMyName strAction = Request.Querystring("action") lngRecord = Request.Querystring("num") Select Case strAction Case "list" 'list all records Call editList() Case "update" 'Get the current record and display in a form for editing Call editUpdate(lngRecord) Case "updateExec" 'Save the changes Call editUpdateExec(lngRecord) Case "insert" 'Display an empty form for entering a new record Call editInsert() Case "insertExec" 'Save the new record Call editInsertExec() Case "delete" 'Display the current record so deletion can be confirmed Call editDelete(lngRecord) Case "deleteExec" 'Delete the record Call editDeleteExec(lngRecord) Case Else 'same as list parameter Call editList() End Select %> <Script RUNAT="SERVER" LANGUAGE="VBSCRIPT"> '====================================================================== Function editUpdate(lngRecord) '====================================================================== strMyTable = Session("myTable") strMyKey = Session("myKey") strMyName = Session("myName") sqlQuery = "SELECT * FROM " & strMyTable & " WHERE " & strMyKey & "=" & lngRecord Set objRS = objConn.Execute(sqlQuery) intFieldCount = objRS.Fields.Count - 1 Response.Write "<html><head><title>Access Table Editor</title>" Response.Write "</head><body>" Response.Write "<h1>Update Record</h1>" Response.Write "<form name=editForm action=" & strMyName & "?action=updateExec&num=" & lngRecord & " method=POST>" Response.Write "<table>" For i = 0 To intFieldCount strName = objRS(i).Name strValue = objRS(i).Value Response.Write "<tr><td>" & strName & "</td>" If strName = strMyKey Then Response.Write "<td><input type=text readonly name=" & strName & " value=""" & strValue & """></td></tr>" Else Response.Write "<td><input type=text name=" & strName & " value=""" & strValue & """></td></tr>" End If Next Response.Write "</table><p>&nbsp;</p><input type=SUBMIT value=Update></form>" Response.Write "<p>&nbsp;</p></body></html>" Set objRS = Nothing End Function '====================================================================== Function editUpdateExec(lngRecord) '====================================================================== strMyTable = Session("myTable") strMyKey = Session("myKey") strMyName = Session("myName") sqlQuery = "SELECT * FROM " & strMyTable & " WHERE " & strMyKey & "=" & lngRecord Set objRS = objConn.Execute(sqlQuery) intFieldCount = objRS.Fields.Count - 1 sqlQuery = "UPDATE " & strMyTable & " Set " For i = 0 To intFieldCount strName = objRS(i).Name strValue = objRS(i).Value If strName <> strMyKey Then If (Vartype(strValue) >= 2) And (VarType(strValue) <= 6) Then sqlQuery = sqlQuery & strName & "=" & Request.Form(strName) Else sqlQuery = sqlQuery & strName & "='" & Request.Form(strName) & "'" End If If i < intFieldCount Then sqlQuery = sqlQuery & ", " End If Next sqlQuery = sqlQuery & " WHERE " & strMyKey & "=" & request.form(strMyKey) objConn.Execute(sqlQuery) Set objRS = Nothing Response.Redirect strMyName End Function '====================================================================== Function editInsert() '====================================================================== strMyTable = Session("myTable") strMyKey = Session("myKey") strMyName = Session("myName") Response.Write "<html><head><title>Access Table Editor</title>" Response.Write "</head><body>" Response.Write "<h1>Create Record</h1>" Response.Write "<form name=insertForm action=" & strMyName & "?action=insertExec method=POST>" Response.Write "<table>" sqlQuery = "SELECT * FROM " & strMyTable 'we do this query just to learn the field names Set objRS = objConn.Execute(sqlQuery) intFieldCount = objRS.Fields.Count - 1 For i = 0 To intFieldCount strName = objRS(i).Name If strName <> strMyKey Then Response.Write "<tr><td>" & strName & "</td>" Response.Write "<td><input type=text name=" & strName & "></td></tr>" End If Next Response.Write "</table><p>&nbsp;</p><input type=SUBMIT value=Create></form>" Response.Write "<p>&nbsp;</p></body></html>" Set objRS = Nothing End Function '====================================================================== Function editInsertExec() '====================================================================== strMyTable = Session("myTable") strMyKey = Session("myKey") strMyName = Session("myName") sqlQuery = "SELECT * FROM " & strMyTable 'we do this query just to learn the field names Set objRS = objConn.Execute(sqlQuery) intFieldCount = objRS.Fields.Count - 1 sqlQuery = "INSERT INTO " & strMyTable & " (" For i = 0 To intFieldCount strName = objRS(i).Name strValue = objRS(i).Value If strName <> strMyKey Then sqlQuery = sqlQuery & strName If i < intFieldCount Then sqlQuery = sqlQuery & ", " End If Next sqlQuery = sqlQuery & ") VALUES (" For i = 0 To intFieldCount strName = objRS(i).Name strValue = objRS(i).Value If strName <> strMyKey Then If (Vartype(strValue) >= 2) And (VarType(strValue) <= 6) Then sqlQuery = sqlQuery & Request.Form(strName) Else sqlQuery = sqlQuery & "'" & Request.Form(strName) & "'" End If If i < intFieldCount Then sqlQuery = sqlQuery & ", " End If Next sqlQuery = sqlQuery & ")" objConn.Execute(sqlQuery) Set objRS = Nothing Response.Redirect strMyName End Function '====================================================================== Function editDelete(lngRecord) '====================================================================== strMyTable = Session("myTable") strMyKey = Session("myKey") strMyName = Session("myName") sqlQuery = "SELECT * FROM " & strMyTable & " WHERE " & strMyKey & "=" & lngRecord Set objRS = objConn.Execute(sqlQuery) intFieldCount = objRS.Fields.Count -1 Response.Write "<html><head><title>Access Table Editor</title>" Response.Write "</head><body>" Response.Write "<h1>Delete Record</h1>" Response.Write "<form name=deleteForm action=" & strMyName & "?action=deleteExec&num=" & lngRecord & " method=POST>" Response.Write "<table>" For i = 0 To intFieldCount strName = objRS(i).Name strValue = objRS(i).Value Response.Write "<tr><td>" & strName & "</td>" Response.Write "<td><input type=text readonly name=" & strName & " value=""" & strValue & """></td></tr>" Next Response.Write "</table><p>&nbsp;</p><input type=SUBMIT value=Delete></form>" Response.Write "<p>&nbsp;</p></body></html>" Set objRS = Nothing End Function '====================================================================== Function editDeleteExec(lngRecord) '====================================================================== strMyTable = Session("myTable") strMyKey = Session("myKey") strMyName = Session("myName") objConn.Execute("DELETE * FROM " & strMyTable & " WHERE " & strMyKey & "=" & lngRecord) Response.Redirect strMyName End Function '====================================================================== Function editList() '====================================================================== strMyTable = Session("myTable") strMyKey = Session("myKey") strMyName = Session("myName") dim objRS Set objRS = objConn.Execute("SELECT * FROM " & strMyTable) intFieldCount = objRS.Fields.Count - 1 Response.Write "<html><head><title>Access Table Editor</title>" Response.Write "<style type=""text/css""> td {font-size: 8pt; border: 0} </style>" Response.Write "</head><body>" Response.Write "<h1>" & strMyTable & "</h1><p>" Response.Write "<table cellspacing=0 cellpadding=4>" Response.Write "<tr>" Response.Write "<td>Befehl</td>" For i=0 To intFieldCount Response.Write "<td>" & objRS(i).Name & "</td>" Next Response.Write "</tr>" '----- List record ----- intCounter = 0 Do Until objRS.Eof intCounter = intCounter + 1 If intCounter Mod 2 = 0 Then Response.Write "<tr bgcolor=#FFFFFF>" Else Response.Write "<tr bgcolor=#DDDDDD>" End If Response.Write "<td><a href=" & strMyName & "?action=update&num=" & objRS(strMyKey) & ">Edit</a> " Response.Write "<a href=" & strMyName & "?action=insert>New</a> " Response.Write "<a href=" & strMyName & "?action=delete&num=" & objRS(strMyKey) & ">Del</a></td>" For i = 0 To intFieldCount varFieldValue = objRS(i) if varFieldValue = "" Then Response.Write "<td>&nbsp;" Else If (Vartype(varFieldValue) >= 2) And (Vartype(varFieldValue) <= 6) Then Response.Write "<td align = right>" & varFieldValue Else Response.Write "<td>" & varFieldValue End If End If Response.Write "</td>" Next Response.Write "</tr>" objRS.Movenext Loop Response.Write "</table>" Response.Write "<p>&nbsp;</p></body></html>" objRS.Close Set objRS = Nothing End Function </Script>