Page 259 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Database Management Systems/Managing Database

Notes So a sample code could be:
Dim recordsettest
Dim db_conn ‘The database connection string
recordsettest= Server.CreateObject (“ADODB.Recordset”)
db_conn = “Provider=Microsoft.Jet.OLEDB.version; Data Source= ~\student\student.mdb
recordsetest.Open “student”, db_conn
However, since many ASP pages on the site will require such a string, it is common to place the
connection string in an application variable in the global workspace.
<SCRIPT LANGUAGE= “VBScript” RUNAT= “Server”>
Sub Application_OnStart ( )
Dim db_conn
db_conn= “Provider” = Microsoft.Jet.OLEDB.version;
Data Source= ~\student\student.mdb Application (“db_conn”) = db_conn
End Sub
</SCRIPT>
The code to retrieve the contents of the student table will now include only the code for Recordset.
Dim recordsettest
Set recordsettest = Server.CreateObject(“ADODB.Recordset’’)
recordsettest.Open “Student”, Application(“db_conn”)

Thus, we have established the connection and can get information from the MS-Access database.
But another problem remains. How do we display the results?
1. Now, we can access recordsets, like of the database table, the result sets with the data row
as one database record. In our example, we have put the contents of the student table into
the recordsetrest object. An opened Recordset keeps track of the current record. Initially
the first record is the current record. A MoveNext method of the Recordset object moves
the pointer to the next record in the set, if any. An EOF property of the Recordset will
become true at the end of Recordset. Thus, to display student id and name, you may write
the following code:

Do While Not recordsettest.EOF
Response. Write “<li> “& recordsettest (“students-id”) &”
Response.Write ‘’<p> “& recordsettest (“name”) &” </p> </li>
recordsetest(‘’name)

recordsettest.MoveNext
Loop
If recordsettest.BOF Then
Response.Write “<p>No students data in the database. </p>”

Notes The BOF property checks if the file is empty.

2. Once you have completed the task then you must be close the recordset as:
Recordsettest.Close
This command sets free the connection to the database. As these connections may not be
very large in numbers, therefore, they need not be kept open longer than it is necessary.

252 LOVELY PROFESSIONAL UNIVERSITY