Question:
Can I make a COM component in VB return a query result in the form of a recordset to the calling ASP Script?
Answer:
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.
