Memperbaharui Rekod ADO

Kami boleh menggunakan perintah SQL UPDATE untuk mengupdate satu rekod dalam tabel database.

Kemaskini rekod dalam tabel database

Kami ingin mengupdate rekod dalam tabel Customers di database Northwind. pertama-tama, kami perlu membuat sebuah tabel untuk mencatat semua rekod di 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>

Jika pengguna mengklik butang di dalam kolom "customerID", akan membuka fail baru "demo_update.asp". Fail ini mengandungi kod sumber untuk menghasilkan medan input, yang berdasarkan rekod dalam pangkalan data, serta butang "Kemaskini" untuk menyimpan perubahan:

<html>
<body>
<h2>Kemaskini Rekod</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("namakompany")="" 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="Kemaskini rekod">
  </form>
<%
else
  sql="UPDATE customers SET "
  sql=sql & "namakompany='" & Request.Form("namakompany") & "',"
  sql=sql & "namakontak='" & Request.Form("namakontak") & "',"
  sql=sql & "alamat='" & Request.Form("alamat") & "',"
  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("Tiada keizinan untuk memperbaharui!")
  else 
    response.write("Record " & cid & " was updated!")
  end if 
end if
conn.close
%>
</body>
</html>