Populating Listbox with data from SQL Server

Question

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

Code looks fine - could be slightly improved by declaring a DataTable (instead of DataSet) and fill that data table, since you only ever needs one single table - but that's not going to make a huge difference.... was there a lot of other activity on the server at this point in time? Was that table e.g. locked by a large INSERT statement or something like that? — marc_s
– marc_s, Commented Jan 30, 2013 at 6:25
@Behroz - it's quite large, 188,874 rows that is the total rows — Danjor
– Danjor, Commented Jan 30, 2013 at 6:27
@Danjor: added an answer to show how to use a DataTable instead of DataSet — marc_s
– marc_s, Commented Jan 30, 2013 at 6:36

SQLGuru · Accepted Answer · 2013-01-30 06:11:53Z

4

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.

answered Jan 30, 2013 at 6:11

SQLGuru

1,0975 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Danjor Over a year ago

error occured : 'dataTextField' is not a member of 'System.Windows.Forms.ListBox'

SQLGuru · Accepted Answer · 2013-01-30 06:43:58Z

4

apologies... thought is was a web application.

Try this:

ListBox1.DataSource = myDataTable 
ListBox1.DisplayMember = "ColumnName"

answered Jan 30, 2013 at 6:43

SQLGuru

1,0975 silver badges14 bronze badges

15 Comments

Danjor Over a year ago

I tried it, it works but the problem is I want to display all the column to the ListBox1 so I tried to put it this way: ListBox1.DisplayMember = "ControlNo,EmpNo,CheckOutDate,CheckOutTime,TaxiNo,PlateNo,Model,Make" and there is a display message in ListBox1 'System.Data.DataRowView'

Danjor Over a year ago

I tried to used atleast 2 columns it shows the same message, can you help me what to do about it?

SQLGuru Over a year ago

in your SQL query, build up a column of all the values. then, use that column for this. e.g. SELECT blah1, blah2, blah1 + ' ' + blah2 as FinalBlah FROM tablename then use FinalBlah as your value for the listbox - make sense?

Danjor Over a year ago

wahh! it's hard to understand! what is the 'blah!' 'blah!' :(

SQLGuru Over a year ago

SELECT col1, col2, col1 + ' ' + col2 as FinalCol FROM tablename. This help?

|

Collectives™ on Stack Overflow

Populating Listbox with data from SQL Server

2 Answers 2

1 Comment

15 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

15 Comments

Your Answer

Sign up or log in

Post as a guest

Related