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

Dropdown 2nd Dropdown Properties
ASP Home

Displaying records of an Access table