Solved

List Box from SQL

Posted on 2006-06-20
4
187 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

752 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