来源于 http://www.adopenstatic.com/faq/80040e21.asp:
There are a number of causes for this type of error. Consult the folowing list try to narrowdo
wn the list of possible causes.
Scenario 1 - Error occurs when trying to insert data into a database
Scenario 2 - Error occurs when trying to open an ADO connection
Scenario 3 - Error occurs inserting data into Access, where a fieldname has a space
Scenario 4 - Error occurs inserting data into Access, when using adLockBatchOptimistic
Scenario 5 - Error occurs inserting data into Access, when using Jet.OLEDB.3.51 or ODBC driver (not Jet.OLEDB.4.0)
Scenario 6 - Error occurs when using a Command object and Parameters
Scenario 1 - Error occurs when inserting data
The error will occur at the point when you try to commit data to the database. This might be when you execute an SQL string using an ADO Connection object's .Execute() method, or when you call a Recordset's .Update() method. The error typically looks like:
Microsoft OLE DB Provider for SQL Server (0x80040E21)
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work wasdo
ne.
This error is caused when you try to insert too much data into a specified field. For example if you enter a string that is 20 characters long into a field that is defined as varChar(10), or if you try to insert a value greater than 215 (approx 2.1 billion) into an Integer field.
Scenario 2 - Error occurs when opening a connection
The error will occur when you call the .Open() method on an ADO Connection object. The error is caused by attempting to specify a value for an unsupported property in the Connection Object's connection string. For example the following connection string:
<%
strConnect = _
"Provider=Microsoft.Jet.OLEDB.4.0;" &
_
"Data Source=c:/databases/db1.mdb;" &
_
"Initial Catalog=Pubs"
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConnect
%>
...will generate an error the same as in Scenario 1. This is because the Initial Catalog property is not used when opening a connection to a Jet database - it is used when connecting to an SQL Server database.
This is not to be confused with attempting to use a property thatdo
es not exist. If the last line the the connection string above was changed from Initial Catalog=pubs to Foobar=Hello then
an entirely different error is generated. For Access/Jet the error message is:
Microsoft JET Database Engine (0x80004005)
Could not find installable ISAM
Scenario 3 - Error occurs with Access when a fieldname has a space
When using the Access ODBC driver with an adOpenForwardOnly server side cursor attempting to update the recordset by updating or inserting new data is not allowed. ADO instead, will create an SQL INSERT or UPDATE statement for you, but will neglect to place [ ] delimiters around the fieldname - causing an error. This isdo
cumented in MS KB article Q189220.
To solve this problem I'd recommend designing your database so that fieldnamesdo
not contain spaces. If this isn't possible, I recommend using the Jet OLEDB Provider. If none of these are feasible, you can either change the cursor type, or change the cursor location, or use SQL statements instead.
Scenario 4 - Error occurs with Access, when using adLockBatchOptimistic
The Microsoft Jet ODBC Drivers and OLEDB Providers only support 1 pending update when using adUseServer cursors. The error generated looks like:
Microsoft JET Database Engine error '80040e21'
Errors occurred
-or-
Microsoft OLEDB Provider For Jet (0x80040E54)
Number of rows with pending changes exceeded the limit.
To get around this problem either use a locktype other than adLockBatchOptimistic (eg adLockOptimistic), or use a client-side cursor
Scenario 5 - Error occurs with Access, when using Jet.OLEDB.3.51 or ODBC Driver
This error only occurs when using the VB/VBscript With statement, and you assign an empty string to one field, and then
an unitialised string value to the very next field eg:
<%
With objRS
.AddNew
.Fields(Field1).Value = "" ' Empty String
.Fields(field2).Value = someMadeUpVar ' Unitialised String
.Update
End With
%>
For resolution to this problem, consult MS KB Article Q228935
Scenario 6 - Error occurs when using Command Object and Parameters
This error occurs when you attempt to create a parameter on the command object, but you set an invalid ADO DataTypeEnum (see adovbs.inc for a listing of available DataTypeEnums) that is not supported by the underlying database. For example, suppose your databasedo
es not support adDBDate, using the following code would generate an error:
<%
objCommand.CreateParameter("@DateStamp", adDBDate, adParamOutput)
%>
Back to FAQ listing.
