ADO 更新记录

Dear 丶 2024-02-22 09:44 8阅读 0赞

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

--------------------

### 更新数据库表中的记录 ###

我们希望更新 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>

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

> <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>