access里的多步oledb错误_救命呀！什么叫“多步OLE DB操作产生错误”(100分)

来源于 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.