Home arrow ASP arrow Page 2 - Beginning ASP and Microsoft Access
ASP

Beginning ASP and Microsoft Access


Just starting out with ASP? In this article Ben shows us how to work with an Access database and basic name/value pairs via a form.

Author Info:
By: Ben Shepherd
Rating: 4 stars4 stars4 stars4 stars4 stars / 21
February 28, 2003
TABLE OF CONTENTS:
  1. · Beginning ASP and Microsoft Access
  2. · Getting Started
  3. · Name/Value Pairs in the URL
  4. · Conclusion

print this article
SEARCH DEVARTICLES

Beginning ASP and Microsoft Access - Getting Started
(Page 2 of 4 )

Firstly, we all know how to communicate to a database for an application in Windows using a DSN. If you are not sure or can’t remember here is a brief demonstration.

By navigating to "Start->Settings->Control Panel->Administrative Tools", we get an icon "Data Sources (ODBC)". If we double click on this icon we would open a window. This is the ODBC Data Source Administrator window.

Let's say we wish to communicate to a MS Access database. We select the "System DSN" tab option and click on the "Add..." button:



This will open a new window where you select the appropriate driver -- in our case the "Microsoft Access Driver (*.mdb)". When we do this another window will now pop-up, this is dedicated to set up a DSN with properties.



We click the "Select..." button and navigate to the MS Access database in the new window. Now click OK:

 

You will see that you have successfully created a DSN for your system.

The DSN-Less Approach
Setting up a DSN is a great idea for an application that is built for your system. But if you want a user to locate a remote database from a remote location, a DSN-less method would deem more appropriate. This implication is suggesting that you might consider DSN-less methodology when it comes to dynamic web pages, which may be database driven.

So we will now consider this example. To tackle the task at hand -- that is dynamic web pages -- we will create a database with your data inserted and use a language such as ASP to communicate with the database and manipulate the HTML page. Once this is done, it is time to start programming. The first thought that comes to mind is how we can find the database we seek in its remote location.

As stated previously, the database will be sent to the web server. Hopefully, it will be located in the same location as the home page for efficient site structure. The Server class in ASP allows us to create an ADODB object. To find the database that we are after, we must map its path. That is, find its physical location. Once this is done, we use the object to open to database and retrieve the contents we require.

In the body of the page the data retrieval will look somewhat like this. Note that this example contains no user id or password. The reason is that this information is targeted at beginners. So, the code is designed in a simplistic manner and it may get a little confusing if all the information is thrown at you all at once:

<%

'Creating a connection object and opening the database
Set v_connection_object = Server.CreateObject("ADODB.Connection")
v_mapped_path = Server.MapPath("yadayada.mdb")

v_connection_string = "Driver={Microsoft Access Driver (*.mdb)};DBQ="&v_mapped_path&";"

v_connection_object.Open v_connection_string

'Query database and retrieve a set of ids from the table id
v_query = "SELECT id FROM yadaTable1"
Set v_recordset = v_conn.Execute(v_query)

'Listing ids
WHILE NOT v_recordset.EOF
%>
<LI> id = <%=v_recordset(“id”)%></LI>
<%
v_record_set.MoveNext
WEND


'Closing connection and setting the variables to nothing
v_users.Close
v_connection_object.Close
v_record_set = Nothing
v_connection_object = Nothing
%>

Notice that ASP script is very similar to Visual Basic code. The ASP scripts are embedded in the HTML using <%,%> tags.

If you have studied SQL programming then you would know that you are able to do a wide range of data manipulation and extraction of data from a database. You may want to do, something like this next example.  

Here we will update the database when a user wants to change some personal information:

<%
v_update = "UPDATE yadaTable1 SET name = '" & v_name & "'"
v_connection_object.Execute(v_update)
%>

Insert a record into a database when a new user is created:

<%
v_insert = "INSERT INTO yadaTable1 VALUES name = '" & v_name & "'"
v_connection_object.Execute(v_insert)%>
%>

Delete a record in a database when a user wants to be off the system:

<%
v_delete = "DELETE * FROM yadaTable1 WHERE id =" & v_id
v_connection_object.Execute(v_delete)%>
%>

...where v_name and v_id are variables that are active on the page, such as the users first name and their numeric login identifier.


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