ADO Update

If you want to saves or make any changes to the current record of a table in the database.For this need we may use the SQL UPDATE command to update or change the record in a table in a database.

Update Method Syntax

recordset.Update Fields, Values

Update Method Parameters

Fields

An optional Variant representing a single name or a Variant array representing names or ordinal positions of the field or fields you wish to modify.

Values

An optional Variant representing a single name or a Variant array representing names or ordinal positions of the field or fields you wish to modify.

This example used ADO to update an existing record. The SQL statement is the key and by changing that we can update whatever record we want to update.

<% ID = 7 %>
<% NAME = "Joe Smoe" %>
<% MESSAGE = "This is another test" %>

Dim DataConn
Dim CmdUpdateRecord
Dim MYSQL
��
Set DataConn = Server.CreateObject("ADODB.Connection")
Set CmdUpdateRecord = Server.CreateObject("ADODB.Recordset")
��
DataConn.Open "DBQ=" & Server.Mappath("../_database/dataone.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};"
��
MYSQL = "SELECT some_table.* FROM some_table WHERE (ID = " & ID & ")"
��
CmdUpdateRecord.Open MYSQL, DataConn, 1, 3
��
CmdUpdateRecord.Fields("NAME") = NAME
CmdUpdateRecord.Fields("MESSAGE") = MESSAGE
CmdUpdateRecord.Update
��
CmdUpdateRecord.Close
Set CmdUpdateRecord = Nothing
DataConn.Close
Set DataConn = Nothing
%>

Update a Record in a Table

If we want to update a record in the Employee table in the dataone database. We first create a table that lists all records in the Employee table:

<html>
<body>

<%

set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/database/dataone.mdb"
set rs=Server.CreateObject("ADODB.Recordset")
rs.open "SELECT * FROM employee",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)="employeeid" then%>
<td>
<input type="submit" name="employeeID"
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 any user clicks on the button in the "employeeID" column he/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:

<html>
<body>
<h2>Update Record</h2>

<%

set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/database/dataone.mdb"
cid=Request.Form("employeeID")
if Request.form("companyname")="" then
set rs=Server.CreateObject("ADODB.Recordset")
rs.open "SELECT * FROM employee WHERE employeeID='" & 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 employee 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>