Correcting Error 3265 Item Cannot be Found in the Collection Accessing Vendor Documents in SedonaOffice

04/26/2024 2:51 pm EDT

Description of Issue:

When attempting to open documents or add documents to Any Vendor records or transactions an error is returned.

How can this be corrected?

Resolution:

This error can be caused by the Credit_Id field not being found in the AP_Vendor_Document table.

You will need the add the field to the table using the scripts below.

The stored procedures will also need to be updated to be sure they include the added field.

To see if this is the issue, run the script below. You should see the Credit_id at the end of the row.

Use SedonaDocuments 
Select top 1  * From AP_Vendor_Documents

If the field is not in the results, run this first set of scripts to add the field to the table.

--- Add Credit_id field to AP_Vendor_Documents ---  
--- Users will see errors in AP trying to view or add documents in AP applications --- 
--- Error 3265 Item cannot be found in the collection corresponding to the requested Name or ordinal ---  

Use SedonaDocuments  

--- Add field to the table ---  
ALTER TABLE AP_Vendor_Documents ADD Credit_Id int NOT NULL  

Go 
--- Add the Default value for the field when new record is created ---  
ALTER TABLE [dbo].[AP_Vendor_Documents ] ADD DEFAULT ((1)) FOR [Credit_Id]  

Go  
--- Update field with the default value of 1 ---update AP_Vendor_Documents 
set credit_id =1 
where credit_ID is null  

Go 
--- Update the field to not Allow Nulls after the default value has been populated. ---  

ALTER TABLE AP_Vendor_Documents ALTER COLUMN Credit_Id INT NOT NULL

Run this second set of scripts to drop and recreate the stored procedures that access this table.

Drop and recreate the Vendor_Documents_GET stored procedure.

Use SedonaDocuments  

DROP PROCEDURE IF EXISTS dbo.Vendor_Documents_GET 
GO  

USE [SedonaDocuments] 
GO  

/****** Object:  StoredProcedure [dbo].[Vendor_Documents_GET]    Script Date: 8/5/2022 8:33:20 AM ******/ 
SET ANSI_NULLS ON 
GO  

SET QUOTED_IDENTIFIER ON 
GO    

CREATE PROCEDURE [dbo].[Vendor_Documents_GET]
 @company_id        int,
     @vendor_id        int,
 @document_id    int   
AS
 IF(@document_id = 0)
 BEGIN SELECT doc.Document_Id, doc.SedonaCompany_Id, doc.Vendor_Id, doc.Security_Level, doc.FileName, doc.FileSize, doc.UploadDate,    typ.Document_Extension, typ.Document_Type_Description, typ.Type_US, doc.UserDescription, doc.UserCode, doc.Invoice_Id,   doc.Check_Id, doc.Purchase_Order_Id, doc.Receipt_Id, doc.Reference_1, doc.Reference_2, doc.Reference_3, Credit_Id 
FROM AP_Vendor_Documents doc 
INNER JOIN SY_Document_Type typ ON typ.Document_Type_Id = doc.Document_Type_id 
WHERE doc.SedonaCompany_Id = @company_id AND doc.Vendor_Id = @vendor_id
     END
 ELSE
 BEGIN
 SELECT doc.Document_Id, doc.SedonaCompany_Id, doc.Vendor_Id, doc.Security_Level, doc.FileName, doc.FileSize, doc.UploadDate,     typ.Document_Extension, typ.Document_Type_Description, typ.Type_US, doc.UserDescription, doc.UserCode, doc.Invoice_Id,    doc.Check_Id, doc.Purchase_Order_Id, doc.Receipt_Id, doc.Reference_1, doc.Reference_2, doc.Reference_3, Credit_Id
 FROM AP_Vendor_Documents doc
 INNER JOIN SY_Document_Type typ ON typ.Document_Type_Id = doc.Document_Type_id 
WHERE doc.SedonaCompany_Id = @company_id AND Vendor_Id = @vendor_id AND doc.Document_Id = @document_id
     END     
GO

Drop and recreate the Vendor_Document_ADD stored procedure.

Use SedonaDocuments  
DROP PROCEDURE IF EXISTS dbo.Vendor_Document_ADD 
GO  

USE [SedonaDocuments] 
GO  

/****** Object:  StoredProcedure [dbo].[Vendor_Document_ADD]    Script Date: 8/5/2022 8:28:29 AM ******/ 
SET ANSI_NULLS ON 
GO  

SET QUOTED_IDENTIFIER ON 
GO   

CREATE PROC [dbo].[Vendor_Document_ADD]
     @company_id            int,
 @vendor_id            int,
 @security_level        int,
 @file_name            nvarchar(255),
 @file_size        int,
 @upload_date        datetime,
 @document_ext        nvarchar(25),
        @user_code        nvarchar(25),
 @user_description    nvarchar(50),
 @reference1        nvarchar(50),
 @reference2        nvarchar(50),
 @reference3        nvarchar(50),
 @file_data        image,
 @invoice_id        int,
 @check_id        int,
 @purchase_order_id    int,
 @receipt_id        int,
 @credit_id        int = 1,
 @document_id        int OUTPUT
     AS
      DECLARE @doc_type_id    int 
EXEC Get_Document_Type_Id @document_ext, @doc_type_id OUTPUT  

IF @doc_type_id = 1
 BEGIN
 INSERT SY_Document_Type(Document_Extension, Document_Type_Description, Type_US)
 VALUES(@document_ext, @document_ext, 'U')
 SET @doc_type_id = SCOPE_IDENTITY()
 END      

SELECT @document_id = 0            

INSERT AP_Vendor_Documents(SedonaCompany_Id, Vendor_id, Security_Level, FileName, FileSize, FileData, UploadDate, Document_Type_Id, UserCode, UserDescription  , Reference_1, Reference_2, Reference_3, Invoice_Id, Check_Id, Purchase_Order_Id, Receipt_Id, Credit_Id)
     VALUES(@company_id, @vendor_id, @security_level, @file_name, @file_size, @file_data, @upload_date, @doc_type_id, @user_code, @user_description   , @reference1, @reference2, @reference3, @invoice_id, @check_id, @purchase_order_id, @receipt_id, @credit_id)

       IF @@error = 0
        BEGIN
 SELECT @document_id = @@IDENTITY
         END   

RETURN 
GO

Drop and recreate the Vendor_Document_UPD stored procedure.

Use SedonaDocuments  

DROP PROCEDURE IF EXISTS dbo.Vendor_Document_UPD 
GO  

USE [SedonaDocuments] 
GO  

/****** Object:  StoredProcedure [dbo].[Vendor_Document_UPD]    Script Date: 8/5/2022 8:30:07 AM ******/ 
SET ANSI_NULLS ON 
GO  

SET QUOTED_IDENTIFIER ON 
GO    


CREATE PROC [dbo].[Vendor_Document_UPD]
 @operation_type        int,
 @document_id        int,
 @security_level        int,
 @file_name            nvarchar(255),
 @file_size            int,
 @upload_date        datetime,
 @document_ext        nvarchar(25),
     @user_code            nvarchar(25),
 @user_description    nvarchar(50),
 @reference1            nvarchar(50),
 @reference2            nvarchar(50),
 @reference3            nvarchar(50),
 @invoice_id            int,
 @check_id            int,
 @purchase_order_id    int,
 @receipt_id            int,
 @credit_id            int = 1,
 @file_data            image = default    

AS

  DECLARE @doc_type_id    int
  EXEC Get_Document_Type_Id @document_ext, @doc_type_id OUTPUT

  IF @operation_type = 0
  BEGIN
         UPDATE AP_Vendor_Documents'
         SET     Security_Level = @security_level, 
                 FileName = @file_name, 
                 FileSize = @file_size, 
                 UploadDate = @upload_date, 
                 Document_Type_Id = @doc_type_id,  
                 UserCode = @user_code, 
                 UserDescription = @user_description, 
                 Invoice_Id = @invoice_id, 
                 Check_Id = @check_id, 
                 Purchase_Order_Id = @purchase_order_id, 
                 Reference_1 = @reference1, 
                 Reference_2 = @reference2,  
                 Reference_3 = @reference3, 
                 Credit_Id = @credit_id
 WHERE  Document_Id = @document_id         
END     
IF @operation_type = 1
          BEGIN
 UPDATE AP_Vendor_Documents
 SET     Security_Level = @security_level, 
FileName = @file_name, 
FileSize = @file_size, 
UploadDate = @upload_date, 
Document_Type_Id = @doc_type_id,  
UserCode = @user_code, 
UserDescription = @user_description, 
FileData = @file_data, 
Invoice_Id = @invoice_id, 
Check_Id = @check_id, 
Purchase_Order_Id = @purchase_order_id, 
Reference_1 = @reference1, 
Reference_2 = @reference2, 
Reference_3 = @reference3, 
Credit_Id = @credit_id 
WHERE Document_Id= @document_id          
END  

IF @@error <> 0  
BEGIN 
RETURN 
END

 GO

Was this article helpful?