Ado.Net SqlConnection in Classic ASP

[techno]

Hi Guys,

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.

 

[Bob Barrows]

Hi Guys,

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

???
Why include the adovbs.inc file if you are not going to use the ADO
constants it defines?
This statement is kind of silly anyways. Why open a non-default cursor if
all you want is the result of a "count(*)"? This resultset is not going to
be updatable since it is the result of a calculation, and it only returns a
single row, so you don't need scrollability. Just open a default
forward-only read-only cursor:

sql="SELECT COUNT(*) AS CityCount FROM city"
Set objRS=objConn.Execute(sql,1)

would make much more sense from an efficiency standpoint ... that is, it
would make sense if objConn referred to an ADODB.Connection object.

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

Oooh, Compounding the errors! Your recordset's source sql statement does not
return a field called "count"
Anyways, it's more efficient anyways to use the field's ordinal position to
refer to it:

Count=objRS.Fields(0).value

or, if you have no reservations against taking advantage of default
properties:

Count=objRS(0)

response.Write Count

%>

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

Error Type:
ADODB.Recordset (0x800A0BB9)

This is a huge hint. The error is being raised by the statement that opens
your recordset ... not the statement that assigns the result of the call to
the dll to the objConn variable.

Arguments are of the wrong type, are out of acceptable range, or are
in conflict with one another.

That's because you failed to use an object with type ADODB.Connection when
attempting to open an _ADODB_ recordset.

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

I am. You can't. Maybe, and I'm not really sure of this, your dll can be
rewritten to use InterOp to instantiate an ADODB connection and return that
instead.

You might consider creating a .Net webservice that the asp page can use
ServerXMLHTTPRequest to query for its data. Using InterOp, the webservice
can return the xml that an ADODB recordset can use as its source. There are
too many details for a newsgroup post so I suggest you do some research via
the ADO documentation
(http://msdn2.microsoft.com/en-us/library/ms675532.aspx), the .Net
documentation and google.

 

[Bob Barrows]

Bob Barrows wrote:

I just noticed this:

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

Your failure to use the Set keyword means that objConn does not even refer
to a SQLConnection object. It MIGHT contain the connection string, which I
think is the default propertof or that object, but it certainly does not
refer to an object.

I.... Maybe, and I'm not really sure of this, your dll can
be rewritten to use InterOp to instantiate an ADODB connection and
return that instead.

However, it would probably be better if your dll returned the information in
a string that can be used to open an Adodb connection object:

sConn = objdll.GetConnectionString()
set objConn=createobject("adodb.connection")
objConn.Open sConn