asked on

Operation is not allowed on an object referencing a closed or invalid connection.

Hello Everyone,
I’m getting a connection related error, although I think the actual connection works, because if I remove the second part of the code, I get to see the first page of my application...

'******************CODE from my dbConnection.asp page*********************

<%
'***************************FIRST PART OF THE CODE*******************
dim objConn, strConnStr
set objConn = server.createObject("ADODB.Connection")
strConnStr = "PROVIDER=MSDASQL;DRIVER={SQL Server}; SERVER={serverName}; DATABASE=databaseName;Integrated Security=SSPI"
objConn.ConnectionString = strConnStr

'***************************SECOND PART OF THE CODE*****************
'Create a command object for the stored procedures
dim objCommand
set objCommand = Server.CreateObject("ADODB.Command")
set objCommand.ActiveConnection = objConn
objCommand.CommandType = adCmdStoredProc
%>
‘***********************************************************************

When I add the SECOND PART OF THE CODE I receive an error on line 14
( set objCommand.ActiveConnection      = objConn )

The error is:

ADODB.Command error '800a0e7d'
Operation is not allowed on an object referencing a closed or invalid connection.
/login/include/dbConnection.asp, line 14

The problem is that the SECOND PART OF THE CODE is needed to get Stored Procedures to work (I think, I did not write this code…)

************************************************************************
But if I REMOVE the SECOND PART OF THE CODE, I get to see the first page of the application where it gives me the option of creating my own username and password:
The screen says: If you do not have a username, please click here.

When I click the link, however, I get this error:

Microsoft VBScript runtime error '800a01f4'
Variable is undefined: 'objCommand'
/login/UserAdd.asp, line 18
**********************************************************************
I must mention that I DID not have any problems when I was using SQL Authentication. The problems started ONLY AFTER the db administrator changed from SQL to Windows Authentication...

This was the code I used for SQL Authentication, but now that I changed to Windows Authentication (which is what my company is using from now on), I'm getting this error:

Microsoft OLE DB Service Components error '80040e21'

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

/login/include/dbConnection.asp, line 14

Line 14 in this case is: .open

ORIGINAL CODE:


<%
dim strConnStr
strConnStr = "PROVIDER=MSDASQL;DRIVER={SQL Server}; SERVER={SERVER NAME}; DATABASE=DatabaseName;Integrated Security=SSPI"

'SQL AUthentication worked without any problems...
'strConnStr = "PROVIDER=MSDASQL;DRIVER={SQL Server}; SERVER=SERVER NAME; DATABASE=DatabaseName;UID=username;PWD=password;"

' Create a connection to the database
dim objConn
set objConn = server.createObject("ADODB.Connection")
with objConn
      .ConnectionString      = strConnStr
      .CursorLocation            = adUseClient
      .open
end with

' Create a command object for the stored procedures
dim objCommand
set objCommand = Server.CreateObject("ADODB.Command")
set objCommand.ActiveConnection      = objConn
objCommand.CommandType                  = adCmdStoredProc

%>

dqmq

Add this between the two parts:
objConn.Open()

Brendt Hess

You need objConn.Open before assigning the connection to the Command object.

srafi78

Is the connection object a global object? Is it visible to the second part of your code, Where are you opening and closing the connection?

SOLUTION

ThinkPaper

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ThinkPaper

wow everyone got it before i did =)

Gemini532

ASKER

well, not necessarily because this .asp page is called by other pages, and it's possible that the connection is opened in those pages... hmmm... let me try your suggestion first...

Gemini532

ASKER

I added:
objConn.Open() '<------------- line 9

and I got this error:

Microsoft VBScript runtime error '800a01a8'

Object required: ''

login/include/dbConnection.asp, line 9

SOLUTION

ThinkPaper

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gemini532

ASKER

Yes, several pages.. whcih is why its' designed this way... so that it can be called whenver a connection needs to be open thoughout the application.
and this application is HUGE!!!

Gemini532

ASKER

it's made up of 3 smaller applcations, which are about 12-14 page applications

Gemini532

ASKER

It has 2 views and about 10 stored procedures

Gemini532

ASKER

Is not good if this connection is called by every single page of the application in an include statment such as this? Why would this give me this error: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

Gemini532

ASKER

I found a URL that claims to have the solution, the problem is I don't really understand it:
http://support.microsoft.com/kb/269495
It has many suggestions:
1. Do not use the "Persist Security Info" keyword in your ADO connection string.
I don't think I'm doing that so that part is OK
2. Add the OLEDB_SERVICES registry entry as follows:

Note Only create the OLEDB_SERVICES value in the registry keys of providers that support COM aggregation. The Exchange OLE DB provider does not support COM aggregation, and when you create this values, you receive the following error message:
0x80040E22
Non-NULL controlling IUnknown was specified, and either the requested interface was not IUnknown, or the provider does not support COM aggregation.

Warning If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.1. Start Registry Editor.
2. In the registry under HKEY_CLASSES_ROOT\CLSID, find the CLSID of the OLE DB provider. For example, the following registry key is for the Microsoft OLE DB Provider for SQL Server (SQLOLEDB):
HKEY_CLASSES_ROOT\CLSID\{0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}
Click the CLSID, and then on the Edit menu, click Add Value and add the following registry value:
Value Name: OLEDB_SERVICES
Data Type: REG_DWORD
Value: 0xFFFFFFFF

Note To find the CLSID for the provider you are using, search for the provider's ProgID (SQLOLEDB, for example) in the registry under HKEY_LOCAL_MACHINE\SOFTWARE\Classes\. Under the ProgID, there will be a key named CLSID.

'****************************************THIS SECOND OPTION I DO NOT UNDERSTAND!!! :( ****************

Gemini532

ASKER

I found out from the DB administrator that the following line is incorrect:
strConnStr = "PROVIDER=MSDASQL;DRIVER={SQL Server}; SERVER={serverName}; DATABASE=databaseName;Integrated Security=SSPI"

This is because it uses my OWN log in, but since this is an applicatioin on the INTERNET where people who log in from their differet computer need to use a specific username and password, which is not mind, this is not correct!
Does this make sense?

If this part is incorrect than the entire program fails
Then it's still a connection issue and the error message is:

Microsoft OLE DB Provider for ODBC Drivers error '80040e4d'
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user SUPERFAKE\fake'.
/login/include/dbConnection.asp, line 18

fake being the username of the login and SUPERFAKE being the DOMAIN

Gemini532

ASKER

The connection string should be similar to the SQL SERVER Authentication according to my db manager therefore should look like this:

strConnStr = "PROVIDER=MSDASQL;DRIVER={SQL Server}; SERVER=SuperServer; DATABASE=OPICForms; UID=SUPERFAKE\fake;PWD=SuperPassword;"

ASKER CERTIFIED SOLUTION

dqmq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

dqmq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gemini532

ASKER

We tried to use Windows Authentication but apparently it only works for II6
Is this true?

Gemini532

ASKER

<<consider storing the Connection information in a config file so that servername, databasename, userid, and password cannot be ascertained by viewing the script

How do I do that?
What's a config file?

SOLUTION

dqmq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gemini532

ASKER

Hi dqmq: I am not using .NET, just ASP(VB Script)

This probably explains a lot, our plan was to create a virtual file with the wizard in IIS and set the connection and any sensitive information outside the webserver....
But if we set the conn.asp file ouside the webserver, can it be created with IIS?
Is that similar to a config file?

dqmq

conn.asp is OK