To retrieve data from data base in ASP, perform the following steps:
1.
open a connection to a database. 2. create a record set, which provides access
to the data fields of each record. 3. retrieve data from the fields of the
recordset. 4. close the recordset and the connection.
The
following example shows these basic actions and how they are performed to retrieve
data from the northwide database. The script retrieves al product names and their
prices from the products table, in order by product names, and writes them to
the client.
RETRIEVING
PRODUCT DATA FROM THE NORTHWIDE DATABASE
1: <% @language = VBScript
%> 2: <% 3: option explicit 4: response. Expires = 0 5: dim
objconn, objrs, strquery 6: dim strconnection 7: set objconn = server. Createobject
(“ADODB. CONNECTION”) 8: strconnection = “DSN= northwind; database=
northwind;” 9: strconnection = strconnection & “UID=sa; PWD=;” 10:
objconn. Open strconnection 11: strquery = “SELECT PRODUCTNAME, unitprice
FROM products” Strquery = strquery & “ORDER BY productname” 13:
set objrs = objconn. Execute (strquery) 14: %> 15: <HTML> 16:
<BODY> 17: all products stored in the product table, 18: orered by
product name: <BR><BR> 19: <% 20: while not objrs.eof 21:
response. Write objrs (“productname”) &”(“ 22: response.
Write fromatcurrency(objrs(“unitprice”)) & “)<BR>” 23:
objrs. Movenext 24: wend 25: 26: objrs. Close 27: objconn. Close 28:
set objrs = nothing 29: set objconn = nothing 30: %> 31: </BODY> 32:
</HTML>
In
lines 1-3, the script instructs the script engine to use VBScript as the default
language and to make sure that every variable is declared.
Line
4 ensures that the browser does not cache the page. Without this line, the browser
would cache the page and the client would see obsolete data if the database, content
has changed between page calls.
