How can I use a SqlConnection (ADO.Net) to connect to an ADO recordset?

Question

I am having tough time since 2 days, and not able to figure out what should i do with my implementation. I am not sure if this could be really workable. Please help me on this.

Below is my scenario:

1. I have a .Net Dll which has a method that returns a SqlConnection object after opening it. Below is the function (similar to which i am using - this is a sample function)

   SqlConnection conn = new SqlConnection();        
   conn.ConnectionString = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=myDatabase;Data Source=.\SQLEXPRESS;Trusted_Connection=true";        
   conn.Open();
   

2. I am installing this .dll in GAC using a private key.

3. Then registering it using regasm.exe utility which exports it as tlb.
4. Finally i am able to access this .net dll from my ASP application and able to get the return value from the method that i am accessing using the .dll class object. (There is certainly no issue in accessing the string value from a method, but this method i am trying to access the SqlConnection ado.net object)

Below is my sample ASP Page to show how i am accessing and using the connection object:

<!-- #include file="adovbs.inc" -->
<% 
set objdll = Nothing
set objConn = Nothing

//creating class object from the .net dll
set objdll = Server.CreateObject("gacDemo.clsGacDemo")

//accessing the GetConnection() method using the object. 
//GetConnection() returns the SQLConnection ado.net object
objConn = objdll.GetConnection()

set objRS = Server.CreateObject("ADODB.Recordset")
objRs.Source = "SELECT COUNT(*) AS CityCount FROM city"
objRS.Open "select count(*) as count from city", objConn, 3, 3, 1

Count = objRS.Fields("count").value
response.Write Count

%>

I am getting this bad error which i am trying to fix it,

Error Type: ADODB.Recordset (0x800A0BB9) Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

I am not sure if i can use the SqlConnection ado.net object in asp page.

Answer 1

In general, you can't use .NET code in Ancient ASP. The only practical way to do it is to expose the .NET code as COM objects. Look up "COM Interop" in the MSDN documentation.

Answer 2

I am already exporting it to tlb, and then using it in ASP application. Also in my .Net Assembly, i am setting ComVisible property to true, thats why i am able access the dll class in ASP Application.