Populating a ASP web page with MS Access Data

austen.robinson · #1 (**permalink**) 09-05-07, 15:26

I hope someone can help me because I'm totally lost. I need to populate a web page with data from an Access table. This sounds simple enough but I'm having real trouble. The page is part HTML and part ASP so if I dont have this post in the right spot I apologize.

I need a simple way to read from the table in Access, populate a web shell, grab the URL that would be unique to that page and do it another 99 times. My boss wants to be able to get the URL for each page so that the BOT can catalog the pages for searching. I have some code and can post it if it helps.

Currently the code is searching the table using ASP and populating the shell based on the result of the search. But even that is not working.

Any help is appreciated. Thanks

gvee · #2 (**permalink**) 09-06-07, 03:55

Please post what you've tried so far.
Moving thread to ASP topic.

austen.robinson · #3 (**permalink**) 09-06-07, 14:55

Here is what I have so far:

[HTML]<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<head>
<title>Search Courseware Database</title>
</head>
<body>
<%
' Declaring variables... always good practice!
Dim strURL ' The URL of this page so the form will work
' no matter what this file is named.

Dim cnnSearch ' ADO connection
Dim rstSearch ' ADO recordset
Dim strDBPath ' path to Access database (*.mdb) file

Dim strSQL ' The SQL Query we build on the fly
Dim strSearch ' The text being looked for

' Retreive the URL of this page from Server Variables
strURL = Request.ServerVariables("URL")

' Retreive the term being searched for. I'm doing it on
' the Query String since that allows people to bookmark results.
' You could just as easily have used the form collection.
strSearch = Request.QueryString("search")
'strSearch = Replace(strSearch, "'", "''")

' Since were doing this all in one page I need to see if anyone
' has searched for something. If they have we hit the DB.
' O/W I just show the search form and quit.

%>
<p>Search the Courseware Database.</p>
<p>Enter a word, or part of a word or phrase (% returns all)</p>
<form action="<%= strURL %>" method="get">
<input name="search" value="" />
<input type="submit" />
</form>

<%
If strSearch <> "" Then
' MapPath of virtual database file path to a physical path.
' If you want you could hard code a physical path here.
'strDBPath = "C:\wbcode\off2007\etg.accdb"
'strDBPath = Server.MapPath("etg.accdb")
strDBPath = etg.accdb

' Create an ADO Connection to connect to the sample database.
' We're using OLE DB but you could just as easily use ODBC or a DSN.
Set cnnSearch = Server.CreateObject("ADODB.Connection")

' This line is for the Access sample database:
cnnSearch.Open "Provider=MicroSoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"

' Build query based on the input.
strSQL = "SELECT Title, Description " _
& "FROM [Application] " _
& "WHERE Title LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR Description LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "ORDER BY title;"

' Execute our query using the connection object. It automatically
' creates and returns a recordset which we store in our variable.
Set rstSearch = cnnSearch.Execute(strSQL)

' Display a table of the data in the recordset. Loop through the
' recordset displaying the fields from the table using MoveNext
' to increment to the next record.

%>
<table border="0" align="left">
<tr>
<th align="left">Title:</th>
<td><%= rstSearch.Fields("Title").Value %></td>
</tr>
<tr>
<th align="left">Subject:</th>
<td><%= rstSearch.Fields("Subject").Value %></td>
</tr>
<tr>
<th align="left">Description:</th>
<td><%= rstSearch.Fields("Description").Value %></td>
</tr>
<tr>
<th align="left">Link:</th>
<td><%= rstSearch.Fields("Link").Value %></td>
</tr>
<tr>
<th align="left">Type:</th>
<td><%= rstSearch.Fields("Type").Value %></td>
</tr>
<tr>
<th align="left">Tech Format:</th>
<td><%= rstSearch.Fields("TechFormat").Value %></td>
</tr>
<tr>
<th align="left">Used In:</th>
<td><%= rstSearch.Fields("UsedIn").Value %></td>
</tr>
<tr>
<th align="left">Program:</th>
<td><%= rstSearch.Fields("Program").Value %></td>
</tr>
<tr>
<th align="left">Faculty / Sponsor:</th>
<td><%= rstSearch.Fields("FacultySponsor").Value %></td>
</tr>
<tr>
<th align="left">Year Created:</th>
<td><%= rstSearch.Fields("YearCreated").Value %></td>
</tr>
<tr>
<th align="left">Showcase Description:</th>
<td><%= rstSearch.Fields("ShowcaseDescription").Value %></td>
</tr>
<tr>
<th align="left">Documentation Link:</th>
<td><%= rstSearch.Fields("DocumentationLink").Value %></td>
</tr>
<tr>
<th align="left">Courseware:</th>
<td><%= rstSearch.Fields("Courseware").Value %></td>
</tr>
<tr>
<th align="left">HBSP Link:</th>
<td><%= rstSearch.Fields("HBSPLink").Value %></td>
</tr>
<tr>
<th align="left">Articles and Recognition:</th>
<td><%= rstSearch.Fields("ArticlesAndRecognition").Value %></td>
</tr>
<tr>
<th align="left">See Also Map:</th>
<td><%= rstSearch.Fields("SeeAlsoMap").Value %></td>
</tr>
<tr>
<th align="left">DemoLinkMap:</th>
<td><%= rstSearch.Fields("DemoLinkMap").Value %></td>
</tr>
<%
Do While Not rstSearch.EOF
%>
<tr>
<td><%= rstSearch.Fields("Title").Value %> <%= rstSearch.Fields("Description").Value %></td>
</tr>
<%

rstSearch.MoveNext
Loop
%>
</table>
<%
' Close our recordset and connection and dispose of the objects
rstSearch.Close
Set rstSearch = Nothing
cnnSearch.Close
Set cnnSearch = Nothing
End If

%>

</body>
</html>
[HTML]

SimonMT · #4 (**permalink**) 09-11-07, 08:51

Here's what I would consider:

Use a global.asa to define your connection:

Code:

<SCRIPT Language=VBScript RUNAT=SERVER>
Sub Application_OnStart

Application("my_conn") = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=C:\wbcode\off2007\etg.accdb"

End Sub

Essentially, you are creating a list of courses forgive me if I'm being pre-emptive but having found the course where to now. If would seem to be to create an asp page for each course which contains details of the course. Let the the Search Engines find because it contains useful and pertinent (related) information.

I would create an asp page with all the courses and (static) links to the details course pages.

Simon

Internet Services

Web Development

Digital Marketing

Consumer Tech