Managing Taxes
| Introduction. Now
we need to create an interface to display and modify state tax
rates. We need both a form for display and modification,
ManageTax.asp, and a processing script to process the updates,
UpdateTaxes.asp. Each of these ASPs has a stored procedure to
implement the database operations.
The following table gives the ASPs and stored procedures we will develop in this page. |
| Stored Procedure | ASP Container |
| sp_RetrieveTaxRates | ManageTax.asp |
| sp_UpdateTaxRate | UpdateTaxes.asp |
| The Stored Procedures.
First we present the stored procedures. Remember, you do not need
to implement the stored procedures, this is to illustrate what you would
need to do if you had the correct permissions in your database.
The first stored procedure uses a SELECT command in SQL to obtain all of the information from the Tax table. The file should be called sp_RetrieveTaxRates.asp. |
| <%@ Language=VBScript %> <% Option Explicit %> <!--#include File="adovbs.inc"--> <% ' Open a connection to our SQL Server database ' We will use the ADO Driver connection Dim connDeptProds, strSQLCreate Set connDeptProds = Server.CreateObject("ADODB.Connection") connDeptProds.ConnectionString="Driver={SQL Server}; Server=cisdev.quinnipiac.edu;" & _ "Database=YourUserName;UID=cis; PWD=csatqu" connDeptProds.Open ' Creating the SQL String to create the stored procedure strSQLCreate = "CREATE PROCEDURE sp_RetrieveTaxRates AS " & _ "select * from tax" connDeptProds.execute(strSQLCreate) connDeptProds.Close Set connDeptProds = Nothing Response.Write "<font size = 5>The SQL has executed</font>" %> |
| The second stored procedure uses an UPDATE command in SQL to modify the tax rate for the appropriate state in the Tax table. This works for individual states identified by idState. The file should be called sp_UpdateTaxRate.asp. |
| <%@ Language=VBScript %> <% Option Explicit %> <!--#include File="adovbs.inc"--> <% ' Open a connection to our SQL Server database ' We will use the ADO Driver connection Dim connDeptProds, strSQLCreate Set connDeptProds = Server.CreateObject("ADODB.Connection") connDeptProds.ConnectionString="Driver={SQL Server}; Server=cisdev.quinnipiac.edu;" & _ "Database=YourUserName;UID=cis; PWD=csatqu" connDeptProds.Open ' Creating the SQL String to create the stored procedure strSQLCreate = "CREATE PROCEDURE sp_UpdateTaxRate " & _ "@fltTaxRate float, " & _ "@idState int AS " & _ "update tax set fltTaxRate = @fltTaxRate " & _ "where idState = @idState" connDeptProds.execute(strSQLCreate) connDeptProds.Close Set connDeptProds = Nothing Response.Write "<font size = 5>The SQL has executed</font>" %> |
| As with all of the stored procedures you create, this
file would only need to be executed once in your database. After
that you should get an error that the stored procedure already
exists. Since this is linked to the WildWillies database you do not even
need to do this much. But, remember, if you do execute such an ASP
you can delete the ASP immediately after the stored procedure has been
created.
The ManageTax.asp. This is essentially a form page to display and allow modification of known tax rates. You need to save this file and upload it to your StoreManager subdirectory. Most of the lines of the code are due to the fact that tax rates are computed by each of fifty states and the District of Columbia.
Call the file ManageTax.asp. |
| <%@ Language=VBScript %> <!-- #Include file="include/validatecheck.asp" --> <HTML> <!-- ManageTax.asp - Supports managing the tax table settings for the store. --> <HEAD> <META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0"> </HEAD> <BODY> <!-- #include file="include/navinclude.asp" --> <!-- Start the update display --> <BR><B>Update the tax tables below:</b><BR> <% ' Create an ADO database connection set dbTax = server.createobject("adodb.connection") ' Create the record set set rsTax = server.CreateObject("adodb.recordset") ' Open the connection using our SQL Server DSN-less connection dbTax.ConnectionString="Driver={SQL Server}; Server=cisdev.quinnipiac.edu;" & _ "Database=WildWillies;UID=cis; PWD=csatqu" dbTax.Open ' Execute the Retrieve Tax Rates stored ' procedure to retrieve all of the current tax rates. sql = "execute sp_RetrieveTaxRates" ' Execute the statement set rsTax = dbTax.Execute(sql) ' Loop through the tax rates. do until rsTax.EOF
Loop |
| The form page should look like the following. |
|
The UpdateTaxes.asp. This is essentially a processing page to update a particular state's tax rate in the Tax table. You need to save this file and upload it to your StoreManager subdirectory. Since this ASP is completely an internal processing ASP it will do the following.
This coding is unusual for Jerke in its lack of efficiency, though it does allow the user to update several states at once. While coding practices and what is considered good programming can be quite ambiguous and the source of disagreement, and Jerke really is a coder of merit, this code requires some worthwhile improvements. Looks to me like it will be the source of a homework problem. So we can first get this implemented for later improvement, call the file UpdateTaxes.asp. |
| <%@ Language=VBScript %> <% ' **************************************************** ' UpdateTaxes.asp - Handles the updates of the tax ' tables. ' **************************************************** ' Create an ADO database connection set dbTaxUpd = server.createobject("adodb.connection") ' Create the record set set rsTaxUpd = server.CreateObject("adodb.recordset") ' Open the connection using our SQL Server DSN-less connection dbTaxUpd.ConnectionString="Driver={SQL Server}; Server=cisdev.quinnipiac.edu;" & _ "Database=WildWillies;UID=cis; PWD=csatqu" dbTaxUpd.Open ' Create an ADO database connection set dbTax = server.createobject("adodb.connection") ' Create the record set set rsTax = server.CreateObject("adodb.recordset") ' Open the connection using our SQL Server DSN-less connection dbTax.ConnectionString="Driver={SQL Server}; Server=cisdev.quinnipiac.edu;" & _ "Database=WildWillies;UID=cis; PWD=csatqu" dbTax.Open ' Exeute the stored procedure to retrieve the ' current tax rate settings. sql = "execute sp_RetrieveTaxRates" ' Execute the statement set rsTax = dbTax.Execute(sql) ' Loop through the tax rate settings. do until rsTax.EOF
Loop |
| You aren't likely to notice either ASP that much because they only run when a particular link on the ManageTax.asp is selected. At the end of each ASP the tax information is updated and the user is routed back to the ManageTax.asp. |