ActiveX Data Objects (or ADO) are a set of COM objects created by Microsoft for accessing different flavors of databases with one fairly simple set of methods. It's often mentioned as a part of ASP (Active Server Pages), but it's really it's own thing, useable from any (Microsoft) programming environment. It's just lumped with ASP because it's what makes ASP easy and popular. The other programming languages still use OLE DB more often than not anyway.
Even though the word(?) "ActiveX" is in the name, it's not quite that evil. It's a really easy and efficient way to set up a database for the web (server-side, compatible with any browser, unlike ActiveX Controls). Here's a quick overview of the objects from an ASP programmer's perspective:
- ADODB.Connection
The Connection object is how the ASP pages connect to a database. Depending on what ODBC supports, there are quite a lot of data formats accessible: SQL Server, Microsoft Access, Excel spreadsheets and CSV files, Oracle, Sybase, FoxPro, and so on (most of those drivers come with MDAC, the ODBC distribution, hence the Microsoftiness - non-MS drivers are available, though).
Here's how to use the Conn object (assuming ODBC is set up, using VBScript):
Dim oConn ' Option Explicit!
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "DSN=pubs;UID=sa;PWD=;DRIVER={SQL Server}" ' Data Source Name may vary.
REM ...this is where data would be queried, see below
oConn.Close
Set oConn = Nothing
- ADODB.Recordset
The Recordset object is how to get results from the database. There are several ways to use the object, but the easiest is to just run a SQL query through it. Through ADO and ODBC, SQL (rather, Transact-SQL (T-SQL)) can be used on any available data source. This object can also save out any results to an XML file, but it's MSXML and always requires editing later.
A recordset example:
Dim oRS
Dim strSql
' create Conn object as shown above
strSql = "SELECT * FROM users" ' assuming there's a table called "users"...
Set oRS = oConn.Execute(strSql)
' now we have an object (oRS) holding a collection of query results, which can
' be accessed in more ways than explicable. easy way:
While Not oRS.EOF
Response.Write oRS("passwd") & "<br />" & vbCrLf
oRS.MoveNext
Wend
Set oRS = Nothing
' close and free Conn object as shown above
- ADODB.Command
The Command object is more or less useless using the above methods, unless you need to access stored procedures in an MSSQL DB. It's a little more than a pain in the ass, so I won't demonstrate. It also gives different ways of specifying the SQL query used in a Recordset, but at the cost of an extra object (and when you're developing for Windows NT, memory is always an issue).
There are several newer objects in the ADO package that do some really nice things (and some not-so-nice):
Additionally, I've never had any problem with ADO - no mysterious errors or bugs, no sudden crashes from using it, or anything. I've had problems with ODBC and MS Transaction Server (and of course, NT) while using ADO, but ADO is one of the few Microsoft products that shines. It's easier than English and twice as effective. A reboot or two every month, and things work fine and fast.
It should be noted that most of my ADO usage has been on a dual processor Compaq Reliant with extra racing stripes on the drive array and DS-3 tattoos, so your mileage may vary when comparing to similar technologies. But practical before proud - my first ASP/ADO project was an eBay-style auction site (the client didn't care much for copyright, heh), and it only took me a week of 8 hour days to get from design to functional. I had a full month to test and tweak it and watch the graphic designers scramble, instead of the usual day-past-deadline "power button's green, it must work!" test. Yay, ADO.