ADO 更新记录

我们可使用 SQL 的 UPDATE 来更新数据库表中的某条记录。

更新数据库表中的记录

我们希望更新 Northwind 数据中 Customers 表的某条记录。首先我们需要创建一个表格，来列出 Customers 中的所有记录。

  1. <html>
  2. <body>
  3. <%
  4. set conn=Server.CreateObject("ADODB.Connection")
  5. conn.Provider="Microsoft.Jet.OLEDB.4.0"
  6. conn.Open "c:/webdata/northwind.mdb"
  7. set rs=Server.CreateObject("ADODB.Recordset")
  8. rs.open "SELECT * FROM customers",conn
  9. %>
  10.  
  11. <h2>List Database</h2>
  12. <table border="1" width="100%">
  13. <tr>
  14. <%
  15. for each x in rs.Fields
  16.   response.write("<th>" & ucase(x.name) & "</th>")
  17. next
  18. %>
  19. </tr>
  20. <% do until rs.EOF %>
  21. <tr>
  22. <form method="post" action="demo_update.asp">
  23. <%
  24. for each x in rs.Fields
  25.   if lcase(x.name)="customerid" then%>
  26.     <td>
  27.     <input type="submit" name="customerID" value="<%=x.value%>">
  28.     </td>
  29.   <%else%>
  30.     <td><%Response.Write(x.value)%></td>
  31.   <%end if
  32. next
  33. %>
  34. </form>
  35. <%rs.MoveNext%>
  36. </tr>
  37. <%
  38. loop
  39. conn.close
  40. %>
  41. </table>
  42.  
  43. </body>
  44. </html>

如果用户点击 "customerID" 列中的按钮，会打开一个新文件 "demo_update.asp"。此文件包含了创建输入域的源代码，这些输入域基于数据库中记录的字段，同时也含有一个保存修改的“更新按钮”：

  1. <html>
  2. <body>
  3.  
  4. <h2>Update Record</h2>
  5. <%
  6. set conn=Server.CreateObject("ADODB.Connection")
  7. conn.Provider="Microsoft.Jet.OLEDB.4.0"
  8. conn.Open "c:/webdata/northwind.mdb"
  9.  
  10. cid=Request.Form("customerID")
  11.  
  12. if Request.form("companyname")="" then
  13.   set rs=Server.CreateObject("ADODB.Recordset")
  14.   rs.open "SELECT * FROM customers WHERE customerID='" & cid & "'",conn
  15.   %>
  16.   <form method="post" action="demo_update.asp">
  17.   <table>
  18.   <%for each x in rs.Fields%>
  19.   <tr>
  20.   <td><%=x.name%></td>
  21.   <td><input name="<%=x.name%>" value="<%=x.value%>"></td>
  22.   <%next%>
  23.   </tr>
  24.   </table>
  25.   <br /><br />
  26.   <input type="submit" value="Update record">
  27.   </form>
  28. <%
  29. else
  30.   sql="UPDATE customers SET "
  31.   sql=sql & "companyname='" & Request.Form("companyname") & "',"
  32.   sql=sql & "contactname='" & Request.Form("contactname") & "',"
  33.   sql=sql & "address='" & Request.Form("address") & "',"
  34.   sql=sql & "city='" & Request.Form("city") & "',"
  35.   sql=sql & "postalcode='" & Request.Form("postalcode") & "',"
  36.   sql=sql & "country='" & Request.Form("country") & "'"
  37.   sql=sql & " WHERE customerID='" & cid & "'"
  38.   on error resume next
  39.   conn.Execute sql
  40.   if err<>0 then
  41.     response.write("No update permissions!")
  42.   else 
  43.     response.write("Record " & cid & " was updated!")
  44.   end if 
  45. end if
  46. conn.close
  47. %>
  48.  
  49. </body>
  50. </html>