Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

List Box from SQL

Posted on 2006-06-20
4
Medium Priority
?
189 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 2000 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 2000 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

704 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