Odd ADODB.Parameters error 3265 "Item cannot be found in the collection corresponding
Greetings,
My Access 2007 app to replicate records from an AS/400 running JDE to SQL Server 2008 R2 received some very odd error messages. A sample is as follows:
Searching around the Internet for clues / ideas, I came across this page:
"ACC2000: How to Use Parameters with ActiveX Data Objects (ADO) and Jet"
http://support.microsoft.com/kb/225897
That suggested wrapping the Parameters Values in double quotes. I tried that and testing the code in Debug / Trace mode, that blows up right away.
With the quotes removed, in Debug / Trace mode the code executes perfectly to insert records which previously failed.
Selecting only one of the records (without Debug / Trace mode) which had an error, that does complete correctly.
The Access VBA code driving the SP is as follows:
There were some dupe records, and those clearly state that the SQL Server index is what prevented those records from being accepted. This error, gives me little reason as to why the INSERT was not successful.
Any suggestions?
Greetings,
My Access 2007 app to replicate records from an AS/400 running JDE to SQL Server 2008 R2 received some very odd error messages. A sample is as follows:
Code:
Date: 20111115 Time: 10:05:39 UserID: fandango
AppErrorMsg: Class: clsObjFRouterTbl, Function: Insert(), Values: partnumber='1000021008', wrkctr='119051', iropsq='1000'
Error Source: ADODB.Parameters
Error Number: 3265
Error Description: Item cannot be found in the collection corresponding to the requested name or ordinal.
MessageText: Error not found."ACC2000: How to Use Parameters with ActiveX Data Objects (ADO) and Jet"
http://support.microsoft.com/kb/225897
That suggested wrapping the Parameters Values in double quotes. I tried that and testing the code in Debug / Trace mode, that blows up right away.
With the quotes removed, in Debug / Trace mode the code executes perfectly to insert records which previously failed.
Selecting only one of the records (without Debug / Trace mode) which had an error, that does complete correctly.
The Access VBA code driving the SP is as follows:
Code:
Public Function Insert() As Boolean
On Error GoTo Err_Insert
Dim adoCMD As ADODB.Command
Dim adoRS As ADODB.Recordset
'Define attachment to database table specifics and execute commands via With block
Set adoCMD = New ADODB.Command
With adoCMD
.ActiveConnection = ObjBEDBConnection.FADODBConnectionObj()
.CommandText = "clsObjFRouterTbl_Insert"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters("@partnumber").Value = Me.partnumber
.Parameters("@wrkctr").Value = Me.wrkctr
.Parameters("@iropsq").Value = Me.iropsq
.Parameters("@irdsc1").Value = Me.irdsc1
.Parameters("@qtyper").Value = Me.qtyper
.Parameters("@fixture").Value = Me.fixture
.Parameters("@irefff").Value = Me.irefff
.Parameters("@irefft").Value = Me.irefft
.Parameters("@irrunl").Value = Me.irrunl
.Parameters("@irsetl").Value = Me.irsetl
.Parameters("@irvend").Value = Me.irvend
.Parameters("@irpoy").Value = Me.irpoy
Set adoRS = .Execute()
End With
'Return the new record's ID
Insert = True
Exit_Insert:
'Clean up the connection to the database
Set adoCMD = Nothing
Set adoRS = Nothing
Exit Function
Err_Insert:
Call errorhandler_Logger("Class: clsObjFRouterTbl, Function: Insert(), Values: partnumber='" & Me.partnumber & "', wrkctr='" & Me.wrkctr & "', iropsq='" & Me.iropsq & "'")
Insert = False
Resume Exit_Insert
End FunctionAny suggestions?
