Scribd Logo
Upload

Welcome to Scribd!

  • 61 views

    Introduction To Database Connectivity: Creating The Guestbook Database

    Uploaded by

    Sagar Kapoor
    This document provides instructions for creating a guestbook database in Microsoft Access and connecting to it from an ASP web page to display the database contents. It describes how to create the database tables, enter test data, connect to the database using ADO and SQL, retrieve records with a recordset, and display the records on the web page. The goal is to create a simple guestbook application that stores comments in an Access database and displays them through a dynamic ASP page.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOC, PDF, TXT or read online from Scribd

    Introduction To Database Connectivity: Creating The Guestbook Database

    Uploaded by

    Sagar Kapoor
    61 views4 pages
    This document provides instructions for creating a guestbook database in Microsoft Access and connecting to it from an ASP web page to display the database contents. It describes how to create the database tables, enter test data, connect to the database using ADO and SQL, retrieve records with a recordset, and display the records on the web page. The goal is to create a simple guestbook application that stores comments in an Access database and displays them through a dynamic ASP page.

    Original Title

    INTRODUCTION TO DATABASE

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    DOC, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document provides instructions for creating a guestbook database in Microsoft Access and connecting to it from an ASP web page to display the database contents. It describes how to create the database tables, enter test data, connect to the database using ADO and SQL, retrieve records with a recordset, and display the records on the web page. The goal is to create a simple guestbook application that stores comments in an Access database and displays them through a dynamic ASP page.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    61 views4 pages

    Introduction To Database Connectivity: Creating The Guestbook Database

    Uploaded by

    Sagar Kapoor
    This document provides instructions for creating a guestbook database in Microsoft Access and connecting to it from an ASP web page to display the database contents. It describes how to create the database tables, enter test data, connect to the database using ADO and SQL, retrieve records with a recordset, and display the records on the web page. The goal is to create a simple guestbook application that stores comments in an Access database and displays them through a dynamic ASP page.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    You are on page 1of 4

    INTRODUCTION TO DATABASE CONNECTIVITY

    In computing, Open Database Connectivity (ODBC) provides a standard software API method for
    using database management systems(DBMS). The designers of ODBC aimed to make it independent
    of programming languages, database systems, and operating systems.

    Creating the Guestbook Database


    To create a database your first need to open Microsoft Access and choose 'Blank Access Database'
    from the starting menu. You will be prompted for a name for the database and where you want it
    saved. Call the database 'guestbook.mdb' and save it in the same directory as the web page
    connecting to the database is going to be.

    You should now see the main Access dialog box, from here select 'Create table in design view'.

    You now need to create 3 fields for the database and select their data types.

    Field 1 needs to be called 'ID_no' and have the data type of 'AutoNumber'. Also set this field as the
    primary key.

    Field 2 needs to be called 'Name' and have the data type of text.

    Field 3 needs to be called 'Comments' and also has the data type of text, but this time you need to
    change the default field size of 50 to 100 characters under the 'General' tab in the 'Field Properties'
    box at the bottom of the screen.

    Once all the field's have been created and the data types and primary key set, save the table as
    'tblComments'.

    Now the table has been created you need to enter some test data into the table. You can do this by
    double-clicking on the new table (tblComments) in the main dialog box. From here you can enter
    some test data. I would recommend entering at least 3 pieces of test data.

    If you are having trouble creating the database then you can download this tutorial containing the
    Access Database with test data already entered.

    Connecting to the Guestbook Database


    Now that the database is created and test data entered we can get on with creating the web page to
    display the data from the database.

    First we need to start web page, open up your favourite text editor and type the following HTML.

    <html>
    <head>
    <title>My First ASP Page</title>
    </head>
    <body bgcolor="white" text="black">

    Next we can begin writing the ASP to connect to the database. First we need to create the variables
    that we are going to use in the script.

    <%
    'Dimension variables
    Dim adoCon 'Holds the Database Connection Object
    Dim rsGuestbook 'Holds the recordset for the records in the database
    Dim strSQL 'Holds the SQL query to query the database

    Next we need to create a database connection object on the server using the ADO Database
    connection object.

    'Create an ADO connection object


    Set adoCon = Server.CreateObject("ADODB.Connection")

    Now we need to open a connection to the database. There are a couple of ways of doing this either by
    using a system DSN or a DSN-less connection. First I am going to show you how to make a DSN-less
    connection as this is faster and simpler to set up than a DSN connection.

    To create a DSN-less connection to an Access database we need tell the connection object we created
    above to open the database by telling the connection object to use the 'Microsoft Access Driver' to
    open the database 'guestbook.mdb'.

    You'll notice the ASP method 'Server.MapPath' in font of the name of the database. This is used as we
    need to get the physical path to the database. Server.MapPath returns the physical path to the script,
    e.g. 'c:\website\', as long as the database is in the same folder as the script it now has the physical
    path to the database and the database name.

    'Set an active connection to the Connection object using a DSN-less connection


    adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("guestbook.mdb")

    If on the other hand you want to use a slower DSN connection to the database then you will need to
    replace the line above with the one below. Also if you don't know how to setup a system DSN you will
    need to read my tutorial on, Setting up a System DSN.

    'Set an active connection to the Connection object using DSN connection


    adoCon.Open "DSN=guestbook"

    Next create an ADO recordset object which will hold the records from the database.

    'Create an ADO recordset object


    Set rsGuestbook = Server.CreateObject("ADODB.Recordset")
    To query a database we need to use SQL (Structured Query Language). In the next line we initialise
    the variable 'strSQL' with an SQL query to read in the fields 'Name' and 'Comments' form the
    'tblComments' table.

    'Initialise the strSQL variable with an SQL statement to query the database
    strSQL = "SELECT tblComments.Name, tblComments.Comments FROM tblComments;"

    Now we can open the recordset and run the SQL query on the database returning the results of the
    query to the recordset.

    'Open the recordset with the SQL query


    rsGuestbook.Open strSQL, adoCon

    Using a 'Do While' loop we can loop through the recordset returned by the database while the
    recordset is not at the end of file (EOF). The 'Response.Write' method is used to output the recordset
    to the web page. The 'MoveNext' method of the recordset object is used to move to the next record in
    the recordset before looping back round to display the next record.

    'Loop through the recordset


    Do While not rsGuestbook.EOF

    'Write the HTML to display the current record in the recordset


    Response.Write ("<br>")
    Response.Write (rsGuestbook("Name"))
    Response.Write ("<br>")
    Response.Write (rsGuestbook("Comments"))
    Response.Write ("<br>")

    'Move to the next record in the recordset


    rsGuestbook.MoveNext
    Loop

    And finally we need to close the recordset, reset the server objects, close the server side scripting tag,
    and close the html tags.

    'Reset server objects


    rsGuestbook.Close
    Set rsGuestbook = Nothing
    Set adoCon = Nothing
    %>

    </body>
    </html>

    Now call the file you have created 'guestbook.asp' and save it in the same directory folder as the
    database, don't forget the '.asp' extension.

    And that's about it, you have now created a connection to a database and displayed you Guestbook in
    a web page, now to find out how add comments to the Guestbook through a web form read the next
    tuorial on, Adding Data to an Access Database.

    If you find that you are getting errors connecting to the database then please read through the Access
    Database Errors FAQ's, practically make sure you have the correct 'ODBC Drivers' installed on your
    system and if you are using the, 'NTFS file system', make sure the permissions are correct for the
    database and the directory the database in.

    You might also like