Creating An Intranet Site with ASP: Part 1

Creating An Intranet Site with ASP: Part 1

Part 1: Creating the database and reading from it

Firstly, let me outline what this tutorial will do. This is a practical guide, which will show you how to put together an intranet system, using ASP. This system will be easy to use for the complete novice, and simple to administer. This guide provides a basic outline: you will have to customize it to your needs.

That said, let's get going. These are the main features of our system:

· An interface for the user to select and read articles in categories and sub-categories, e.g. 'marketing' as a category and 'news' as its sub-category.

· A control panel for the staff to use, with no programming knowledge, to update their department's Intranet site.

· A central control panel for the administrator to use, to over see the workings of the site.

For this tutorial, I assume that you're using Microsoft Internet Information Server or Microsoft Personal Webserver. If you're an NT user and you don't have IIS, then you can download it from microsoft.com as part of the NT Option Pack.

I promised a practical guide, so let's get practical.

Step 1: Putting the database together

All of the information on the Intranet site, such as articles, will be stored in a database. This will provide a central location for information, making the site easier to back-up and organize. Consider your needs for the database. Below is a sample table:

And here are the values for one article:

| 01 | Request | Peter | HR | 03/07/2001 | Give me money!! | Yes |

So, the 'ID' column contains a unique reference number, and 'title', 'author' and 'department' are text values. 'Date' obviously contains the date, 'body' contains the article and 'enabled' lets the administrator enable or disable a particular article.

Draw out the columns you need for your site, and using software such as Microsoft Access, create a database. There should be a main table, which must contain the field entitled 'ID'. See below:

'ID': Set to 'autoincrement', 'long', 'unique', and 'primary key'.

For the other Fields:

Text values: Anything needing text values under 200 characters, set the datatype to 'text'.

Long text values: For text longer than 200 characters, set to the 'memo' data type.

Numbers: Use the 'integer' type for short numbers, 'long' for longer numbers.

Add a sample record using Access, like I've done above, and save your database in MDB format. Put this in a folder called 'databases' within the folder used by your web server, usually called WWWroot or Inetpub.

Step 2: The Interface: Creating a page to list all records in the database

The first step of the interface will be to to read articles from the database, and show them to the user. The code below will do this, and is fully annotated. NOTE: I advise you understand how this code works before you use it, by reading the accompanying notes, in case anything goes wrong. If you don't understand it all, don't panic!

'The first line declares the variables

Dim dblocat, cnn, cnnstring, rs, SQL

'The location of our database is stored in the variable called dblocat

'Change it to the location of your database on the server.

dblocat = "c:\inetpub\tutorial\data\intranet.mdb"

'These 2 lines tell the server how to connect to the database.

'cnn is used to store the 'connection object', ie how to connect to the database

'cnnstring stores the method in which the database is connected to, and takes

'dblocat (above) as the location for the database.

set cnn = server.CreateObject("ADODB.Connection")

cnnstring = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & dblocat

'Now we've told it how to connect, this says to the server "Go Connect!"

cnn.open(cnnstring)

'Right, we're doing well. The database is open, ready and waiting. Now, let's

'get some data out of it. These 2 lines create a 'recordset', basically letting

'you read the database.

Set rs = server.CreateObject("ADODB.Recordset")

rs.ActiveConnection = cnn

'Now we can query the database. The following line will do this:

SQL= "SELECT * From departmental"

'The stament above should be written in the format: 'SELECT * From tablename'

'Where 'tablename' is the name of the table you created earlier in the database.

rs.Open SQL

'Now, print the results on the screen. The HTML Tags, below, will put the data into a simple table.

rs.MoveFirst

<table border="0" cellspacing="0" cellpadding="0" align="center" width="80%">

<tr> <td><b>Title</b></td> <td><b>Author</b></td> <td><b>Date</b></td> </tr>

<tr><td></td><td></td><td></td></tr>

while not rs.EOF ' Keep going until all of the records have been displayed.

<tr><td><%=rs("Title")%></td> <td><%=rs("Author")%></td>

<td><%=rs("Date")%></td>

'Now, move to the next record & start again.

rs.MoveNext

wend

'Now, close the table & end the connection to the database.

Response.Write("</table></font>")

rs.close

cnn.close

And you're done! Copy this code into any page that you need to read from your database. When you need to display the contents of a particular field, type:

<%=rs("Name")%>

Where "name" is the name of that field. For example, say I wanted in my website:

"You have selected the user Jim Jones"

then code that you would use would be:

You have selected the user <%=rs("Name")%>.

The codey bit will automatically be replaced with the current database record.

With regards to keeping things in tabular form, typing <tr> will start a new row and </tr> will end it. Everything in between these two "tags" goes in that row. Typing <td> will start a cell within a row, and </td> will close it. Using these four tags, you can create a table with multiple rows and columns. For more information, get a book on HTML or download the code example at the bottom of the page.

Using this code, you can keep a central source of information that any page can read from. However, this displays all of the records; what you really want is to be selective with what you show. That will be covered in the next tutorial.

Next time:

· Choosing which data is shown.

· Creating a search engine.

Further reading:

· HTML For Dummies

· ASP By Example