adOpenStatic Logo
Navigation
Home
FAQ
Ken's Blog
Resources
Contact Ken
Copyright 2000 -



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 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 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;" & _
   "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 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'
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 database does not support adDBDate, using the following code would generate an error:

<%
objCommand.CreateParameter("@DateStamp", adDBDate, adParamOutput)
%>

Back to FAQ listing.