Can I make a COM component in VB return a query result in the form of a recordset to the calling ASP Script?
Yes, a COM component can return a recordset. Create your Public Function in the (VB) COM component as follows:
Public Function GetRecordSet(byval strSQL as variant) as ADODB.Recordset
As you can see, the function accepts a single argument—a valid SQL statement—and returns an ADODB Recordset. You can increase the number of arguments to include a connection string or a database name, etc.
Within your Function, remember to create, disconnect, and then return a recordset:
Dim objRS as ADODB.Recordset
Set objRS = New ADODB.Recordset
objRS.open ....valid arguments including connection string
' Disconnect the recordset
objRS.ActiveConnection = Nothing
' -- Finally, return the recordset back
Set GetRecordSet = objRS
Remember to use the SET statement when working with objects. If the above code was within your COM component called MyCom in a Class Module called MyClass then your ASP page code would look like this:
Dim objCOM, objRS
' -- Create a recordset object
Set objRS = Server.CreateObject("ADODB.Recordset")
' -- Create our COM object
Set objCOM = Server.CreateObject("MyCom.MyClass")
' -- Execute the Function to get the recordset
Set objRS = objCOM.GetRecordSet("Select * from Customers")
' -- destroy the Com object
Set objCOM = Nothing
' -- play with your recordset variable
Again, remember to use the SET statement when working with objects. In 95 percent of the cases on Web pages, you will be doing read-only fetch from the database. In that case, always remember to use disconnected recordsets.