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

enter image description here

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

Have you tried string concatenation? – chris_techno25 Jan 25 '14 at 06:57 — chris_techno25, Jan 25 '14 at 06:57

score 2 · Answer 1 · edited May 23 '17 at 12:15

Dim res as string = "SELECT ITEM FROM DETAIL WHERE TID='1'"
CMD = New MySqlCommand(res, con)


' Read data from database
Dim result As New ArrayList()
Dr = CMD.ExecuteReader()

' Add each entry to array list
While Dr.Read()
    ' Insert each column into a dictionary
    Dim dict As New Dictionary(Of String, Object)
    For count As Integer = 0 To (Dr.FieldCount - 1)
        dict.Add(Dr.GetName(count), Dr(count))
    Next

    ' Add the dictionary to the ArrayList
    result.Add(dict & ", ")
End While
Dr.Close()

So, now you could loop through result with a for loop like this:

For Each dat As Dictionary(Of String, Object) In result
     Console.Write(dat("ColName"))
Next

Quite similar to how you would do it if it were just the DataReader:

While Dr.Read()
    Console.Write(Dr("ColName"))
End While

Code from : Reference I have modified it as what you want, but without testing. hope can help you.

score 1 · Accepted Answer · answered Jan 25 '14 at 07:36

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.

answered Jan 25 '14 at 07:36

har07

88,338
12
84
137

thx for the answer, bro. i only need the values to be stored into single string. when i tried to display it using msgbox `MsgBox("Result: " & resstring)`. the program hangs and the error popped up. And what is the `boolean` variable role here? could you please explain it to me? – Soni Gunz Jan 25 '14 at 07:54
`isFirstResult` is to avoid adding preceding comma (',') for the first result. It should only be added to 2nd-last result. – har07 Jan 25 '14 at 07:57
and what is the error message? I can't guess, it kinda work fine here. I tested by looping through list of string though, instead of looping through DataReader – har07 Jan 25 '14 at 08:00
here's the error `Value of type 'System.Collections.Generic.List(Of String)' cannot be converted to '1-dimensional array of String'`. and the code is on my post – Soni Gunz Jan 25 '14 at 08:08
more logical thing to try: change this `String.Join("','", aray)` to this `String.Join("','", aray.ToArray())` – har07 Jan 25 '14 at 08:20
I tested with .NET 4.5, maybe in older version of .NET String.Join only accept parameter of type Array not List or other IEnumerable collection – har07 Jan 25 '14 at 08:25
i've modified my code using array now not `LIST`. but it still getting error.. logically, `STRING JOIN` should be correct for joining the array. i've updated my code again on the post. could you please take a look at them again? – Soni Gunz Jan 25 '14 at 08:30
I checked your updated post. Still the same error message? If yes, I don't have any idea it should work fine, or try to stick with `List` but call `aray.ToArray()` as I mentioned above. Otherwise may I know what the error message now? – har07 Jan 25 '14 at 08:39
this is the error `"Object reference not set to an instance of an object."` – Soni Gunz Jan 25 '14 at 08:45
just try with `List` as I mentioned above bro, I'm sure that will work. The latest error because your array has not been initialized yet – har07 Jan 25 '14 at 08:55
1

i've fixed this.. it turned out that i forget to `redim` the array. that's why the `datareader` can't fill the array with values. **thx for the help like always, bro (y)** – Soni Gunz Jan 25 '14 at 08:56
Doesn't matter. Just my opinion, but dealing with `List` is more convenient than `Array` I mostly avoid using `Array` :p – har07 Jan 25 '14 at 09:06
how to make the separator stops when there's no more array element. i've updated my code and ss on my first post. sorry for the trouble again.. – Soni Gunz Jan 25 '14 at 11:09
that means you have empty string in the last array element. in `while` loop you can add checking like : `If Not String.IsNullOrEmpty(result("kode_brg")) Then arayITEM(i) = result("kode_brg")` to prevent adding empty string to the array – har07 Jan 25 '14 at 11:18
@har07.. thx u bro, you're right.. i recheck my `ReDim statement` and it turned out that i have set the array's capacity more than it should be, which resulting in 'extra empty element'..:) – Soni Gunz Jan 25 '14 at 11:52
I see, that's another reason to consider replacing array with list :D – har07 Jan 25 '14 at 11:55

vb net store multiple datareader results into one string variable

2 Answers2