<% @LANGUAGE = VBScript %> <% Option Explicit On Error Resume Next ' In the event of an error, this forces logic to resume at the next statement %> Hamill's Used Tech Books

Hamill's Used Tech Books

Current List of Books in Stock

<% ' Obviously not a very secure way to handle password management, but suffices for this simple example! ' Note that since the password is in ASP it is not echoed to the server, so when one does a View Source ' it is not there Dim password password = "xyz" ' The next three blocks of code look for the presence of parameters on the URL. If they are there ' an add, delete or change action should occur. ' Is there a book to add? Dim add_book add_book = False If Request.QueryString("addbook").Count > 0 Then If Request.QueryString("password")=password Then add_book = True Else Response.Write("

Did not specify the right password to add a book to database.

") End If End If ' Is there a book to delete? Dim delete_book delete_book = False If Request.QueryString("deletebook").Count > 0 Then If Request.QueryString("password")=password Then delete_book = True Else Response.Write("

Did not specify the right password to delete a book from the database.

") End If End If ' Is there a book to change? Dim change_book change_book = False If Request.QueryString("changebook").Count > 0 Then If Request.QueryString("password")=password Then change_book = True Else Response.Write("

Did not specify the right password to change book details.

") End If End If ' Connect to the Access database Dim conn set conn=Server.CreateObject("ADODB.Connection") conn.Provider="Microsoft.Jet.OLEDB.4.0" conn.Open "D:\Inetpub\wwwroot\home\mhamill\ITD-210\Files\database\books.mdb" ' This is the recordset we will use for all transactions: select, update, insert and delete Dim rs set rs=Server.CreateObject("ADODB.recordset") ' Variable which will contain instructions in SQL to send to the database. Dim SQL ' Logic to add a book to the database. The values for the new book should all be in the URL. ' They are picked off one at a time and parsed into a valid SQL insert statement. If add_book Then ' I do a select first strictly so I can get at the table column names programatically. ' Once I can fetch the column names I can also fetch the values from the submitted ' form because the form field names have the same name as the table column name. rs.Open "select * from books", conn ' Generate the proper SQL insert statement programatically SQL = "insert into books (" for each x in rs.fields SQL = SQL & x.name & ", " next SQL = Mid(SQL,1,Len(SQL) - 2) & ") values (" for each x in rs.fields SQL = SQL & "'" & Request.QueryString(x.name) & "', " next SQL = Mid(SQL,1,Len(SQL) - 2) & ")" rs.Close ' I don't need the select statement anymore rs.Open SQL, conn ' This inserts the new row into the database If conn.Errors.Count > 0 Then Response.Write("

An insert error occurred!

") Else Response.Write("

The book was added to the database.

") End If End If ' Logic to change book details in the database. The values for the new book should all be in the URL. ' They are picked off one at a time and parsed into a valid SQL insert statement. If change_book Then ' I do a select strictly so I can get at the table column names programatically ' Once I can fetch the column names I can also fetch the values from the submitted ' form because the form field names have the same name as the table column name. rs.Open "select * from books", conn SQL = "update books set " for each x in rs.fields SQL = SQL & x.name & " = '" & Request.QueryString(x.name) & "', " next ' The books table has a key on ISBN, so I search that so I know I am updating only one row. SQL = Mid(SQL,1,Len(SQL) - 2) & " where isbn='" & Request.QueryString("change") & "'" rs.Close ' I don't need the select statement anymore rs.Open SQL, conn ' This updates the row in the database If conn.Errors.Count > 0 Then Response.Write("

An update error occurred!

") Else Response.Write("

The book details were changed in the database.

") End If End If ' Logic to delete the book in the database. The values for the new book should all be in the URL. ' They are picked off one at a time and parsed into a valid SQL insert statement. If delete_book Then ' This logic is simple. I only need the primary key to delete a unique row, and that is the ISBN SQL = "delete from books where isbn='" & Request.QueryString("delete") & "'" rs.Open SQL, conn ' This deletes the row in the database If conn.Errors.Count > 0 Then Response.Write("

A delete error occurred!

") Else Response.Write("

The book was delete from the database.

") End If End If ' Note that the form action returns to the same URL. This way if I change the name of the ' ASP file the code still works! %>
" method="get"> <% Dim records_read ' Create a variale so I can write a table header row of database column names records_read = 0 Dim x ' Local variable used in for/next statements rs.Open "select * from books", conn 'This fetches all the books from the database into a recordset do until rs.eof ' Loop through the record set until there are no more rows. If records_read = 0 Then ' Print the table column names programatically response.write("" & vbCrLf) for each x in rs.fields ' The name property of the field object tells the table column name response.write("" & vbCrLf) next ' And print some extra columns so we can perform some operations response.write("" & vbCrLf) response.write("" & vbCrLf) response.write("" & vbCrLf) response.write("" & vbCrLf) End If response.write("" & vbCrLf) ' Print on one row the details about one book for each x in rs.fields If IsNumeric(x.value) Then ' Right justify numeric columns. Makes them look pretty If x.name = "price" Then ' For the book price, show dollars and cents response.write("" & vbCrLf) Else response.write("" & vbCrLf) End If Else response.write("" & vbCrLf) End If next ' Create a URL for the Buy action. We'll call buy_book.asp and throw out the ' ISBN parametern so buy_book.asp knows what book the user wants to buy response.write("" & vbCrLf) ' A radio button metaphor will be used to indicate the row to be changed. Exclusivity goes ' down, which is somewhat counterintuitive. response.write("" & vbCrLf) ' A radio button metaphor will be used to indicate the row to be deleted. Exclusivity goes ' down, which is somewhat counterintuitive. response.write("" & vbCrLf) response.write("" & vbCrLf) records_read = records_read + 1 'So I can ensure I am not on the first row, so no more table headers are written rs.movenext ' This puts focus on the next row of results retrieved from the database loop %>
" & Ucase(x.name) & "Buy ItemChangeDelete
" & FormatNumber(x.value,2) & "" & x.value & "" & x.value & "Buy

Enter password to add, delete or change:



Add/Change a Book

To add a book simply fill in the fields below. To change details about a book, select the row containing the book, press the radio button in the Change column, then enter the new details below. Do not leave any field blank. For either option remember to enter the correct password above.

<% ' This logic dynamically draws the form fields needed to enter a new book. Because it is a simple ' table we can use all text fields. Note that I can get the size of the field by querying the database. for each x in rs.fields response.write("" & vbCrLf) response.write("" & vbCrLf) response.write("" & vbCrLf) next %>
" & Ucase(x.name) & "
<% ' When done it's always a good idea to explicitly clean up. rs.Close ' Close recordset first conn.Close ' Close connection last set conn = Nothing ' Free the memory %>