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, Jan 30 '13 at 06:25
@Behroz - it's quite large, 188,874 rows that is the total rows — Danjor, Jan 30 '13 at 06:27
@Danjor: added an answer to show how to use a `DataTable` instead of `DataSet` — marc_s, Jan 30 '13 at 06:36
@marc_s I don't know how to use it , honestly speaking I just used this codes that I got from some website and I apply it, but first I just tried it with 1 button and 1 listbox to know if it's working, and I found out that it is working but the problem is the loading of my program. — Danjor, Jan 30 '13 at 06:38
So, clearly you have alot of rows. For each row, you are using a loop and concatenating string. It will take time. You should return the concatenated string from the query and bind the results directly to the listbox. — Behroz Sikander, Jan 30 '13 at 06:57
@Behroz like I said I don't have any idea on how to do that kind of command , could you help me? — Danjor, Jan 30 '13 at 07:01
you need a WHERE clause in your SQL - as it stands at the moment you are returning the whole table — peterG, Jan 30 '13 at 11:03
@peterG is absolutely right, you need to limit the dataset. A Listbox with > 200 records starts becoming unusable. if the dataset is > 180,000 you're doing everything wrong. — simo.3792, Jul 23 '14 at 06:47

score 4 · Accepted Answer · answered Jan 30 '13 at 06:11

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.

error occured : 'dataTextField' is not a member of 'System.Windows.Forms.ListBox' — Danjor, Jan 30 '13 at 06:33

score 4 · Answer 2 · answered Jan 30 '13 at 06:43

4

apologies... thought is was a web application.

Try this:

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

answered Jan 30 '13 at 06:43

SQLGuru

1,099
5
14

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 Jan 30 '13 at 06:56
I tried to used atleast 2 columns it shows the same message, can you help me what to do about it? – Danjor Jan 30 '13 at 06:59
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? – SQLGuru Jan 30 '13 at 07:27
wahh! it's hard to understand! what is the 'blah!' 'blah!' :( – Danjor Jan 30 '13 at 07:45
SELECT col1, col2, col1 + ' ' + col2 as FinalCol FROM tablename. This help? – SQLGuru Jan 30 '13 at 07:47
what actually the ' ' for? is there a textbox in there? – Danjor Jan 30 '13 at 07:51
what i was doing there is concatenating the fields together, and the ' ' is to add a space between the two columns values, similar to the " " you had in your original code that you pasted. – SQLGuru Jan 30 '13 at 07:59
it works! how to make a space on listbox? because the output was 120129960606385 12012996 - was in 1st col 0606385 - was in 2nd col how to make a space between to make it look good and for easy viewing? – Danjor Jan 30 '13 at 08:00
I get it now, thanks to you sir! if I can only add some reputation for you I would gladly do it... thank you for lots of help! – Danjor Jan 30 '13 at 08:03
wait I have one last question : how to fix this one? >>> Syntax error converting datetime from character string the error comes from : adpt.Fill(myDataSet, "dbo.ChkInOut") – Danjor Jan 30 '13 at 08:08
ah. that is a DB conversion issue. Try converting the value to string like: CONVERT(varchar, ColumnName, 110) – SQLGuru Jan 30 '13 at 08:27
am I gonna change it to SQL SERVER? or in the DATASET only? – Danjor Jan 30 '13 at 08:36
is there a sql string for that? or a command something like that? – Danjor Jan 30 '13 at 08:38
in your SQL query, where you concat the columns together, perferom the CONVERT there... like SELECT Col1 + ' ' + CONVERT(varchar, Col2, 110) FROM TableName - if Col2 is your date field – SQLGuru Jan 30 '13 at 08:43
Ok thanks , Im newbie to SQLSERVER and to the string etc. sorry for that! your really a big help to this site :) – Danjor Jan 30 '13 at 08:44

Populating Listbox with data from SQL Server

2 Answers2