Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

List Box from SQL

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
cpfeiffer
Asked:
cpfeiffer
  • 2
  • 2
2 Solutions
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
cpfeifferAuthor Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
cpfeifferAuthor Commented:
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now