Data Reader issue with images inside selector

Question

I took reference from below Ruslan post and implemented same and working as expected but sometimes, am facing an issue saying like "An error occurred during processing of the field ItemRequiresTerms: There is already an open DataReader associated with this Command which must be closed first."

How to show images inside selector lookup?

One Stock Item may have multiple images and we need only icon images. Below is the code.

public void InventoryItemRowSelecting(PXCache sender, PXRowSelectingEventArgs e)
    {
        var row = e.Row as InventoryItem;
        if (row != null)
        {
            if (!string.IsNullOrEmpty(row.ImageUrl))
            {
                foreach (NoteDoc noteDoc in PXSelectReadonly<NoteDoc, Where<NoteDoc.noteID, Equal<Required<NoteDoc.noteID>>>>.Select(Base, row.NoteID)) // here i got error
                {
                    foreach (UploadFile uploadFile in PXSelectReadonly<UploadFile, Where<UploadFile.fileID, Equal<Required<UploadFile.fileID>>>>.Select(Base, noteDoc.FileID))
                    {
                        if (uploadFile.Name.Contains("icon"))
                        {
                            row.ImageUrl =
                            ControlHelper.GetAttachedFileUrl(null, uploadFile.FileID.ToString());
                            break;
                        }
                    }
                }
            }
        }
    }

DChhapgar · Accepted Answer · 2018-08-31T14:12:30.200

You need to use separate connection scope to execute additional BQL statements within a RowSelecting event handler.

More details can be found here.

You can avoid nested for loops by utilizing proper Join,

public void InventoryItemRowSelecting(PXCache sender, PXRowSelectingEventArgs e)
{
    var row = e.Row as InventoryItem;
    if (row != null)
    {
        if (!string.IsNullOrEmpty(row.ImageUrl))
        {
            using (new PXConnectionScope())
            {
                UploadFile uploadFile = PXSelectReadonly2<UploadFile, InnerJoin<NoteDoc, On<NoteDoc.fileID, Equal<UploadFile.fileID>>>,
                                                Where<NoteDoc.noteID, Equal<Required<NoteDoc.noteID>>,
                                                And<UploadFile.name, Like<Required<UploadFile.name>>>>>.
                                                Select(Base, row.NoteID, "%icon%");
                row.ImageUrl = (uploadFile != null) ? ControlHelper.GetAttachedFileUrl(null, uploadFile.FileID.ToString()) 
                                                        : null;
            }
        }
    }
}

Parrish Husband · Answer 2 · 2018-08-31T19:25:34.250

Already an accepted answer here, but I wanted to show you an alternative to the deeply indented structure you are using in your example code.

public void InventoryItemRowSelecting(PXCache sender, PXRowSelectingEventArgs e)
{
    var row = e.Row as InventoryItem;
    if (row == null)
        return;  // Don't proceed when row doesn't exist

    if (string.IsNullOrEmpty(row.ImageUrl))
        return; // Don't proceed when ImageUrl doesn't exist

    foreach (NoteDoc noteDoc in PXSelectReadonly<NoteDoc, Where<NoteDoc.noteID, Equal<Required<NoteDoc.noteID>>>>.Select(Base, row.NoteID)) // here i got error
    {
        foreach (UploadFile uploadFile in PXSelectReadonly<UploadFile, Where<UploadFile.fileID, Equal<Required<UploadFile.fileID>>>>.Select(Base, noteDoc.FileID))
        {
            if (!uploadFile.Name.Contains("icon"))
                continue;  // Skip non-icon files

            row.ImageUrl = ControlHelper.GetAttachedFileUrl(null, uploadFile.FileID.ToString());
        }
    }
}

Note how you use more the code page instead of pushing everything over to the right. This is your original example, so it doesn't have the fixes from the accepted answer, I just wanted to show you a possible alternate pattern to consider in the future.

This also is a dramatic change when you apply this to the accepted solution:

public void InventoryItemRowSelecting(PXCache sender, PXRowSelectingEventArgs e)
{
    var row = e.Row as InventoryItem;
    if (row == null)
        return;  // Don't proceed when row doesn't exist

    if (string.IsNullOrEmpty(row.ImageUrl))
        return; // Don't proceed when ImageUrl doesn't exist

    using (new PXConnectionScope())
    {
        UploadFile uploadFile = PXSelectReadonly2<UploadFile, InnerJoin<NoteDoc, On<NoteDoc.fileID, Equal<UploadFile.fileID>>>,
                                    Where<NoteDoc.noteID, Equal<Required<NoteDoc.noteID>>,
                                    And<UploadFile.name, Like<Required<UploadFile.name>>>>>.
                                    Select(Base, row.NoteID, "%icon%");
        if (uploadFile == null)
            continue; // Skip non-icon files         

        row.ImageUrl = ControlHelper.GetAttachedFileUrl(null, uploadFile.FileID.ToString()); 
    }
}

Data Reader issue with images inside selector

2 Answers2