Database Connectivity with ASP and ADO

I have just started doing the ASP (Active Server Pages). I have completed some basic programs related to the ASP. Now I am stuck in connecting database with ASP and ADO. I don't know much about the ADO, so I have not tried many things.!! Can someone provide me the information about the database connectivity with ASP and ADO.? Please provide me the useful information as soon as possible.

I think that you should know more about the ASP and ADO. ASP uses a technology called ActiveX Data Objects (ADO) to work with databases. ADO is ActiveX technology which is built into the Internet Information Server (IIS). There are 3 major objects in ADO :

• the Command Object.
• the Connection Object.
• and the Recordset object.

From the above, you will be mainly using the Recordset object. These 3 objects are present, whenever you perform a database operation but every-time it is not necessary to explicitly create all 3 objects.

You will have to create a variable to access the objects of ADO. The syntax for creating a variable to access these objects in VBScript :

Code:

---

"[variable name] = Server.CreateObject("ADODB.[object name]")"

---

To communicate and manipulate a database is the main purpose of using ADO. The types of databases ADO will allow your ASP program to interact include Access, MySQL, and MSSQL.

You mostly use the Recordset Object in ADO. So I thought to give you more information about the Recordset. You can create a RecordSet object explicitly, or by executing a command through the Command Object. Some of the properties of Recordset Object which is used very frequently are :

• RecordCount - The number of records in the RecordSet.
• BOF - Beginning of file.
• EOF - End of file.
• MaxRecords - Maximum number of records returned in a query.
• CursorType - Forward-only, static,dynamic, and keyset.

According to me, one of the common tasks in a ASP web application is the retrieval of data from a database. The below coding demonstrates how to pick up data from a database table.

Code:

---

strSQL="SELECT * FROM students"
set rs=db.execute(strSQL)
if not rs.eof then
  do
    stud_name=rs("stud_name").value
    strProgram=rs("strProgram").value
    rs.movenext
  loop until rs.eof
end if
rs.close
set rs=nothing

---

I am providing you a simple ASP ADO SQL Query. The coding for this :

Code:

---

Set rs = Server.CreateObject("ADODB.RecordSet")
param = Request.Form("add")
q = "SELECT * FROM personal WHERE add LIKE '" & param & "'"
rs.Open q, "DSN=mydsn;"

if NOT rs.EOF then
    while NOT rs.EOF
          Response.Write rs("firstname") & " " & rs("add") & "<BR>"
          rs.MoveNext
    wend
end if

---