There are a number of causes for this type of error. Consult the folowing list try to narrow down 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
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 was done.
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;" & _
Set objConn = Server.CreateObject("ADODB.Connection")
...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 that does 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 is documented in MS KB article Q189220.
To solve this problem I'd recommend designing your database so that fieldnames do 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'
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:
.Fields(Field1).Value = ""
.Fields(field2).Value = someMadeUpVar
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 database does not support adDBDate, using the following code
would generate an error:
objCommand.CreateParameter("@DateStamp", adDBDate, adParamOutput)
Back to FAQ listing.