vb net store multiple datareader results into one string variable

Question

i have a query like SELECT ITEM FROM DETAIL WHERE TID="1". that will return the results like

m4, c1, f2, d5, k2

i'm using DATAREADER to get the multiple value results

and here's the code

Dim res as string = "SELECT ITEM FROM DETAIL WHERE TID='1'"
CMD = New MySqlCommand(res, con)
result = CMD.ExecuteReader()
while result.HasRows
  result.Read()
  array(indeks) = result("ITEM")
end while

now instead storing results one by one into each array's index,

array(0)=m4

array(1)=c1,.....

i want to store all of that into single string variable with format like 'm4', 'c1', 'f2', 'd5', 'k2'

the format is a single quote and commas(,) as separator for each results like example above (the commas only appear if there's still a result)

how could i do that in vb.net? i'm using mysql as database

UPDATED CODE

    Dim cnt As String = "select count(*) from detail where kode_faktur= '" & 1 & "' "
    Dim max As Int32
    CMD_sup = New MySqlCommand(cnt, conn.konek)
    max = Convert.ToInt32(CMD_sup.ExecuteScalar())
    CMD_sup.Connection.Close()

    Dim result As MySqlDataReader

    Dim resultString As String
    Dim isFirstResult = True

    Dim arayITEM() As String
    Dim res As String = "select kode_brg from detail where kode_faktur= '" & 1 & "' "
    CMD = New MySqlCommand(res, conn.konek)
    result = CMD.ExecuteReader()


    ReDim arayITEM(max)
    If result.HasRows Then
        For i As Integer = 0 To max - 1
            result.Read()
            arayITEM(i) = result("kode_brg")
        Next
    End If

    resultString = "'" & String.Join("','", arayITEM) & "'"
    'MsgBox("HASIL : " & resultString)

here's the screenshoot

i don't need separator at the end of last array's element (,'')

Answer 1

How about this :

Dim res as string = "SELECT ITEM FROM DETAIL WHERE TID='1'"
CMD = New MySqlCommand(res, con)
result = CMD.ExecuteReader()
Dim resultString as String = ""
Dim isFirstResult = True
while result.HasRows
  result.Read()
  If Not isFirstResult Then 
    resultString &= string.Format(",'{0}'",result("ITEM"))
  Else 
    isFirstResult = False
    resultString &= string.Format("'{0}'",result("ITEM"))
  End If
end while

Or if you want to keep using the array but also need the single string version, you can convert the array using String.Join :

Dim resultString As String = "'" & String.Join("','", array) & "'"

String.Join is clever enough to add separator only if next element exists. So both approach above should produce the same result.