Solved

List Box from SQL

Posted on 2006-06-20
4
183 Views
Last Modified: 2010-04-23
Ok I have a List box that I am filling from sql with the following:

Dim SQL as String
Dim ADO as new MyADO
Dim rs as IDataReader
Dim Values as Listbox = Listbox1

SQL = "SELECT ID, FirstName, LastName FROM Employee WHERE IsFired = 0"
RS = ado.ExecuteQuery(SQL)
While RS.Read()
     Values.Items.Add(RS.Item("FirstName").ToString())
End While

rs.dispose
ado.dispose

Ok that works. I would like to have it list the first and last name however in the list box if there are any ideas? I have tried the following:

Values.Items.Add(RS.Item("FirstName, LastName").ToString())
Values.Items.Add(RS.Item("FirstName" & "LastName).ToString())

Neither Work

I would also like to have the ID stored so that when I click on one of the items from the list I can have it run a query and fill in text box with the correct information relating to the ID that was clicked on on the list box. Here is what I tried for that:

Private Sub ListBox1_SelectedIndexChange
 
Dim Person as String
Dim SQL as String
Dim ado as MyADO
Dim DR as IDataReader

listbox1.SelectedValue = ("Person")
SQL = "SELECT FirstName, LastName, HomePhone, CellPhone FROM Employee WHERE FirstName = " & Person

the thing is "Person" is never a value, and I would like to use the ID that I have filledthe list box with.

Please let me know if anyone can hlep or if there is more information need to get help.

Chris
   
0
Comment
Question by:cpfeiffer
  • 2
  • 2
4 Comments
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 500 total points
ID: 16940688
Couple of options.

1) Concatenate the values using the SQL query:

      SQL = "SELECT ID, FirstName+' '+LastName AS Fullname FROM Employee WHERE IsFired = 0"

Then:
 
     Values.Items.Add(RS.Item("Fullname").ToString())

2) Combine the two values when adding to the list:

    Values.Items.Add(RS.Items("FirstName").ToString() & " " & RS.Items("LastName").ToString())
0
 

Author Comment

by:cpfeiffer
ID: 16942711
Great Thnaks... any ideas on when you click on one of the values now to make it fill in the rest of the text boxes ?
0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 total points
ID: 16943143
You'd be better off using databinding:

    Dim SQL as String
    Dim ADO as new MyADO
    Dim rs as IDataReader
    Dim Values as Listbox = Listbox1

    SQL = "SELECT ID, FirstName+' '+LastName AS Fullname FROM Employee WHERE IsFired = 0"
    RS = ado.ExecuteQuery(SQL)

    Values.DataSource = RS
    Values.DisplayMember = "Fullname"
    Values.ValueMember = "ID"
   
    rs.dispose
    ado.dispose

Then in your SelectedIndexChanged event:

    Dim sID As String = Values.SelectedValue.ToString()         '// This will be the ID number of the selected item


This may need tweaking a little as the code I have posted is based on .Net 2.0. So if you're using 1.1 then it will need tweaking slightly.
0
 

Author Comment

by:cpfeiffer
ID: 16951854
Thanks to both of you~~
This is what i ended with:

     Dim SQL As String
        Dim ado As New myADO
        Dim DT As DataTable
        Dim Values As ListBox = ListBox1
        SQL = "SELECT ID, FirstName+' '+LastName AS FullName FROM Employee WHERE ISFired = 0"
        DT = ado.ExecuteTableQuery(SQL)
        Values.DataSource = DT
        Values.DisplayMember = "FullName"
        Values.ValueMember = "ID"
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now