Using ADO to Connect to a Database and Retrieve the Contents of a Table [ASP Programming]

ASP

ASP Using Database

Table Definitions:

Table Name	[MusicTable.mdb]-Download
Primary Key	MusicID
Foreign Key	Nil

Column Definition:

Column Name	Data Type
MusicID	AutoNumber
MusicName	Text
SingerName	Text
PageLink	Text
Lyrics	Text

Table Description:

Column Name	Description
MusicID	Create AutoNumber for music id
MusicName	insert text
SingerName	insert text
PageLink	insert text
Lyrics	insert text

<!--
=======================================================
File Name : OpenRecordsWithADO.asp
WebSite : http://programming-asp.blogspot.in/
Facebook : https://www.facebook.com/aspprogramming
Twitter : https://twitter.com/ASPProgramming
Created By : Bintu Chaudhary
=======================================================
-->

<%@Language=VBScript%>
<%Option Explicit %>
<html>
<head>
 <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
 <title>Using ADO to Connect to a Database and Retrieve the Contents of a Table</title>
</head>
<body>
<table align=center width=100% border=1 frame=border bordercolor=darkcyan rules="rows">
<tr bgcolor=cyan>
 <th width=5%>MusicID</Th>
    <th width=40%>MusicName</Th>
    <th width=10%>SingerName</Th>
    <th width=45%>Lyrics</Th>
</Tr>
<%
  Dim objConn
  Set objConn=Server.CreateObject("ADODB.Connection")
  objConn.Provider="Microsoft.Jet.OLEDB.4.0"
  objConn.Open "d:\ASP-Database.mdb"
  
  
  'Create a recordset object instance and retrieve the information fromt the MusicTable table.
  Dim objRS, mySQL
  
  'set query to open records from MusicTable
  mySQL="SELECT * FROM MusicTable order by MusicID asc"
  
  Set objRS = Server.CreateObject("ADODB.Recordset")
  objRS.Open mySQL, objConn 


  WHILE NOT objRS.EOF 
   
   MusicID   = objRS("MusicID")
   MusicName  = objRS("MusicName")
   SingerName  = objRS("SingerName")
   PageLink  = objRS("PageLink")
   Lyrics   = objRS("Lyrics") 
   
   Lyrics    = Left(Lyrics, 70)       
   
   %>
<tr>
    <td align=center><%=MusicID%></Td>
    <td><a href="<%=PageLink%>"><%=MusicName%></A></Td>
    <td><%=SingerName%></Td>                
    <td align=justify><%=Lyrics%>...</Td>                
   </Tr>
<%
   objRS.MoveNext
  WEND
%>    
</Table>

</body>
</html>

Download Complete Source

Below Figure shows the output of above asp code, when viewed through a browser.