How To Use a Singleton SELECT from ASP (272165)

MORE INFORMATION

A singleton SELECT speeds data retrieval for a resultset that contains one record. The speed increase is a direct result of removing the overhead required for creating the recordset. However, because no recordset is actually created, only one read-only ADODB.Record is returned. This is true regardless of whether the specified SELECT results in multiple records being returned.

If the desired result set contains more than one record, then a normal recordset can be used. Therefore, it is not advisable to issue multiple singleton SELECTs as an alternative to opening a normal ADODB.Recordset in anticipation of retrieving the results faster. The buffering and caching that are set up for a normal ADODB.Recordset speed the performance when you want to retrieve multiple records.

<%@ Language=VBScript %>

<!--#include file="adovbs.inc" -->
<%
    Dim adoConn     '   As ADODB.Connection
    Dim adoRec      '   As ADODB.Record
    Dim sConn       '   As String
    Dim sQuery      '   As String
    Dim col         '   As ADODB.Field
    dim txtResults  '   output string for results    
    
                           
    set adoConn = CreateObject("ADODB.Connection")
    adoConn.ConnectionString  = "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=Northwind;uid=sa;pwd=;"
    adoConn.CursorLocation =   adUseServer
    adoConn.Open
        
    sQuery = "SELECT * FROM CUSTOMERS WHERE CUSTOMERID='ALFKI'"

    Set adoRec = CreateObject("ADODB.Record")   

    adoRec.Open  sQuery, adoConn, adModeRead, ,adOpenExecuteCommand 
    Response.write("<table><thead><th align=left>Column Name</th><th align=left>Column Value</th></thead>"  )
    For Each col In adoRec.Fields
        Response.Write ("<tr><td align=left>" & col.Name & "</td><td align=left>" & col.Value & "</td></tr>" )
    Next 
    Response.Write ( "</table>")

%>