Run-time error ''-2147217887''(80040e21) Multiple-step operation generated errors. Check each status value
-
Help! I encountered an error msg above during updating a field in my ADO recordset.
I've use ADO connection as below,
dim adoCon as new ADODB.CONNECTION
dim sqlupdate as String
set rs = new ADODB.RECORDSET
set adoCon = NEW ADODB.CONNECTION
sqlupdate = "select 0 as ticked, * from aTable where..."
With adoCon
.Provider = "MSDASQL"
.ConnectionString = "driver={SQL Server};Server=" & ServerName & ...rs.CursorLocation = adUseClient
rs.Open sqlupdate,adoCon,adOpenKeyset, adLockOptimistic
rs("ticked") = true <- "multipe Multiple-step operation generated errors. " -
This is not an actual column in the database, and therefore cannot be updated in the db.
-
but it was running fine on MS Access db until I convert it into MS SQL Server...how come?
-
Can't answer this one... But it's not something I've been able to do with Access ADPS (sql server back end). I'll see what I can dig up tomorrow (pls reply to this msg so I don't forget)
-
ok. Thanks...
-
I once had something like this. The problem was that the number of affected rows was returned with resultset coming from the database, when I added SET NOCOUNT ON to the top of the stored procedure it solved my problem back then, might wanna give it a try ...
-
Try running SQL Profiler when executing your code. See what the ADO recordset is trying to tell SQL Server - I would assume that it is issuing an update statement for the ticked column, which as Remi said, does not exist (or if you do have a column called ticked, it is a constant).
Are you hoping to have a column called ticked in your client-side recordset and when you update the ticked column, you just want it to change locally so you can later iterate through and get the "checked" rows for example?
-
With the 'no count' off the 'x records affected' message would be part of the result set and while the cursor location is set to client, you would have like 2 open recordsets.
Add the 'SET NOCOUNT ON' to the stored proc. to avoid the 2nd resultset or change the cursor location to server.
-
run this code, I think it reproduces the error exactly :
Sub x()
On Error GoTo Gestion
Dim MyRs As ADODB.Recordset
Set MyRs = New ADODB.Recordset
MyRs.Open "Select 1 as Test, id, name from dbo.SysObjects", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If Not MyRs.EOF Then
MyRs.Fields("Test").Value = 0
End If
MyRs.Close
Set MyRs = Nothing
Exit Sub
Gestion:
MsgBox Err.Description & " : " & Err.Number
End Sub
I also checked for the server-side error, but using the profiler I realized that access doesn't even goes back to the server before throwing the error.
-
Are you sure the error isn't provider specific? Why aren't you using the SQLOLEDB provider?
I've written many apps designed to work on both SQL and Access using ADO, the only difference should be (other than changing a few select statements, Access doesn't support the same joins as T-SQL) the provider and connection string.
The MSDASQL provider is older and uses ODBC as an intermediary layer. You should try and use the SQLOLEDB provider where you can, it does not use ODBC.
With the SQLOLEDB provider your connection string should read something like this :
"data source = {server name}, initial catalog = {database}, user id = {user name}, password = {password}"
Like I said, there should be very little difference between using an Access database, or a SQL database, if you are using ADO as your interface. The SQL statements will differ, as Access doesn't support T-SQL but rather its own stripped down version of SQL. Other than that you should only have to specify a new provider and a different connection string.
I've attached below a copy from BOL on the difference between the SQLOLEDB provider and the MSDASQL provider.
- Microsoft OLE DB Provider for SQL Server (SQLOLEDB), which maps OLE DB interfaces and methods over SQL Server data sources.
- Microsoft OLE DB Provider for ODBC (MSDASQL), which maps OLE DB interfaces and methods to ODBC APIs. OLE DB consumers connect to an instance of SQL Server using the SQL Server ODBC driver as an intermediary layer.
-
I had a similar issue and resolved it by setting the destination table fields to varchar(255). For some unknown reason my brain told me to set each value to the same settings as in the source. Bad Brain...
Every field that was updated, (151 of them), was entered with the max value of 256 characters and all was well with the world, or at least the app. The Audit table now has proper data in it.
Yes I am lazy...and in a hurry...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply