Connecting to Object that returns database connection

[T McDonald]

I'm having some issues with a dll I've created to return an ADOConnection
back to an ASP script.

I successfully open my object:
mytest = Server.CreateObject("mydll.database")
I then attempt to use the connection:
myConnection = mytest.getConnection

This is where the script breaks down with an "Object required" error.

I am able to access other methods in the dll, it just doesn't seem to be
returning the database connnection successfully.

Any help is greatly appreciated.

 

[Bob Barrows [MVP]]

I'm having some issues with a dll I've created to return an
ADOConnection back to an ASP script.

I successfully open my object:
mytest = Server.CreateObject("mydll.database")
I then attempt to use the connection:
myConnection = mytest.getConnection

Does getConnection return an object? If so, you cannot assign an object to a
variable without using the Set keyword:

Set myConnection = mytest.getConnection

This is where the script breaks down with an "Object required" error.

I suspect this error occurs on subsequent lines where you attempt to use the
connection object, right?

I am able to access other methods in the dll, it just doesn't seem to
be returning the database connnection successfully.

Any help is greatly appreciated.

Marshalling COM objects across processes is always problematic. My
suggestion would be to do all database work within the dll (making it a true
"database layer") instead of doing some in the dll and some in ASP. If you
absolutely must do some database work in ASP, then I would suggest
retrieving a connection string from the dll instead of the object itself.
There really is no benefit to passing the object vs. passing a string and
opening your own connection object.

 

[T McDonald]

Does getConnection return an object? If so, you cannot assign an object to a
variable without using the Set keyword:

Set myConnection = mytest.getConnection


I suspect this error occurs on subsequent lines where you attempt to use the
connection object, right?

Yes, this is exactly the issue. It didn't dawn on me that I needed to use
set. That clears up more than one bit of confusion.

Marshalling COM objects across processes is always problematic. My
suggestion would be to do all database work within the dll (making it a true
"database layer") instead of doing some in the dll and some in ASP. If you
absolutely must do some database work in ASP, then I would suggest
retrieving a connection string from the dll instead of the object itself.
There really is no benefit to passing the object vs. passing a string and
opening your own connection object.

I would prefer to do all the database connections, and collection of data
within the ddl, however, once I have the recordset, I have to pick particular
items out of the data - which I don't (or can't realistically at this point)
want to manipulate wthin the dll. Unless I am looking at this completely
wrong - are you suggesting to return the recordset?

Thanks for the help.

 

[Bob Barrows [MVP]]

I would prefer to do all the database connections, and collection of
data within the ddl, however, once I have the recordset, I have to
pick particular items out of the data - which I don't (or can't
realistically at this point) want to manipulate wthin the dll. Unless
I am looking at this completely wrong - are you suggesting to return
the recordset?

I actually wasn't but yes, I would prefer passing a disconnected recordset
back and forth, especially if an xml document created by using the
recordset's Save method is passed. The idea is to open a client-side
recordset using the adLockUpdateBatch LockType, disconnect it by setting its
activeconnection property to Nothing, use Save to persist it as an xml
document (rs.Save xmldoc) which you pass to the caller. The caller can use
it to open its own recordset (rs.open xmldoc), do what needs to be done to
it, then save it back to xml and pass it to the dll to be processed.

 

[Bob Barrows [MVP]]

Errr... that was the first suggestion he received ... two weeks ago. I
would imagine his problem is resolved by now.

 

[B@DJJ]

Sorry! that's what I get for not reading all the posts (and doing it only
now and again!)!

 

[T McDonald]

Yes 'set' was the missing piece of the puzzle. I picked up some other
helpful insights from the responses also.

Thanks for the help everyone.

 

[Venkatesan S]

Hi,
I am facing a different issue with the same scenario.

My component returns a Connection objec to ASP page and then I am trying to
use the same Connection object to Command/Recordset object to execute a SP

I am getting the error given below
************************************
ADODB.Recordset error '800a0bb9'

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

=====================================
Source code :
Option 1:
rs1.Open "MentorTest",objConnection.GetConnection,2,4,4

Option 2:

Dim sqlConnection
Set sqlConnection = objConnection.GetConnection
rs1.Open "MentorTest",sqlConnection,2,4,4

Option 3:
Dim sqlConnection
Set sqlConnection = Server.CreateObject("ADODB.Connection")
Set sqlConnection = objConnection.GetConnection
rs1.Open "MentorTest",sqlConnection,2,4,4

************************************
I ensured object creation by IsObject method, connection state of the
sqlConnection

Could you please tell what could be the issue. Your pointers will be
appreciated.

 

[Bob Barrows [MVP]]

Hi,
I am facing a different issue with the same scenario.

My component returns a Connection objec to ASP page and then I am
trying to use the same Connection object to Command/Recordset object
to execute a SP

I am getting the error given below
************************************
ADODB.Recordset error '800a0bb9'

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

=====================================
Source code :
Option 1:
rs1.Open "MentorTest",objConnection.GetConnection,2,4,4

Option 2:

Dim sqlConnection
Set sqlConnection = objConnection.GetConnection
rs1.Open "MentorTest",sqlConnection,2,4,4

Option 3:
Dim sqlConnection
Set sqlConnection = Server.CreateObject("ADODB.Connection")
Set sqlConnection = objConnection.GetConnection
rs1.Open "MentorTest",sqlConnection,2,4,4

************************************
I ensured object creation by IsObject method, connection state of the
sqlConnection

So that seems to indicate that you are misplacing the blame here. To
prove it, try a simple

set rs1 = createobject("adodb.recordset")
sqlConnection.MentorTest, rs1

I believe a dynamic cursor (2) is incompatible with a lock type of
adLockBatchOptimistic (4), which may be causing your error.

 

[Venkatesan S]

Hi,
Still I am getting the same error. But for the same parameters(2,4) If I
provide the connection string instead of a conneciton object it is working
fine. As suggested by you I have tried with different parameter value also
(0-adOpenForwardOnly, 3-adLockOptimistic).

I have tried another approach also with SQL Command Execute Method. Here
also I am getting error but the error is different(below one)

****************************************
Connection is a Object
Microsoft VBScript runtime error '800a000d'

Type mismatch: 'ActiveConnection'
****************************************



The error is occuring when assigning a connection object to the
activeconnection property of SQLCommand and Recordset.

The code of using command object is given below

Set SQLCommand.ActiveConnection = sqlConnection
SQLCommand.CommandText="MentorTest"
SQLCommand.CommandType=1
'Creates a read-only, forward only recordset
Set rs = SQLCommand.Execute


Thanks & Regards
Venkatesan S

 

[Bob Barrows [MVP]]

Hi,
Still I am getting the same error. But for the same parameters(2,4)
If I provide the connection string instead of a conneciton object it
is working fine. As suggested by you I have tried with different
parameter value also (0-adOpenForwardOnly, 3-adLockOptimistic).

I have tried another approach also with SQL Command Execute Method.
Here also I am getting error but the error is different(below one)

****************************************
Connection is a Object
Microsoft VBScript runtime error '800a000d'

Type mismatch: 'ActiveConnection'
****************************************



The error is occuring when assigning a connection object to the
activeconnection property of SQLCommand and Recordset.

The code of using command object is given below

Set SQLCommand.ActiveConnection = sqlConnection
SQLCommand.CommandText="MentorTest"
SQLCommand.CommandType=1
'Creates a read-only, forward only recordset
Set rs = SQLCommand.Execute

Well, the type mismatch does seem to indicate that you are not getting
an adodb connection from your component. In your initial post, you said
you had verified the object type and state - had you done that in the
consuming vbscript code or in the component?
Frankly, I don't think we're going to have much success getting to the
bottom of this from our vantage point. My earlier point about passing
connection strings instead of objects from components still stands.

 

[Venkatesan S]

Thanks Bob.
The reason for the problem is

"ADO connection objetcs cannot be marshalled across
apartments/processes/host "

So I am stopping my analysis on this with this....

Thanks & Regards
Venkatesan S