Reputation: 135
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 (,''
)
Upvotes: 0
Views: 4494
Reputation: 2279
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.
Upvotes: 2
Reputation: 89285
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.
Upvotes: 1