We may use the SQL UPDATE command to update a record in a table in a database.
我们可以使用UPDATE命令对数据库中的表单记录进行更新。
Update a Record in a Table
更新一个表单中的记录
We want to update a record in the Customers table in the Northwind database. We first create a table that lists all records in the Customers table:
我们希望更新“Northwind”数据库中的“Customers”表单记录。首先,我们需要创建一个表单,并在其内列出所有 “Customers”表单中的记录信息:
<html>
<body> <% set conn=Server.CreateObject("ADODB.Connection") conn.Provider="Microsoft.Jet.OLEDB.4.0" conn.Open "c:/webdata/northwind.mdb" set rs=Server.CreateObject("ADODB.Recordset") rs.open "SELECT * FROM customers",conn %> <h2>List Database</h2>
<table border="1" width="100%"> <tr> <% for each x in rs.Fields response.write("<th>" & ucase(x.name) & "</th>") next %>
</tr> <% do until rs.EOF %> <tr> <form method="post" action="demo_update.asp"> <% for each x in rs.Fields if lcase(x.name)="customerid" then%>
<td> <input type="submit" name="customerID" value="<%=x.value%>"> </td> <%else%>
<td><%Response.Write(x.value)%></td> <%end if next %> </form> <%rs.MoveNext%> </tr> <% loop conn.close %> </table> </body> </html> |
If the user clicks on the button in the "customerID" column he or she will be taken to a new file called "demo_update.asp". The "demo_update.asp" file contains the source code on how to create input fields based on the fields from one record in the database table. It also contains a "Update record" button that will save your changes:
如果用户点击“customerID”列中的按钮,那么它将会打开一个新的名为“demo_update.asp”的文件。“demo_update.asp”文件中包含了“基于数据库中的一条记录所对应的字段来创建一个输入域”的源代码以及一个“更新记录[Update record]”的按钮(可以通过它保存改变后的信息):
<html> <body> <h2>Update Record</h2> <% set conn=Server.CreateObject("ADODB.Connection") conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/webdata/northwind.mdb" cid=Request.Form("customerID") if Request.form("companyname")="" then set rs=Server.CreateObject("ADODB.Recordset") rs.open "SELECT * FROM customers WHERE customerID='" & cid & "'",conn %>
<form method="post" action="demo_update.asp"> <table> <%for each x in rs.Fields%> <tr> <td><%=x.name%></td>
<td><input name="<%=x.name%>" value="<%=x.value%>"></td> <%next%> </tr> </table>
<br /><br /> <input type="submit" value="Update record"> </form> <% else sql="UPDATE customers SET "
sql=sql & "companyname='" & Request.Form("companyname") & "'," sql=sql & "contactname='" & Request.Form("contactname") & "',"
sql=sql & "address='" & Request.Form("address") & "'," sql=sql & "city='" & Request.Form("city") & "',"
sql=sql & "postalcode='" & Request.Form("postalcode") & "'," sql=sql & "country='" & Request.Form("country") & "'"
sql=sql & " WHERE customerID='" & cid & "'" on error resume next conn.Execute sql if err<>0 then response.write("No update permissions!") else response.write("Record " & cid & " was updated!") end if end if conn.close %> </body> </html> |