An investment in knowledge
pays the best interest.
-Benjamin Franklin
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
NEW Release: Access Developer Level 3   dismiss
 
 
Courses - Active Server Pages 301
Description: Advanced ASP 301
Running Time: 76 minutes
Pre-Requisites: ASP 202
Versions:
We use Standard ASP on a Windows Server. We also use Microsoft FrontPage 2003 for our Web editor. You can use any server or Web editor compatible with ASP.
 

 Click here for the Teacher's Outline
 

Major topics covered in this class:

  • Setting up a Database
  • Connecting to a Database
  • Using ODBC Data Sources
  • Reading Records from a Table
  • Looping Through a Set of Records
  • Adding Records to your Table

This is the big one: how to use a Database on your web site. We'll begin by learning a little bit about databases and exactly what they are. We will be using a Microsoft Access 2003 database in today's class (although later on I'll show you how to connect to a different database type). We'll create a simple Customer table and save our database.

Getting your database set up on your Web server is no easy task. I'll show you exactly what you have to do to get your database online and working. We'll set up the permissions on your Windows server so that people have access to read and write information from your database.

Next, we'll write an ASP script to connect to the database, open a table, and display some data from your records. If you can get this to work, everything else is going to work just fine for you. This screen shot doesn't look like much, but it's the first name of the first person in my customer table:

If you're not using Microsoft Access, I'll show you how you can use an ODBC connection on a Windows Server to connect to any other type of database, such as SQL Server, Oracle, even an Excel spreadsheet or delimited text file.

Since we know how to read in one record from our database, now I'll show you how to loop through all of the records in your database and display them all on the screen in a nice, pretty, formatted HTML table.

Finally, we'll learn how to add records to our database. This is important if you want to be able to save any new information to it.

Like I said earlier, this is the big one. Don't miss this class if you want to do anything with databases on your web site - including building shopping carts, blogs, user information sites, etc.

 

 
 


Try a FREE Demo Lesson

 

 Click here for the Teacher's Outline
 
 
 

Student Interaction: Active Server Pages 301

Richard on 8/20/2008:  ASP 301 covers DATABASES: Setting up a Database, Connecting to a Database, Using ODBC Data Sources, Reading Records from a Table, Looping Through a Set of Records, Adding Records to your Table. See the complete outline here: /site/courselist/asp/asp301/
Gary Gomer on 12/6/2008: Richard

I am trying to use the DSN database connection in ASP301 and I couldn't get it working. Here is my code:

<%
DSN = "ASP300" ' Name of the ODBC Connection

Set Conn = Server.CreateObject ("ADODB.Connection") ' Creates a connection to the database ADODB (Active X Data Objects B)
Conn.ConnectionString="DSN=" & DSN
Conn.Open

Set RS = Server.CreateObject("ADODB.Recordset") ' Tell it what recordsets / Queries to look at

RS.Open "CustomerT", Conn 'Tell it What Database to open (CustomerT) and what connection to use to Open It(Conn)

Response.write RS("FirstName") ' Will write the first name on the first record

RS.Close
Set RS = Nothing
Conn.Close
Set Conn = Nothing


%>

Here is the error I am getting:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

/ASP301/DSNDefault.asp, line 7

Line 7 is : Conn.Open

Any idea what is wrong?

Gary

james delaverson on 4/10/2009: asp301-lesson 3 06:20
Your example did not work on my web site. Here is what my provided said I need to make the connection. I don't know how to place it into your example.



'Database connection info and provider
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &_
Server.MapPath("_database\customert.mdb")

Richard Rost on 4/14/2009: James, there are a couple of ways you can write that connection string. The method I show is my preferred method. I don't personally like including the Data Source inside the Provider string like they showed you, but it should work fine. Ask them to provide you with the EXACT connection string that you need.
John Sottile on 4/15/2009: report - internet and distributed files system (DFS)

setting up a DFS common folder ath for ASP script testing. I've got a LAN with 4 machines. Learned that the SYSVOL on the server (\\servername\domain.local is the path to the LAN's Server Logon system, which is also the SYSVOL folder. I plan to put main DFS shared folder on the SYSVOL (domain common folder). I am having difficulty using a script to access SQL Server data, unless I am signed on as the administrator which is the password used with the install of SQL Server 2000. I hope to allow others to access data across my domain but logon for them may take time. This seemed a useful trick, I have enough literature. The first item syuggested was to remove delete permissions on all of the drive letters, even to administrators. So that no one deletes the whole drive and 300 gigs of my data. I almost did that myself today.

Mubeezi Micah on 12/31/2012: Dear Richard,

Time index 14:35
I am struggling with a situation when a user does not enter a valid date. I have tried using On Error Resume Next. The error gets ignored but the record does not get written into the table.

Please advice me what i can do.
Thank you!
MICAH

Reply from Richard Rost:

Well, first make sure there's something in the field. Then specifically convert it to a DATE value and make sure it's within a valid date range. I do something like this:

MyDateTxt = request("MyDateField")
If MyDateTxt = "" then response.write "INVALID DATE"
MyDate = CDATE(MyDateText)
if MyDate < #1/1/2001# or MyDate > Now() then response.write "INVALID DATE"

Something along those lines.

Mubeezi Micah on 1/1/2013: Dear Richard,

Time index 5:43
Now that i can easily see any errors generated, I managed to connect to my db using this method. Thank you for your continuous support.

MICAH

Reply from Richard Rost:

You're very welcome.

Salvatori C on 10/11/2014: Hey now, I'm trying connecting to a accdb with no luck, no option to save as a .mdb regarding the access database and no option to add to the DNS,
Thanks in advance

Reply from Alexander Hedley:

What is your connection string?
The Provider is different from ACCDB to MDB.

MDB
Conn.Provider = "Microsoft.Jet.OLEDB.4.0;
ACCDB
Conn.Provider=Microsoft.Ace.OLEDB.12.0;

Alonzo Dock on 1/22/2015: I am using expression web4 and Microsoft access 13 for my database. When I fill in my form and submit it I get a 404 not found. The requested document was not found on this server. In my asp page I try both ext. mdb and accdb for my database. I not sure if the problem is in databasepath for my database or RS.open. for my tabl. Can you help me on this problem. the database show up on my web site and I am using the _database folder

Reply from Alex Hedley:

Does the folder the db is in have the correct permissions on it?
What code are you using to connect?
The connection string would be different depending on what Access version you are using.

 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP