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

*** Sent via Developersdex http://www.developersdex.com ***

[rowe_newsgroups]

On Jul 23, 5:31*pm, jc <[e-mail]:

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
"jc" <jo**@webdos.comschreef in bericht
news:ca**********************************@s50g2000 hsb.googlegroups.com...

>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!