Gemini532
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*********************
<!--#include file="adovbs.asp"-->
<%
'************************* **FIRST PART OF THE CODE*******************
dim objConn, strConnStr
set objConn = server.createObject("ADODB .Connectio n")
strConnStr = "PROVIDER=MSDASQL;DRIVER={ SQL Server}; SERVER={serverName}; DATABASE=databaseName;Inte grated 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.ActiveConnectio n = objConn
objCommand.CommandType = adCmdStoredProc
%>
‘************************* ********** ********** ********** ********** ******
When I add the SECOND PART OF THE CODE I receive an error on line 14
( set objCommand.ActiveConnectio n = objConn )
The error is:
ADODB.Command error '800a0e7d'
Operation is not allowed on an object referencing a closed or invalid connection.
/login/include/dbConnectio n.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/dbConnectio n.asp, line 14
Line 14 in this case is: .open
ORIGINAL CODE:
<!--#include file="adovbs.asp"-->
<%
dim strConnStr
strConnStr = "PROVIDER=MSDASQL;DRIVER={ SQL Server}; SERVER={SERVER NAME}; DATABASE=DatabaseName;Inte grated Security=SSPI"
'SQL AUthentication worked without any problems...
'strConnStr = "PROVIDER=MSDASQL;DRIVER={ SQL Server}; SERVER=SERVER NAME; DATABASE=DatabaseName;UID= username;P WD=passwor d;"
' Create a connection to the database
dim objConn
set objConn = server.createObject("ADODB .Connectio n")
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.ActiveConnectio n = objConn
objCommand.CommandType = adCmdStoredProc
%>
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*********************
<!--#include file="adovbs.asp"-->
<%
'*************************
dim objConn, strConnStr
set objConn = server.createObject("ADODB
strConnStr = "PROVIDER=MSDASQL;DRIVER={
objConn.ConnectionString = strConnStr
'*************************
'Create a command object for the stored procedures
dim objCommand
set objCommand = Server.CreateObject("ADODB
set objCommand.ActiveConnectio
objCommand.CommandType = adCmdStoredProc
%>
‘*************************
When I add the SECOND PART OF THE CODE I receive an error on line 14
( set objCommand.ActiveConnectio
The error is:
ADODB.Command error '800a0e7d'
Operation is not allowed on an object referencing a closed or invalid connection.
/login/include/dbConnectio
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/dbConnectio
Line 14 in this case is: .open
ORIGINAL CODE:
<!--#include file="adovbs.asp"-->
<%
dim strConnStr
strConnStr = "PROVIDER=MSDASQL;DRIVER={
'SQL AUthentication worked without any problems...
'strConnStr = "PROVIDER=MSDASQL;DRIVER={
' Create a connection to the database
dim objConn
set objConn = server.createObject("ADODB
with objConn
.ConnectionString = strConnStr
.CursorLocation = adUseClient
.open
end with
' Create a command object for the stored procedures
dim objCommand
set objCommand = Server.CreateObject("ADODB
set objCommand.ActiveConnectio
objCommand.CommandType = adCmdStoredProc
%>
You need objConn.Open before assigning the connection to the Command object.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
wow everyone got it before i did =)
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...
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
objConn.Open() '<------------- line 9
and I got this error:
Microsoft VBScript runtime error '800a01a8'
Object required: ''
login/include/dbConnection
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!!!
and this application is HUGE!!!
ASKER
it's made up of 3 smaller applcations, which are about 12-14 page applications
ASKER
It has 2 views and about 10 stored procedures
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.
<!--#include file="include/dbConnection .asp"-->
<!--#include file="include/dbConnection
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\{0 C7FF16C-38 E3-11d0-97 AB-00C04FC 2AD98}
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\SOFTWAR E\Classes\ . Under the ProgID, there will be a key named CLSID.
'************************* ********** *****THIS SECOND OPTION I DO NOT UNDERSTAND!!! :( ****************
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\{0
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\SOFTWAR
'*************************
ASKER
I found out from the DB administrator that the following line is incorrect:
strConnStr = "PROVIDER=MSDASQL;DRIVER={ SQL Server}; SERVER={serverName}; DATABASE=databaseName;Inte grated 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/dbConnectio n.asp, line 18
fake being the username of the login and SUPERFAKE being the DOMAIN
strConnStr = "PROVIDER=MSDASQL;DRIVER={
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/dbConnectio
fake being the username of the login and SUPERFAKE being the DOMAIN
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=Sup erPassword ;"
strConnStr = "PROVIDER=MSDASQL;DRIVER={
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
We tried to use Windows Authentication but apparently it only works for II6
Is this true?
Is this true?
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?
How do I do that?
What's a config file?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
conn.asp is OK
objConn.Open()