‘’ 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> </p><input type=SUBMIT value=Update></form>"
Response.Write "<p> </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> </p><input type=SUBMIT value=Create></form>"
Response.Write "<p> </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> </p><input type=SUBMIT value=Delete></form>"
Response.Write "<p> </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> "
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> </p></body></html>"
objRS.Close
Set objRS = Nothing
End Function
</Script>
延伸阅读
文章来源于领测软件测试网 https://www.ltesting.net/
