Unanswered: Populating a ASP web page with MS Access Data
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.
[HTML]<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<title>Search Courseware Database</title>
' 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" />
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.
<SCRIPT Language=VBScript RUNAT=SERVER>
Application("my_conn") = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=C:\wbcode\off2007\etg.accdb"
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.