concatinating rows returned from ADO select into a single string?

[jc]

I might be able to do this in t-sql, but is there an easy way to do
this in vb.net/ado.net?

Say my function looks like this:

Public Function GetDataSet(ByVal sql As String) As DataSet

If _connected = True Then
Dim da As New SqlDataAdapter(sql, _cnn)
Dim ds As New DataSet

da.Fill(ds)
Return ds
End If
Return Nothing

End Function


and may return a dataset with 5 rows max. I'd like to take the result
from one of the columns and concatinate them into a single string and
return that.

Thanks for any help!

 

[Rich P]

Public Function GetDataSet(ByVal sql As String) As DataSet

If _connected = True Then
Dim da As New SqlDataAdapter(sql, _cnn)
Dim ds As New DataSet

da.Fill(ds)
Return ds
End If
Return Nothing

End Function
<<

'--change your function as follows:

Public Function GetDataSet(ByVal sql As String) As String

If _connected = True Then
Dim da As New SqlDataAdapter(sql, _cnn)
Dim ds As New DataSet

da.Fill(ds, "tbl1")
dim str1 as string
for each dr as datarow in ds.Tables("tbl1").rows
if str1 <> "" then str1 &= ","
str1 &= dr("desiredCol").ToString
next
Return str1
End If
End Function


Rich

 

[rowe_newsgroups]

I might be able to do this in t-sql, but is there an easy way to do
this in vb.net/ado.net?

Say my function looks like this:

     Public Function GetDataSet(ByVal sql As String) As DataSet

            If _connected = True Then
                Dim da As New SqlDataAdapter(sql, _cnn)
                Dim ds As New DataSet

                da.Fill(ds)
                Return ds
            End If
            Return Nothing

        End Function

and may return a dataset with 5 rows max. I'd like to take the result
from one of the columns and concatinate them into a single string and
return that.

Thanks for any help!

Instead of using a dataadapter and filling a dataset, instead using a
datareader and use that to loop through the results. For the string
concatenation, you will want to use a stringbuilder for best
performance.

Thanks,

Seth Rowe [MVP]
http://sethrowe.blogspot.com/

 

[Cor Ligthert[MVP]]

jc,

I made this morning here this message, I never saw it.

\\\
Public Function GetDataSet(ByVal sql As String, ByVal wantedColumn as
integer) As String
If _connected = True Then
Dim da As New SqlDataAdapter(sql, _cnn)
Dim dt As New DataTable
Dim myString as String
da.Fill(dt)
for each dr as DataRow in Dt.Rows
myString = myString &
dr.item(wantedColumn).ToString & ";"
next
Return myString.Substring(0, myString.Length - 1)
End If
End Function
///

This is not tested and is typed here.

You are using only 5 rows, so the purpose of the StringBuilder is low,
however as you have wide columns you better can use the stringbuilder
instead of the concatination of the string.

Cor