1. How ADO relates to other data access technologies like ODBC and OLE DB, whenit is appropriate to use it?

   Details

   ADO is a native Microsoft technology which is a COM data access library and a layer over OLE DB. Actually ADO is simpler to use then OLE DB. OLE DB data acces is based on API data access and has more functionality and more fast access to databases. However OLE DB can not be used for programming data access in scripting languages where ADO sutes fine. When OLE DB functionality could be implemented in C++ client-server development, ADO functionality used for development web client-server systems. Now ODBC is subclass of OLE DB.

2. What is cursor?

   Details

   Cursor is a program or software agent that points to a chosen record in a recordset. When speed of access matters it makes sense to use forward-only cursor for the price of scanning records forwardly only.

3. There are two ways of inserting new recors to to a table. First it can be done by add method of a recorset and storing new row values for a record. Second way is to use SQL INSERT statement. What method is better?

   Details

   Using SQL statement for modifying and viewing data in a table is the best way because of less execution time. This is true for a typical table data modification and viewing SQL statements like INSERT, UPDATE, SELECT and DELETE.

4. Why command of a recordset like Filter, MovePrevious does not work anytime?

   Details

   It means that you are using a type of cursor that makes impossible to use this commands. Change cursor from Forward to another type.

5. What is disadvantage of using execute method of command object when getting a recordset?

   Details

   By default resulting recordset will be read-only with forward-only cursor. You can not implement search capabilities, along with modifications of a recordset. This type of command is useful for viewing data only.

6. Show minimum connection strings for connection to Access and SQL database?

   Details

   strAccess= strSQL = "Driver={SQL Server}; Database=dbname; uid=sa; pwd="

7. There are at least three explicit methods of getting a recordset: with connection, command and recordset object itself. What is the difference?

   Details

   First, examine getting a recordset with connection object:
   
   strConnection = "Driver={SQL Server};Server=(local);Database=Northwind;"
   set objConnection = Server.CreateObject("ADODB.Connection")
   objConnection.open strConnection
   set objRecordset = objConnection.Execute("SELECT COUNT(*) FROM Products")
   Response.Write objRecordset(0)
   
   Execute method of Connection object will return always, and this is the point, Forward-Only, Read-Only recordset. Therefore such recordset cannot be modified or updated. It can be viewed only.
   
   Second, consider getting recordset with Command object:
   
   strConnection = "Driver={SQL Server};Server=(local);Database=Northwind;"
   set objCommand = Server.CreateObject("ADODB.Command")
   objCommand.ActiveConnection = strConnection
   objCommand.CommandText = "SELECT COUNT(*) FROM Products"
   set objRecordset = objCommand.Execute()
   Response.Write objRecordset(0)
   
   We are using Execute method of Command object instead of Execute method of Connection object. The difference is that the Command'd Execute method lets execute stored procedures with parameters, while Connection's Execute method cannot do it. The common ground between Command'd Execute and Connection's Execute methods is that they return Forward-Only, Read-Only recordsets.
   
   Third, Recordset of any type can be returned with recordset object:
   
   strConnection = "Driver={SQL Server};Server=(local);Database=Northwind;"
   set objConnection = Server.CreateObject("ADODB.Recorset")
   objRecordset.open "SELECT COUNT(*) FROM Products", strConnection
   Response.Write objRecordset(0)
   
   Open method of Recordset object is frequently used with 4 parameters. 3^d parameter is cursor type, 4 ^th parameter is Lock Type used. Default values, with no parameters specified, are 0 and 1: Forward-Only cursor and Read-Only lock. It can be verified:
   
   Response.write "Cursor Type: " & objRecordset.CursorType & "<br>" 'gives - 0
   Response.write "Lock Type: " & objRecordset.LockType & "<br>" 'gives - 1
   
   Note: we've used Northwind database pre-installed with SQL Server 2000.
   

© (c) 2000, Stanislav Malevanny