Populating Listbox with data from SQL Server

Danjor picture Danjor · Jan 30, 2013 · Viewed 26.6k times · Source

I don't know what is happening to my program, it took me (4) four minutes to load the result of my code.... can someone tell me why? Can someone tell me how to fix this loading problem?

This is my code:

Imports System.Data.SqlClient
Public Class Form1
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim str As String = "Data Source=######;Initial Catalog=###;Persist Security Info=True;User ID=#####;Password=#####"
        Dim con As New SqlConnection(str)
        Dim cmd As String = "Select ControlNo,EmpNo,CheckOutDate,CheckOutTime,TaxiNo,PlateNo,Model,Make from dbo.ChkInOut"
        Dim adpt As New SqlDataAdapter(com, con)
        Dim myDataSet As New DataSet()
        adpt.Fill(myDataSet, "dbo.ChkInOut")
        Dim myDataTable As DataTable = myDataSet.Tables(0)
        Dim tempRow As DataRow
        For Each tempRow In myDataTable.Rows
            'ListBox1.Items.Add((tempRow("ControlNo") & " (" & tempRow("EmpNo") & ")" & " (" & tempRow("CheckOutDate") & ")" & " (" & tempRow("CheckOutTime") & ")" & " (" & tempRow("TaxiNo") & ")" & " (" & tempRow("PlateNo") & ")" & " (" & tempRow("Model") & ")" & " (" & tempRow("Make") & ")"))
            'ListBox1.Items.Add((tempRow("ControlNo") & " (" & tempRow("EmpNo") & ")"))
            ListBox1.Items.Add(tempRow("ControlNo") & "            " & tempRow("EmpNo") & "            " & tempRow("CheckOutDate") & "            " & tempRow("CheckOutTime") & "            " & tempRow("TaxiNo") & "            " & tempRow("PlateNo") & "            " & tempRow("Model") & "            " & tempRow("Make") & "            ")
        Next
    End Sub

End Class

Answer

SQLGuru picture SQLGuru · Jan 30, 2013

firstly, I must agree with the above question, how much data is being returned. In addition to that, can I suggest that rather than looping through the DataTable and populating the ListBox, you rather bind the data:

Dim myDataSet As New DataSet()
adpt.Fill(myDataSet, "dbo.ChkInOut")

ListBox1.DataTextField = "yourtext"
ListBox1.DataValueField = "yourvalue"
ListBox1.Datasource = myDataSet
ListBox1.DataBind()

This might just increase the performance.