JayasheeUsing 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 Name | Data Type |
| Name | Text |
| Street Address | Text |
| City | Text |
| State | Text |
| Zip | Text |
| Phone Number | Text |
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>
Add comment