Solved

List Box from SQL

Posted on 2006-06-20
4
186 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

808 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