Displaying records of Access table by looping using ASP

We can display records of a table by looping through all the records of a recordset object. Recordset object properties are discussed before. We have to connect to our Access table using connecting string before collecting our records.

Now let us use our emp table where we have stored our employee details. This table has four fields. Filed name emp_no stores the unique employee number of the employee. Then Name field stores the name of the employee, desg field stores the designation of the employee then the last one dept field stores the department name of the employee.

Using connecting string we can connect and then we will use one sql command to select records from the table.


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

objconn.ConnectionString = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("db/emp.mdb")

objconn.Open

Here is our sql query to select records


strSQL = "SELECT  * FROM emp_m "

After opening the recordset object we will use recordset properties and then we will use Do Wile loop to loop through the records of the recordset. Here at the beginning of the each loop the condition of EOF is checked and if NOT TRUE then the loop is executed. Inside the loop we display two fields of each record by using their filed names like this.


Response.Write objRs("emp_no") & " " & objRs("name") & "<br>"

This way all the records are displayed and the loop ends if the last record is shown.

This is the simplest way to display records of a access table. Here is the output of the script.

145 John Reid
245 Peter Horn
175 Rabin Pack
187 Ran Keth

Here is the complete code


Dim objconn,objRS,strSQL



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

objconn.ConnectionString = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("db/emp.mdb")

objconn.Open



Set objRs = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT  * FROM emp_m "

objRS.Open strSQL, objconn



Do While Not objRS.EOF 

	Response.Write objRs("emp_no") & " " & objRs("name") & "<br>"

     objRS.MoveNext

 Loop

objRs.Close

objconn.Close

Displaying records of an Access table