Many of you will be using ADO from a Visual Basic Script running on IIS or the local browser (less likely). In this case, you won't be using the Visual Basic IDE to create your applications, which means that you'll have to code the ADO object instantiations yourself as in:
Dim cn ' This will be our ADO connection object. Set cn = Server.CreateObject("ADODB.Connection")
If you use Visual InterDev (VI) to develop your ASP or HTM pages, VI will try to help you populate objects it recognizes using statement completion—but it's not nearly as comprehensive and complete as the support in the Visual Basic IDE. That's why you'll have to add an include file to your project to complete symbol table population for Web development, as shown in the following Visual Basic Script code. Without adovbs.inc., your ASP code will be unable to reference certain arguments and unenumerated options. Well, actually, that's not true. Your code can reference things such as adCmdText as an option on the Recordset Open method, but it won't compile or run.
<!-#include File="adovbs.inc"-> <% Dim rs, cn set cn = server.CreateObject ("ADODB.Connection") Set rs = Server.CreateObject("ADODB.Recordset") Set stm = Server.CreateObject("ADODB.Stream") cn.ConnectionString = "dsn=WorkServer;uid=TestASP;pwd=Secret" cn.CursorLocation = aduseclient cn.Open if err then BuildErrorRecord 0, err, err.Description Else
After your ASP ADO objects are created, they can be referenced in Visual Basic Script just as they are in "regular" Visual Basic. Neither Visual InterDev nor "Visual" Notepad (your Visual Basic Script editor automatically capitalizes the object names and properties as you code). Because of this, you don't (always) have a visual confirmation that the code you're pounding in is correct. Perhaps in Visual Basic 7.0…
ADO can be used in any tier—as long as it makes sense. For example, the ADO Command object might not be the best choice for every application. I've been having a running debate with others around here about the efficiency of creating Command objects in the middle tier as when working with MTS components or ASP pages. Just keep in mind that ADO objects (like all COM objects) take time to instantiate and additional time to tear down. Because these creation and destruction operations are carried out each time the object is referenced (no, MTS does not support pooled components yet), you'll find it pretty expensive to create unnecessary ADO objects. Yes, you can use the ODBC API to decrease the time it takes to get your components started and destroyed. If you're thinking about bypassing ADO and using OLE DB directly, forget it. You can't use OLE DB from Visual Basic—despite its performance benefits over ADO. I discuss this more fully in the chapters on middle-tier development.