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.
Next: Conclusion >>
More ASP Articles
More By Eric Beck
