[RESOLVED] How to process a record set in vb 6 reading multiple files names

[TonyR6]

hi there guys,

im having a problem where im trying to read a record set and assigning multiple file names into a textbox along with ids

okay i have a stored procedure which returns results as follows. Im only interested in attachmenid and filenames

SELECT
	AttachmentID,
	IncidentID,
	Attachment,
	FileName,
	LastUpdated,
	UpdatedBy
FROM
	[dbo].[Attachment]
WHERE
	IncidentID = @IncidentID

vb 6 code as follows

  If oConn.State = adStateOpen Then
        Set objCmd = Nothing
        With objCmd
            .CommandText = "sproc_AttachmentSelect"
            .CommandType = adCmdStoredProc
            .ActiveConnection = oConn
            .Parameters.Append _
                objCmd.CreateParameter("@IncidentID", adInteger, adParamInput, , lngLoadIndcidentDetailsFor)
        End With
        ' Execute the command
        Set rs = objCmd.Execute
    End If
    
    If Not rs.EOF Then
    
        tboAttachment.Text = rs!Filename
        strOriginalAttachment = rs!Filename
        tboAttachment.Tag = rs!AttachmentID
        btnDownloadAttachment.Visible = True
        btnDownloadAttachment.Enabled = True
    Else
        btnDownloadAttachment.Visible = False
    End If
    rs.Close

im trying to read 3 files into a text box along with their ids. but currently i can only read one file
SalesTestAzure.pdf
Sales.pdf
Sales2017N.pdf

help anyone will be much appreciated

kind regards
Tony

[LaVolpe]

You are only looking at one record. Need to loop through the recordset. But you'll have problems storing the items in a tag property for multiple IDs unless you format a specific way. Instead of a textbox, might want to consider a listbox?

An example using List1 as the listbox and a collection to store the IDs
1. At top of your project: Dim colAttachIDs As Collection
2. Now in your code above:

    Set colAttachIDs = New Collection
    List1.Clear
...
    If oConn.State = adStateOpen Then
        ...
    End If

    Do Until rs.EOF = True
        List1.AddItem rs!FileName
        colAttachIDs.Add rs!AttachmentID
        rs.MoveNext
    Loop
    rs.Close

    If colAttachIDs.Count Then ' recordset returned records
        btnDownloadAttachment.Enabled = True
        btnDownloadAttachment.Visible =True
    Else
        btnDownloadAttachment.Visible = False
        Set colAttachIDs = Nothing
    End If

If the listbox has multiselect, then you can offer multiple downloads by looping thru the listbox looking for items where .Selected(x) = True. To reference the AttachmentID for any listbox item...

  colAttachIDs(x+1) has attachment ID for List1.List(x) file name
' collections are 1-bound, first item is index 1
' listbox items are 0-bound, first item is index 0

Edited: If rs!AttachmentID is a Long/Integer value, you can store it in the List1.ItemData(x) property instead of a collection, i.e.,

    Do Until rs.EOF = True
        List1.AddItem rs!FileName
        List1.ItemData(List1.NewIndex) = rs!AttachmentID
        rs.MoveNext
    Loop

[TonyR6]

Hey there LaVolpe,

apologize for replying late,

oh yes i see, i had to make some changes from textbox to listbox. I must say its the best way to go. Textbox will limit me and it will be very tricky for reading values.

i changed the code and added some looping

please see below if it makes sense...

    Do Until rs.EOF
    List1.AddItem rs!Filename
    List1.ItemData(List1.NewIndex) = rs!AttachmentID
    colAttachIDS.Add rs!AttachmentID
    rs.MoveNext
    Loop
    
   If colAttachIDS.Count Then 'record set returned records
    
        'tboAttachment.Text = rs!Filename
        'strOriginalAttachment = rs!Filename
        'tboAttachment.Tag = rs!AttachmentID
        btnDownloadAttachment.Visible = True
        btnDownloadAttachment.Enabled = True
    Else
        btnDownloadAttachment.Visible = False
   End If

i could use List1.listcount instead of collection count?

 If List1.ListCount Then   'record set returned records
    
        'tboAttachment.Text = rs!Filename
        'strOriginalAttachment = rs!Filename
        'tboAttachment.Tag = rs!AttachmentID
        btnDownloadAttachment.Visible = True
        btnDownloadAttachment.Enabled = True
    Else
        btnDownloadAttachment.Visible = False
   End If

kind regards
Tony

[LaVolpe]

Tony, looks like it should work. But you don't need the collection now that you are storing the attachment ID in the ItemData property, do you?

Tip: You can set the listbox's Sorted property to true so that if multiple file attachments exist, they are presented in sorted order to the user. No changes would be needed when you add the file & ID to the listbox.

[TonyR6]

Hey there LaVope,

yes i nolonger need the collection, i have set the sorted property to true all is good

thanks for the help,

kind regards
Tony