How to bind a ListBox to datadase

bex · July 1st, 2009, 11:34 AM

I need a way to display 3 columns data in ListBox

I Have this code:

Code:

 
Dim cnn As SqlConnection = New SqlConnection("Data Source=SQL1;Initial Catalog=Tracking;Integrated Security=True")
' Connect to the database
' Create a new ad hoc query to retrieve customer names
Dim cmd As SqlCommand = cnn.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT CNAM05 , CUSN05 , PCD105  FROM table1 Where (CNAM05 Like '" & TextBox1.Text & "%')"
' Dump the data to the user interface
cnn.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader
DoWhile dr.Read()
ListBox1.Items.Add(dr.GetString(0))
Loop
' Clean up
dr.Close()
cnn.Close()

this returns data from 1 column, I need to display Name -- Address--Post Code. but when i select an item only the value of name should be past

thanks in advance

Imar · July 4th, 2009, 04:01 AM

Hi there,

You can concatenate the values you need in the query and then display them:

SELECT Name + ' (' + Address + ')' AS FullDescription, Name FROM Table

Then you can use FullDescription as the text for the drop down and Name for the value.

You can also query them as separate columns (Name, Address) and then concatenate them in your VB code.

Hope this helps,

Imar

bex · July 4th, 2009, 10:35 AM

Thanks Imar
that works fine,
now it dysplays like this

Name ------------------Address------------------------ Postcode
Name --------------------------------Address------------------------------ Postcode
Name ---------Address -----------------------------------Postcode.....

it will be nice if i can disply the query in columns:

Name Address Postcode
Name Address Postcode
Name Address Postcode

Imar · July 5th, 2009, 04:09 AM

Hi bex,

This is not so easy to implement. Browsers don't support multi columns in a drop down list and strip multiple spaces from the items in the list so you can't use spaces to fill up the items.

There are a number of solutions available; some free and some paid:

http://www.google.com/search?hl=en&q...n+drop+&aqi=g5

Cheers,

Imar