Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2007
    Posts
    2

    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.

    Any help is appreciated. Thanks

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Please post what you've tried so far.
    Moving thread to ASP topic.
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2007
    Posts
    2
    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]

  4. #4
    Join Date
    Sep 2006
    Posts
    265
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •