ADO Example - The Asset Manager

Web Developer 2

Home Instruction Assignments Grades Source Code Discussion Chat Syllabus Instructor Links Search Contact Us

Example: The Asset Manager (Get the code here)

Asset Manager Technology Overview

The Asset Manager was built using Active Server Pages (ASPs) written in JavaScript against an Access Database. The database is simple but serves our needs sufficiently. Both the ASP and Access technology were chosen for two reasons. First, given the ubiquity of the Windows platform, it was decided that the application could be migrated to any existing Windows network. Second, the technology was chosen because it was simply the easiest to implement of the technology on hand.

The architecture of the Asset Manager follows a Model-View-Controller (MVC) architecture. In a traditional MVC architecture, it is desirable to separate the tiers of the application into distinct modules representing the data (model), the presentation (view), and business rules (controller) that interact with the model and drives the view.

In the MVC architecture, the view is usually some front end application – usually web pages viewed within a browser. The controller is a set of scripts that are executed on a server of some sort – can be ASPs, Java servlets, CGI scripts, Java Server Pages (JSPs), or any other of many technologies. The model contains the data of the application – usually contained in a relational database but can be legacy data or any other structured data such as that from a mainframe or even XML.

In the Asset Manager, the model is contained in the database, the view is a set of ASPs that display the data, and the controller is the set of scripts that connect to the database and execute queries against it. Each page in the view uses specific controller components for the task at hand through the ASP #include mechanism.

Using the MVC architecture makes sense from a code reuse standpoint. Maintaining the necessary routines in component modules makes them easily ported to other, similar applications. In this regard, a component library of reusable routines can be established and shared among any developers that require them.

The code for the Asset Manager is reasonably well documented. Following the code should be no problem given a working knowledge of ASP and JavaScript.

Here’s the code:

Default.asp

<html>

<head>

<title>NCC Ventures Lab Asset Manager</title>

</head>

<body>

<h2>NCC Ventures Lab Asset Manager</h2>

<hr>

<p>Click a button to work with assets.</p>

</body>

</html>

searchResults.asp

<%@ language="JavaScript" %>

<html>

<head>

<title>Asset Search Results</title>

</head>

<body>

<h2>Search Results</h2>

<hr>

if(Request("exists")=="true"){

<p>Product exists in database - update it here.</p>

}

<p><a href="search.asp">New Search</a></p>

<p><a href="javascript:document.forms[0].submit()">Update Assets</a></p>

</body>

</html>

search.asp

<html>

<head>

<title>Find an Asset</title>

</head>

<body>

<h2>Find An Asset</h2>

<hr>

<p>Keyword: <input type="text" name="keyword" size="20"><input type="submit" value="Search"></p>

</form>

<p>Enter a keyword or keyword fragment.</p>

<p>If you enter a fragment, all assets that contain the fragment will be

included in your RecordSet.</p>

</body>

</html>

add.asp

<%@ language="JavaScript" %>

//Check for return from addAsset.asp

var error = Request("error");

<html>

<head>

<title>Add an Asset</title>

</head>

<body>

<h2>Add an Asset</h2>

<hr>

<p>Please fill in ALL Fields</p>

//if we got an error - show message

if(error=="true"){

Response.Write('<p class="error">An error occurred. Please make sure that the SKU and quantity fields are numeric.</p>');

}

<tr>

<td>SKU (no spaces)</td>

</tr>

<tr>

<td>Quantity</td>

</tr>

<tr>

<td>Manufacturer</td>

</tr>

<tr>

<td>Product</td>

</tr>

<tr>

<td>Version</td>

</tr>

<tr>

<td>Description</td>

</tr>

<tr>

</tr>

<tr>

</tr>

</table>

</form>

</body>

</html>

addAsset.asp

<%@ language="JavaScript" %>

//Grab the assetId and quantity

var assetID = Request("assetID");

var quantity = Request("quantity");

//Sanity check for numeric quantities and assetID

//assetID is Primary key and MUST be present

if((assetID*0)!=0||(quantity*0)!=0||assetID==''||assetID==null)

Response.Redirect("add.asp?error=true");

//OK, we've got an assetID,

//now check if the asset exists

var rs = Server.CreateObject("ADODB.RecordSet");

rs.Open("select assetID from products where assetID = " + assetID,con);

if(!rs.EOF){

//Asset exists, show it and display message

Response.Redirect("searchResults.asp?keyword=" + assetID + "&exists=true");

}

//No need to keep RecordSet at this point

rs.close();

rs = null;

//If we've made it this far, continue getting form variables

var mfr = Request("mfr");

var product = Request("Product");

var version = Request("version");

var description = Request("description");

//Handle single quotes

mfr = replaceAwithBinC("'","~",mfr+'');

product = replaceAwithBinC("'","~",product+'');

verson = replaceAwithBinC("'","~",version+'');

description = replaceAwithBinC("'","~",description+'');

var msrp = Request("msrp");

var cost = Request("cost");

//Create insert query

var query = "insert into products values(";

query += assetID + ", ";

query += quantity + ", '";

query += mfr + "', '";

query += product + "', '";

query += version + "', '";

query += description + "', '";

query += msrp + "', '";

query += cost + "')";

//Response.Write(query);

//Create Command object to execute query

var cmd = Server.CreateObject("ADODB.Command");

cmd.ActiveConnection = con;

cmd.CommandText = query;

cmd.Execute();

//Show the product listing

Response.Redirect("searchResults.asp?keyword=" + assetID);

asset.css

font: 10pt Verdana,sans-serif;

color: rgb(102,153,204);

font-weight: bold;

text-decoration: none;

}

th{

color: #FFFFFF;

font: 10pt Verdana;

font-weight: bold;

background-color: #000080;

}

body,td,p{

font: 10pt Verdana, sans-serif;

color: rgb(51,102,153);

}

h2{

font-size: 16pt;

font-weight: bold;

}

.error{

color: red;

}

results.asp

<tr>

<th width="15">Quantity</th>

<th>Delete</th>

<th>Manufacturer</th>

<th>Product</th>

<th>Version</th>

<th width="40%">Description</th>

<th>Price</th>

</tr>

<% while(!rs.EOF ){ %>

<tr>

<td bgcolor="#DDFFDD"><input type="text" size="2" name="quant<%= rs("assetID") %>" value="<%= rs("quantity") %>"></td>

</tr>

<% rs.MoveNext();

} %>

</table>

</form>

replace.asp

//Generic function to handle single quotes in queries

//Store and retrieve data using this filter to avoid

//headaches with single quotes and apostrophes

function replaceAwithBinC(a,b,c) {

i = c.indexOf(a);

aLen = a.length;

bLen = b.length;

while (i != -1) {

c = c.substring(0,i) + b + c.substring(i+aLen, c.length);

i = c.indexOf(a, i+bLen);

}

return c;

}

navigation.asp

<tr>

Show All Assets</a></td>

Search for an Asset</a></td>

Add an Asset</a></td>

</tr>

</table>

failedSearch.asp

<html>

<head>

<title>NCC Ventures Lab Asset Manager</title>

</head>

<body>

<h2>Search Results</h2>

<hr>

<p>Your Search Returned No Matches</p>

<p><a href="search.asp">New Search</a></p>

</body>

</html>

searchQuery.asp

//get search criteria

var keyword = Request("keyword");

//Create RecordSet

var rs;

rs = Server.CreateObject("ADODB.RecordSet");

//Build query string

var query;

if(keyword==null||keyword==""||keyword=='all')

query = 'select * from Products order by mfr';

else{

query = "select * from Products where mfr like '%";

query += keyword;

query += "%' or product like '%";

query += keyword;

query += "%' or assetID like '%";

query += keyword;

query += "%' or description like '%";

query += keyword;

query += "%' order by mfr";

}

//Retrieve records

rs.Open(query,con);

//if EOF is true, no records found

if(rs.EOF){

rs.close();

Response.Redirect("failedSearch.asp");

}

close.asp

//close the connection

con.close();

con = null;

update.asp

<%@ language="JavaScript" %>

//Get search criteria

var keyword = Request("keyword");

//Need to use SQL text, prevent dirty reads, and use a bi-directional cursor

var adCmdText = 0x0001;

var adLockPessimistic = 2;

var adOpenStatic = 3

//Create query string

var query;

//handle null, empty string, 'all' or build string

if(keyword==null||keyword==""||keyword=='all')

query = 'select * from Products';

else{

query = "select * from Products where mfr like '%";

query += keyword;

query += "%' or product like '%";

query += keyword;

query += "%' or description like '%";

query += keyword;

query += "%' order by mfr desc";

}

//Create RecordSet, open using above parameters

var rs = Server.CreateObject("ADODB.RecordSet");

rs.Open(query,con,adOpenStatic,adLockPessimistic,adCmdText);

//Loop through each record, assign quantity

//if remove is selected, call removeItem()

while(!rs.EOF){

var id = rs("assetID");

var quantity = Request("quant" + id);

rs("quantity") = quantity;

rs.Update();

if(Request("del" + id)=='on')

removeItem(id);

rs.MoveNext();

}

//Removes an asset from DB

function removeItem(id){

var cmd = Server.CreateObject("ADODB.Command");

cmd.ActiveConnection = con;

cmd.CommandText = "delete from Products where assetID = " + id;

cmd.Execute();

rs.Update();

}

Response.Redirect("searchResults.asp?keyword=" + keyword);

connect.asp

var con;

con = Server.CreateObject("ADODB.Connection");

con.Open("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" + Server.MapPath("\\asset\\db\\assets.mdb"));