ProgrammerGuide.Net | Step by Step Programmer Guide

Asp Using Database

Using Database,

Why use Database?
Database, like text files, cookies, and the Session and Application objects, have their time and place. If you need to store a lot of information over a long period of time, databases are clearly the way to go. An entire branch of computer science is dedicated to study database design and how to create efficient database system. Due to this intensive research into database system, a database is the most effective and efficient method of storing method.

Working with Database Using ASP
Now that we’ve discussed the importance of using database, it’s time to get your hand dirty and begin working with them! We will start with simple example using Microsoft Access 2000 or 2002 database for query through ASP page.Create a database named ‘FriendsContactInfo.mdb’, and create a table named ‘Friends’ that contains the following columns.
 

Field NameData Type
NameText
Street AddressText
CityText
StateText
ZipText
Phone NumberText

Now add some information for friends of yours into table. You have now created a database with one table using Access 2000 or 2002 . Now that you have a database, you can query it through ASP page. To communicate with a database through an ASP page, you need to use the ActiveX Data Object component (ADO). This Component provides a number of objects that can be used to connect to and query a database. When you want to retrieve information from a database, you need to use a two-step process.
1. Establish a connection to the database.
2. Query the database, asking the database for the information you are interested in.

The following listing contains the code for display information of friends you added in database. It uses the Connection and Recordset objects to display the information from Friends tables.

<%@ Language=VBScript %>
<% Option Explicit %>
<!–#Include Virtual=”/adovbs.inc”>
<%
     ‘Open up a connection to our Access Database
     ‘We will use a DSN-Less connection.
    Dim objConn
    Set objConn = Server.CreateObject(“ADODB.Connection”)
    objConn.ConnectionString = “DRIVERS={Microsoft Access Driver (*.mdb)};” & _
                               “DBQ=c:\database\FriendsContactInfo.mdb”
    objConn.Open

     ‘Create Recordset object instance and retrieve the information 
    Dim objRS 
    Set objRS = Server.CreateObject(“ADODB.RecordSet”)
    objRS.Open “Friends”, objConn, , adCmdTable

     ‘Display the content of the Friends table
Do while Not objRS.EOF    
    Response.Write “<B>” & objRS(“Name”) & “</B><BR>”
    Response.Write objRS(“Street Address”) & “<BR>”
    Response.Write objRS(“City”) & “, ” & objRS(“State”) & “<BR>”
    Response.Write objRS(“ZIP”) & “<BR>”
    Response.Write objRS(“Phone Number”) & “<p><HR><P>”

     ‘Move to the next row in the friends table
    objRS.MoveNext
Loop

     ‘Clean up our ADO objects
    objRS.Close
    Set objRS = Nothing

    objConn.Close 
    Set objConn = Nothing
%>

Properties of the Connection
There is a collection in the connection object called the Properties collection. This collection contains the instance of the Property object for every property supported by the connection. The following example demonstrate the use of this collection.

Using Database,

<%@ Language=VBScript %>
<% Option Explicit %>
<!--#Include Virtual="/adovbs.inc">
<HTML>
  <BODY>
<%
    Dim objConn
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.ConnectionString = "DRIVERS={Microsoft Access Driver (*.mdb)};" & _
                               "DBQ=c:\database\FriendsContactInfo.mdb"
    objConn.Open
   
    Dim objProp
    For Each objProp in objConn.Properties
        Response.Write objProp.Name & ": " & objProp.Value & "<BR>"
    Next
    objConn.Close
    Set objConn = Nothing
%>
  </BODY>
</HTML>
Inserting, Updating & Deleting Database Records
AddNew  and Update are two method of Recordset object that you will need to make changes to the database. AddNew creates a new record in recordset. The new record is not added to the database until the Update method is called. After AddNew is called, the new record becomes the current record, and it remains the current record even after update is called. The following example demonstrate the use of AddNew and Update. It adds a new record to the table and then prints out the content of table. This allows you to verify  that the record was successfully added.
<%@ Language=VBScript %>
<% Option Explicit %>
<!--#Include Virtual="/adovbs.inc">
<HTML>
  <BODY>
<%
    Dim objConn
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.ConnectionString = "DRIVERS={Microsoft Access Driver (*.mdb)};" & _
                               "DBQ=c:\database\FriendsContactInfo.mdb"
    objConn.Open

    Dim objRS 
    Set objRS = Server.CreateObject("ADODB.RecordSet")
    objRS.Open "Friends", objConn, , adLockOptimistic, adCmdTable    objRS.AddNew
    objRS("Name") = "Sanjay Dutt"
    objRS("Street Address") = "29, Lokandwala Building,"
    objRS("City") = "Mumbai"
    objRS("State") = "Maharastra"
    objRS("Zip") = "356004"
    objRS("Phone Number") = "91-004-2569874" 
    objRs.Update
    objRs.MoveFirst
%>
<B>Entries in table </B>
<P>
<Table>
<TR>
    <TD>Name</TD>
    <TD>Address</TD>
    <TD>ZIP</TD>
    <TD>Phone Number</TD>
</TR>
<%  Do While Not objRS.EOF  %>
<TR>
     <TD><%= objRS("Name")%></TD>  
     <TD><%= objRS("Street Address")%>, <%= objRS("City")%>-<%= objRS("State")%></TD>
     <TD><%= objRS("Zip")%></TD>
     <TD><%= objRS("Phone Number")%></TD>
</TR>

<%
    objRS.MoveNext
Loop
    objRS.Close
    Set objRS = Nothing
    objConn.Close
    Set objConn = Nothing
%>
</TABLE>
</BODY>
</HTML>

Please click here for related products on Amazon!

Jayashee

Add comment

Want to Check Celebrities News?