Seckou Fofana Seckou Fofana - 3 months ago 67
Vb.net Question

Displaying Data from SQL to vb.net listbox

I'm trying to display all the golfer's information from TGolfers into a ListBox. But when I run my code it only displays one golfer's information.

Public Class frmGolfers

Private Sub btnDisplay_Click(sender As Object, e As EventArgs) Handles btnDisplay.Click

Dim strSelect As String = ""
Dim strName As String = ""
Dim cmdSelect As OleDb.OleDbCommand ' this will be used for our Select statement
Dim drSourceTable As OleDb.OleDbDataReader ' this will be where our data is retrieved to
Dim dt As DataTable = New DataTable ' this is the table we will load from our reader

' open the database
If OpenDatabaseConnectionSQLServer() = False Then

' No, warn the user ...
MessageBox.Show(Me, "Database connection error." & vbNewLine & _
"The application will now close.",
Me.Text + " Error",
MessageBoxButtons.OK, MessageBoxIcon.Error)

' and close the form/application
Me.Close()

End If

' Build the select statement using PK from name selected
strSelect = "SELECT * FROM TGolfers "

' Retrieve all the records
cmdSelect = New OleDb.OleDbCommand(strSelect, m_conAdministrator)
drSourceTable = cmdSelect.ExecuteReader

' load the data table from the reader
dt.Load(drSourceTable)

' populate the text boxes with the data
lbxDisplay.Items.Add(dt.Rows(0).Item(1).ToString & "," & " " & dt.Rows(0).Item(2).ToString &
ControlChars.CrLf & " " & dt.Rows(0).Item(3).ToString & " " & dt.Rows(0).Item(4).ToString & " " & dt.Rows(0).Item(5).ToString & " " & dt.Rows(0).Item(6).ToString)


' close the database connection
CloseDatabaseConnection()

End Sub
End Class

Answer

After loading the DataTable start a loop

......  
' load the data table from the reader
dt.Load(drSourceTable)

' populate the text boxes with the data
For Each row in dt.Rows
    Dim text = row(1).ToString & ", " & _
               row(2).ToString & ControlChars.CrLf & " " & _ 
               row(3).ToString & " " & _
               row(4).ToString & " " & _ 
               row(5).ToString & " " & _ 
               row(6).ToString
    lbxDisplay.Items.Add(text)
Next
......

In this way you loop over each row present in the dt.Rows collection, extract the information needed, build a string and add the string to the ListBox Items collection.

Notice that I have followed your indexing starting from Index = 1 for each column in the DataRow but keep in mind that in NET the array indexing start at index 0 not 1, so the first column of your datarow is at index 0. Change the index in code accordingly to your need. (This means that if you have 6 columns in that table then the indexing should go from 0 to 5, using the index 6 in this scenario gives an Index out of Range exception)