CODEWITHSUNDEEP
mysqlvb.netvisual-studio-2010
Anna Riekic
Anna Riekic

Reputation: 738

vb.net/mysql show more then 1 row in TextBox

I just started messing around with Visual basic (vb.net) and am trying to show more then 1 database row in a TextBox, so far I have this:

Private Sub foobox_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        Dim conn As MySqlConnection

        conn = New MySqlConnection
        conn.ConnectionString = connStr

        Try
            conn.Open()
        Catch myerror As MySqlException
            MsgBox("No connection")
        End Try

        Dim myAdaptor As New MySqlDataAdapter

        Dim sqlquery = "SELECT * FROM foo ORDER BY id DESC"
        Dim myCommand As New MySqlCommand()
        myCommand.Connection = conn
        myCommand.CommandText = sqlquery

        myAdaptor.SelectCommand = myCommand
        Dim myData As MySqlDataReader
        myData = myCommand.ExecuteReader()

        If myData.HasRows Then
            myData.Read()
            Viewer.Text = myData("foo1") & myData("foo2")

        End If

        myData.Close()
        conn.Close()

    End Sub

which connects to a database successfully but but it only outputs 1 row, how can I get it to output more?

Upvotes: 0

Views: 2753

Answers (2)

Steve
Steve

Reputation: 216303

You need a loop reading data and storing line after line in a StringBuilder.
Then, when exiting from the reading loop set the Text property of your textbox

    Dim sb as StringBuilder = new StringBuilder()
    While myData.Read()
        sb.AppendLine(myData("foo1") & myData("foo2"))
    End While
    Viewer.Text = sb.ToString

and, of course, your textbox should have the MultiLine property set to True

Apart from this direct answer to your question, your code should be changed to dispose the connection and the datareader after use, I have also removed the DataAdapter because is not needed here

    Using conn = New MySqlConnection(connStr)
        Try
            conn.Open()
        Catch myerror As MySqlException
            MsgBox("No connection")
        End Try

        Dim sqlquery = "SELECT * FROM foo ORDER BY id DESC"
        Dim myCommand As New SqlCommand(sqlquery, conn)
        Using myData = myCommand.ExecuteReader()
            Dim sb as StringBuilder = new StringBuilder()
            While myData.Read()
                sb.AppendLine(myData("foo1") & myData("foo2"))
            End While
            Viewer.Text = sb.ToString        
        End Using
    End Using

Upvotes: 2

Tim Schmelter
Tim Schmelter

Reputation: 460158

You need some kind of loop. I would also use the Using statement to ensure that all unmanaged resources are disposed even in case of an exception(it also closes the connection):

Using conn As New MySqlConnection(connStr)
    Using myCommand As New MySqlCommand("SELECT * FROM foo ORDER BY id DESC", conn)
        Try
            conn.Open()
            Using myData = myCommand.ExecuteReader()
                If myData.HasRows Then
                    While myData.Read()
                        Dim line = String.Format("{0}{1}{2}",
                                                 myData.GetString(myData.GetOrdinal("foo1")),
                                                 myData.GetString(myData.GetOrdinal("foo1")),
                                                 Environment.NewLine)
                        viewer.Text &= line
                    End While
                End If
            End Using
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Using
End Using

However, if you want to show multiple records i would recommend a ListBox instead. It's more efficient with many items and it also separates them logical from each other.

( just replace viewer.Text &= line with ListBox1.Items.Add(line) )

Upvotes: 1

Related Questions