Displaying the data in SQL SERVER on ListBox

Question

Ive been having a hard time finding a solution for my problem, I want to display all the columns on my DB in SQL in my ListBox... anyone have an idea? please do help me?

Here is my codes:

Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        Try
            Dim thisConnection As New SqlConnection("Data Source=#####;Initial Catalog=####;Persist Security Info=True;User ID=#####;Password=#####") 
            thisConnection.Open()
            Dim sql As String = ("SELECT ControlNo,EmpNo,CheckOutDate,CheckOutTime,TaxiNo,PlateNo,Model,Make FROM dbo.ChkInOut WHERE ControlNo='" & txtsearch.Text & "'")
            Dim da As New SqlDataAdapter(sql, thisConnection)
            da.Fill(ds, "dbo.ChkInOut")
            Dim dt As DataTable = ds.Tables("dbo.ChkInOut")
            ListBox1.DataSource = dt
            ListBox1.DisplayMember = ("ControlNo")
            txtCtrlNo.DataBindings.Add("text", dt, "ControlNo")
            txtEmpNo.DataBindings.Add("text", dt, "EmpNo")
            txtCheckOutDate.DataBindings.Add("text", dt, "CheckOutDate")
            txtCheckOutTime.DataBindings.Add("text", dt, "CheckOutTime")
            txtTaxiUnitNo.DataBindings.Add("text", dt, "TaxiNo")
            txtPlateNo.DataBindings.Add("text", dt, "PlateNo")
            txtModel.DataBindings.Add("text", dt, "Model")
            txtMake.DataBindings.Add("text", dt, "Make")
            thisConnection.Close()
        Catch ex As Exception
        End Try
    End Sub

Try putting ';DROP Table dbo.ChkInOut;-- into your search field. After this, the ListBox will be showing all columns still in your table. — Joel Coehoorn
– Joel Coehoorn, Commented Jan 31, 2013 at 4:30
@JoelCoehoorn -- love it, hehe -- why stop there? ';DROP DATABASE OJT; -- SQL Injection can be fun — sgeddes
– sgeddes, Commented Jan 31, 2013 at 4:36

sgeddes · Accepted Answer · 2013-01-31 03:31:47Z

1

I don't think you can add multiple columns to a single listbox. Instead, I'd recommend concatenating the column you'd like to display (DisplayMember) as a single column.

Perhaps something like this (assuming all columns are varchar -- if not you'll need to use CAST, CONVERT and possible COALESCE:

SELECT ControlNo + ': ' + EmpNo + ': ' + CheckOutDate + ': ' + 
          CheckOutTime + ': ' + TaxiNo + ': ' + PlateNo + ': ' + 
          Model + ': ' + Make as DisplayColumn
FROM dbo.ChkInOut 
WHERE ControlNo='" & txtsearch.Text & "'"

If not, you could consider using a Gridview or some other control that supports multiple columns.

BTW -- you're not setting a ValueMember -- you may need to do that as well depending on your needs.

Good luck.

answered Jan 31, 2013 at 3:31

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

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

9 Comments

Danjor Over a year ago

sgeddes these codes are working fine, my only problem is the 'LOADING TIME' can I reduce it?

sgeddes Over a year ago

Not sure I understand your question? If you mean it takes a while to load your above query, then my only guess is you don't have an index on your ControlNo column in the DB.

Danjor Over a year ago

yeah, it takes a while to load the result of my program... what is that index that your talking about sir sgeddes?

sgeddes Over a year ago

In SQL Server (Management Studio), you need to make sure the table ChkInOut has and Index on the ControlNo field. Search for SQL Server Table Indexing and you should be able to figure it out. Easiest way is to open SSMS, right click ChkInOut table, click Design, and one of your options (on your toolbar) will be Manage Indexes and Keys -- add an index on your ControlNo field. Google it for more information. Good luck.

Danjor Over a year ago

I can't find the 'Design', but I use 'Modify' and I found out there in Column Properties under Table Designer that the ControlNo (Indexable = Yes) is that it sir sgeddes?

|

Collectives™ on Stack Overflow

Displaying the data in SQL SERVER on ListBox

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related