Home arrow ASP arrow Page 3 - MS Access: Tables, Views and Procedures
ASP

MS Access: Tables, Views and Procedures


Ever wondered how to create a table, view or procedure with MS Access? In this article Eric shows us how to create all three and work with them from an ASP script.

Author Info:
By: Eric Beck
Rating: 4 stars4 stars4 stars4 stars4 stars / 74
July 04, 2002
TABLE OF CONTENTS:
  1. · MS Access: Tables, Views and Procedures
  2. · Building Our Database
  3. · Creating the Queries
  4. · Conclusion

print this article
SEARCH DEVARTICLES

MS Access: Tables, Views and Procedures - Creating the Queries
(Page 3 of 4 )

The next thing we need to do is to start creating our saved queries, or stored procedures. The first one will be used to add new tapes to our database. By selecting the SQL view, we can add the following query:

PARAMETERS AlbumName Text (50), ArtistName Text (50);
INSERT INTO bv_Tapes (Title, Artist)
VALUES (AlbumName, ArtistName);


Save this new query as "pr_AddTape". The "pr" indicates that this is a procedure type query, which manipulates the data in some way.

Notice that in our INSERT INTO statement, we are using our base view "bv_Tapes". This ensures that we can work on the tables without affecting our queries. The data in the underlying table will automatically be updated. By now, you're all experts at creating these queries so go ahead and pound out the rest of them on your own while I continue on to the next topic.

Creating the ASP Script
Our database is created, our views setup, and our procedure in ready. Let's now move Access to the side and code up our ASP page:

<!--#include virtual = " adovbs.inc "#-->
<%
If request.form("txtTitle") = "" then
%>

Our HTML code will be inserted here!

<%
Else

Dim objCommand
Dim objConnection

Set objConnection = Server.CreateObject("ADODB.Connection")
Set objCommand = Server.CreateObject("ADODB.Command")

objConnection.ConnectionString = "DSN=TapeDB"
objConnection.Open

Set objCommand.ActiveConnection = objConnection

objCommand.CommandText = "pr_AddTape"
objCommand.CommandType = adCmdStoredProc

objCommand.Parameters.Append objCommand.CreateParameter("Title",_
adChar, adParamInput, 50, request.Form("txtTitle"))

objCommand.Parameters.Append objCommand.CreateParameter("Artist",_ adChar, adParamInput, 50, request.Form("txtArtist"))

objCommand.Execute

'Clean up our objects
Set objCommand = NOTHING
Set objConnection = NOTHING
End if
%>


Our first line of code simply includes the contents of the adovbs.inc file, which contains a bunch of constants, some of which are used by our application. If you don't already have this file, just search your local hard drive for it. It is included with MDAC (Microsoft Data Access Components).

The third line in the code simply checks the value of the input box for our tape title to see if there is any data in it. If the textbox is empty, then it will print out the Add Tape form, which will allow us to add a tape to our database. If however, the title textbox is not empty then the display of the form will be ignored and the second part of our condition will be executed.

The next couple of lines of code are very important. These are the lines that process our data. The first thing we want to do is establish a connection to our data source. In this particular instance, I am using a System DSN:

objConnection.ConnectionString = "DSN=TapeDB"
objConnection.Open


Once we have our connection, we need to tell our command object which connection to use to execute the stored procedure that we will be using. We can now tell our command object which command to execute and the type of command it will be executing. In our case, we will be executing a command of type adCmdStoredProc that has the name "pr_AddTape":

objCommand.CommandText = "pr_AddTape"
objCommand.CommandType = adCmdStoredProc


The objCommand.Parameters.Append lines are used to pass in our parameters to our stored procedure. Remember that our stored procedure accepts 2 parameters: The first is the title of the tape, and the second is the artist:

objCommand.Parameters.Append objCommand.CreateParameter("Title",_
adChar, adParamInput, 50, request.Form("txtTitle"))

objCommand.Parameters.Append objCommand.CreateParameter("Artist",_ adChar, adParamInput, 50, request.Form("txtArtist"))


As mentioned above, objCommand.Parameters.Append is used to tell our command object that the stored procedure we are executing requires a parameter and that the information following will include the specifications for our parameter. The CreateParameter method of the command object is used to create a parameter and accepts the following input:
  • Parameter Name: This is the name that we gave the parameter when we created it in Access.
  • Parameter Type: Parameter type is simply the type of data you are sending. These types are defined in the MSDN Library.
  • Direction: adParamInput tells our command object that these are input parameters meaning that our stored procedure is expecting to receive them. Other values used here include adParamUnknown, adParamOutput, adParamInputOutput and adParamReturnValue.
  • Size: Is the size of our data type. This must match the value we used when creating our database.
  • Value: This is the value that we want to send to the database.
We can now execute our stored procedure and clean up our objects using the ASP script that we created above.
blog comments powered by Disqus
ASP ARTICLES

- Central Scoreboard with Flash and ASP
- Calorie Counter Using WAP and ASP
- Creating PGP-Encrypted E-Mails Using ASP
- Be My Guest in ASP
- Session Replacement in ASP
- Securing ASP Data Access Credentials Using t...
- The Not So Ordinary Address Book
- Adding and Displaying Data Easily via ASP an...
- Sending Email From a Form in ASP
- Adding Member Services in ASP
- Removing Unconfirmed Members
- Trapping HTTP 500.100 - Internal Server Error
- So Many Rows, So Little Time! - Case Study
- XDO: An XML Engine Class for Classic ASP
- Credit Card Fraud Prevention Using ASP and C...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials