CODEWITHSUNDEEP
sqlsql-servervb.net
user3503267
user3503267

Reputation: 13

Combine value from sqldatareader

i want to combine(concatenate) all row that come from a select statement, how can i do that please? i tried one but it return on the first record but i want the all rows into one single string can someone help please

If IsNumberExist() Then
   Using conss As New SqlConnection(_start)
       Dim sql4 As String = "SELECT Film.Title + ' ,   ' + Film.ParentControl + ', ' + '     SALLE : ' + Program.Venue +  ' ,' +'    SEAT AVAILABLE :' +  Cast(Program.Seat as varchar) + ' , ' +  '   SEAT PRICE :  Rs ' + Cast(Film.Price as varchar) AS Expr1 FROM Program INNER JOIN Film ON Program.FilmID = Film.FilmID"
       Dim myCommand1 = New SqlCommand(sql4, conss)

       conss.Open()

       Dim reader As SqlDataReader = myCommand1.ExecuteReader

       If reader.HasRows Then
          While reader.Read
              msg = reader.GetString(0)
          End While

          Dim message As String = msg
          Response.Redirect("http://localhost:9333/Ozeki?login=admin&password=abc123&action=sendMessage&messagetype=SMS&recepient=++23057864516&messageData=" + message)
      End If
   End Using
End If

Upvotes: 0

Views: 1288

Answers (2)

Markus
Markus

Reputation: 22456

You can concatenate the strings as follows. In the following sample, I collect the parts in a list and concatenate them with String.Join (using ", " as the separator for the parts, but you can change this as you like):

Using reader As SqlDataReader = myCommand1.ExecuteReader
    If reader.HasRows Then
        Dim parts As New List(Of String)()
        While reader.Read
            parts.Add(reader.GetString(0))
        End While
        Dim message As String = String.Join(", ", parts)
        Response.Redirect("http://localhost:9333/Ozeki?login=admin&password=abc123&action=sendMessage&messagetype=SMS&recepient=++23057864516&messageData=" + message)
    End If
End Using

Please note that I've added a Using statement for the SqlDataReader.

Upvotes: 1

D Stanley
D Stanley

Reputation: 152556

You are overwriting msg each time. Use

msg = msg + Environment.NewLine + reader.GetString(0)

instead.

Or use a StringBuilder:

Dim sb As new StringBuilder()
While reader.Read
    sb.AppendLine(reader.GetString(0))
End While 

Dim message As String = sb.ToString()

Upvotes: 0

Related Questions