www.fingertip.page.tl or www.fingertip.us.tt - ASP-5
   
www.fingertip.us.tt
  Index
  Free WebHost&DNS
  Hardware&Networking
  Red Hat Enterprise Linux 5
  Active Server Page2.0
  => Active Server Pages2.0
  => ASP-1
  => ASP-1.1
  => ASP-2
  => ASP-3
  => ASP-4
  => ASP-5
  Database Coding Standards
  .Net Platform
  Link Website
  Online Game
  Satellite Image Home
  Download Toolbar
  Other
  WebMaster
  Counter
  SiteMap
  Guestbook
  To Contact Us
  IT People
  Website Grade
  Learn English
  Donate Money
  Comrades
  Free Dictionary
  VaaHOO

www.e-comwebsite.blogspot.com
 ADO Update
This chapter explains how to use the SQL UPDATE command to update a record in a table in a database. 
 
 
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:
<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 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:
<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 & "customersID='" & cid & "',"
 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 customersID=" & 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>
 
 
ADO Delete
This chapter explains how to use the SQL DELETE command to update a record in a table in a database. 
 
 
 
 
Delete a Record in a Table
We want to delete a record in the Customers table in the Northwind database. We first create a table that lists all records in the Customers table:
<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_delete.asp">
<%
for each x in rs.Fields
 if 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_delete.asp". The "demo_delete.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 "Delete record" button that will delete the current record:
<html>
<body>
<h2>Delete 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="Delete record">
 </form>
<%
else
 sql="DELETE FROM customers"
 sql=sql & " WHERE customersID=" & cid
 on error resume next
 conn.Execute sql
 if err<>0 then
    response.write("No update permissions!")
 else 
    response.write("Record " & cid & " was deleted!")
 end if 
end if
conn.close
%>
</body>
</html>
 
 
 
 
 
 
 
ADO Demonstration
This chapter demonstrates how ADO can be used.
 
 
 
 
 
 
 
ADO Object References
ADO Command object
The Command object is used to execute a query against a data source. The query can perform actions like creating, adding, retrieving, deleting or updating records. The Command object can also use stored queries and procedures with parameters.
ADO Connection object
The Connection Object is used to create a connection to a data source.
ADO Error object
The Error object contains details about data access errors that have been generated during a single operation.
ADO Field object
The Field object contains information about a column in a Recordset.
ADO Parameter object
The Parameter object contains information about a parameter used in a stored procedure or query.
ADO Property object
The Property object represents a dynamic characteristic of an ADO object that is defined by the provider.
ADO Record object
The Record object is used to hold a row in a Recordset, or a directory or file in a file system.
ADO Recordset object
The Recordset Object is used to hold a set of records from a database table.
ADO Stream object
The Stream Object is used to hold a stream of text or binary data.
 
 
 
 
 
 
 
Display
<html>
<body>

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("northwind.mdb"))
set rs = Server.CreateObject("ADODB.recordset")
rs.Open "Select * from Customers", conn

do until rs.EOF
    for each x in rs.Fields
       Response.Write(x.name)
       Response.Write(" = ")
       Response.Write(x.value & "<br />")
    next
    Response.Write("<br />")
    rs.MoveNext
loop

rs.close
conn.close
%>

</body>
</html>
<html>
<body>

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("northwind.mdb"))

set rs = Server.CreateObject("ADODB.recordset")
rs.Open "SELECT Companyname, Contactname FROM Customers", conn
%>

<table border="1" width="100%">
<%do until rs.EOF%>
    <tr>
    <%for each x in rs.Fields%>
       <td><%Response.Write(x.value)%></td>
    <%next
    rs.MoveNext%>
    </tr>
<%loop
rs.close
conn.close
%>
</table>

</body>
</html>
<html>
<body>

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("northwind.mdb"))
set rs = Server.CreateObject("ADODB.recordset")
sql="SELECT Companyname, Contactname FROM Customers"
rs.Open sql, conn
%>

<table border="1" width="100%">
<tr>
<%for each x in rs.Fields
    response.write("<th>" & x.name & "</th>")
next%>
</tr>
<%do until rs.EOF%>
    <tr>
    <%for each x in rs.Fields%>
       <td><%Response.Write(x.value)%></td>
    <%next
    rs.MoveNext%>
    </tr>
<%loop
rs.close
conn.close
%>
</table>

</body>
</html>
<html>
<body>

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("northwind.mdb"))
set rs = Server.CreateObject("ADODB.recordset")
sql="SELECT Companyname, Contactname FROM Customers"
rs.Open sql, conn
%>

<table border="1" width="100%" bgcolor="#fff5ee">
<tr>
<%for each x in rs.Fields
    response.write("<th align='left' bgcolor='#b0c4de'>" & x.name & "</th>")
next%>
</tr>
<%do until rs.EOF%>
    <tr>
    <%for each x in rs.Fields%>
       <td><%Response.Write(x.value)%></td>
    <%next
    rs.MoveNext%>
    </tr>
<%loop
rs.close
conn.close
%>
</table>

</body>
</html>
 
 
Queries
html>
<body>

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("northwind.mdb"))
set rs = Server.CreateObject("ADODB.recordset")
sql="SELECT Companyname, Contactname FROM Customers WHERE CompanyName LIKE 'A%'"
rs.Open sql, conn
%>

<table border="1" width="100%">
<tr>
<%for each x in rs.Fields
    response.write("<th>" & x.name & "</th>")
next%>
</tr>
<%do until rs.EOF%>
    <tr>
    <%for each x in rs.Fields%>
       <td><%Response.Write(x.value)%></td>
    <%next
    rs.MoveNext%>
    </tr>
<%loop
rs.close
conn.close
%>
</table>

</body>
</html>
<html>
<body>

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("northwind.mdb"))
set rs = Server.CreateObject("ADODB.recordset")
sql="SELECT Companyname, Contactname FROM Customers WHERE CompanyName>'E'"
rs.Open sql, conn
%>

<table border="1" width="100%">
<tr>
<%for each x in rs.Fields
    response.write("<th>" & x.name & "</th>")
next%>
</tr>
<%do until rs.EOF%>
    <tr>
    <%for each x in rs.Fields%>
       <td><%Response.Write(x.value)%> </td>
    <%next
    rs.MoveNext%>
    </tr>
<%loop
rs.close
conn.close
%>
</table>

</body>
</html>
<html>
<body>

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("northwind.mdb"))
set rs = Server.CreateObject("ADODB.recordset")
sql="SELECT Companyname, Contactname FROM Customers WHERE Country='Spain'"
rs.Open sql, conn
%>

<table border="1" width="100%">
<tr>
<%for each x in rs.Fields
    response.write("<th>" & x.name & "</th>")
next%>
</tr>
<%do until rs.EOF%>
    <tr>
    <%for each x in rs.Fields%>
       <td><%Response.Write(x.value)%> </td>
    <%next
    rs.MoveNext%>
    </tr>
<%loop
rs.close
conn.close
%>
</table>

</body>
</html>
 
 
<html>
<body>

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("northwind.mdb"))

set rs=Server.CreateObject("ADODB.recordset")
sql="SELECT DISTINCT Country FROM Customers ORDER BY Country"
rs.Open sql,conn

country=request.form("country")

%>

<form method="post">
Choose Country <select name="country">
<%  do until rs.EOF
    response.write("<option")
    if rs.fields("country")=country then
      response.write(" selected")
    end if
    response.write(">")
    response.write(rs.fields("Country"))
    rs.MoveNext
loop
rs.Close
set rs=Nothing %>
</select>
<input type="submit" value="Show customers">
</form>

<%
if country<>"" then
   sql="SELECT Companyname,Contactname,Country FROM Customers WHERE country='" & country & "'"
   set rs=Server.CreateObject("ADODB.Recordset")
   rs.Open sql,conn
%>
   <table width="100%" cellspacing="0" cellpadding="2" border="1">
   <tr>
     <th>Companyname</th>
     <th>Contactname</th>
     <th>Country</th>
   </tr>
<%
do until rs.EOF
   response.write("<tr>")
   response.write("<td>" & rs.fields("companyname") & "</td>")
   response.write("<td>" & rs.fields("contactname") & "</td>")
   response.write("<td>" & rs.fields("country") & "</td>")
   response.write("</tr>")
   rs.MoveNext
loop
rs.close
conn.Close
set rs=Nothing
set conn=Nothing%>
</table>
<%  end if %>

</body>
</html>
 
 
Sort
 
Sort
<html>
<body>

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("northwind.mdb"))
set rs = Server.CreateObject("ADODB.recordset")
sql="SELECT Companyname, Contactname FROM Customers ORDER BY CompanyName"
rs.Open sql, conn
%>

<table border="1" width="100%">
<tr>
<%for each x in rs.Fields
    response.write("<th>" & x.name & "</th>")
next%>
</tr>
<%do until rs.EOF%>
    <tr>
    <%for each x in rs.Fields%>
       <td><%Response.Write(x.value)%></td>
    <%next
    rs.MoveNext%>
    </tr>
<%loop
rs.close
conn.close
%>
</table>

</body>
</html>
<html>
<body>

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("northwind.mdb"))
set rs = Server.CreateObject("ADODB.recordset")
sql="SELECT Companyname, Contactname FROM Customers ORDER BY CompanyName DESC"
rs.Open sql, conn
%>

<table border="1" width="100%">
<tr>
<%for each x in rs.Fields
    response.write("<th>" & x.name & "</th>")
next%>
</tr>
<%do until rs.EOF%>
    <tr>
    <%for each x in rs.Fields%>
       <td><%Response.Write(x.value)%> </td>
    <%next
    rs.MoveNext%>
    </tr>
<%loop
rs.close
conn.close
%>
</table>

</body>
</html>
<html>
<body>

<table border="1" width="100%" bgcolor="#fff5ee">
<tr>
<th align="left" bgcolor="#b0c4de">
<a href="demo_sort_3.asp?sort=companyname">Company</a>
</th>
<th align="left" bgcolor="#b0c4de">
<a href="demo_sort_3.asp?sort=contactname">Contact</a>
</th>
</tr>
<%
if request.querystring("sort")<>"" then
   sort=request.querystring("sort")
else
   sort="companyname"
end if

set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("northwind.mdb"))
set rs=Server.CreateObject("ADODB.recordset")
sql="SELECT Companyname,Contactname FROM Customers ORDER BY " & sort
rs.Open sql,conn

do until rs.EOF
   response.write("<tr>")
   for each x in rs.Fields
     response.write("<td>" & x.value & "</td>")
   next
   rs.MoveNext
   response.write("</tr>")
loop
rs.close
conn.close
%>
</table>

</body>
</html>
 
Recordset Object
<html>
<body>

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("northwind.mdb"))
set rs = Server.CreateObject("ADODB.recordset")
rs.Open "Select * from Customers", conn

'The first number indicates how many records to copy
'The second number indicates what recordnumber to start on
p=rs.GetRows(2,0)

response.write("<p>This example returns the value of the first column in the first two records:</p>")
response.write(p(0,0))
response.write("<br>")
response.write(p(0,1))

response.write("<p>This example returns the value of the first three columns in the first record:</p>")
response.write(p(0,0))
response.write("<br>")
response.write(p(1,0))
response.write("<br>")
response.write(p(2,0))

rs.close
conn.close
%>

</body>
</html>
 
 
 Locations of visitors to this page
 
 
 
 
 
 
   
This website was created for free with Own-Free-Website.com. Would you also like to have your own website?
Sign up for free