Load two fields in one listbox

digita

Active member
Joined
Jul 8, 2004
Messages
29
Programming Experience
1-3
Hi,

I have a problem with populating a listbox. What i want is this: I have an Access database with a table "MemberData" and in that table a few columns (Surname, Name, Address etc.). I want to load the "Surname" and the "Name" in a list box. Then when i click that name in the listbox select all the data and put those in the fields on the form, so that the adress and zip code and al the other data is also filled.

How do i do that?

Thanks
 

Schenz

Well-known member
Joined
Sep 11, 2004
Messages
181
Location
Cincinnati, OH
Programming Experience
5-10
I would use SQL to return the Surname and Name fields as one field:
VB.NET:
SELECT ID, Surname + ', ' + Name AS 'FullName' FROM MemberData

Now you can list FullName in a listbox, and use the ID as the Value for the appropriate text.....Once you have that ID, you can update the rest of the controls.

I think you want to get a good book on ADO.NET and for what you have described I would do some reading on databinding.
 

JuggaloBrotha

VB.NET Forum Moderator
Staff member
Joined
Jun 3, 2004
Messages
4,524
Location
Lansing, MI; USA
Programming Experience
10+
yes, what schenz said one of the things you can do is in the DataAdapter wizard click query builder when setting up the select sql statement in the query builder you can make a new field (that's not in the database) which is a concantination of two or more other fields (you can also do calulation fields too)

anywho in the column field put: Surname + ', ' + Name in the alias field put FullName
then click ok and next then finish then all you do is generate the dataset and bind the listbox to your new field.
 

digita

Active member
Joined
Jul 8, 2004
Messages
29
Programming Experience
1-3
Thanks it works, but i have another question.

I use this code to populate the listbox

VB.NET:
[size=2][color=#0000ff]Private[/color][/size][size=2][color=#0000ff]Sub[/color][/size][size=2] ListLoad()
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] oCmd [/size][size=2][color=#0000ff]As[/color][/size][size=2] OleDbCommand
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] ODR [/size][size=2][color=#0000ff]As[/color][/size][size=2] OleDbDataReader
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] strSQL [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff]String 
[/color][/size][size=2]strSQL = "SELECT Id, Achternaam + ', ' + Voorletters AS FullName FROM Gegevens"
 
[/size][size=2][color=#0000ff]Try
[/color][/size][size=2]	 oCmd = [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbCommand
[/size][size=2][color=#0000ff]	 With[/color][/size][size=2] oCmd
			.Connection = ktcConnection
			.Connection.Open()
			.CommandText = strSQL
			ODR = .ExecuteReader()
	[/size][size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]With
[/color][/size][size=2]
lstLeden.Items.Clear()
[/size][size=2][color=#0000ff]Do[/color][/size][size=2][color=#0000ff]While[/color][/size][size=2] ODR.Read()
		 lstLeden.Items.Add(ODR.Item("FullName"))
[/size][size=2][color=#0000ff]Loop
 
[/color][/size][size=2][color=#0000ff]Catch[/color][/size][size=2] ex [/size][size=2][color=#0000ff]As[/color][/size][size=2] Exception
		 MessageBox.Show(ex.Message)
 
[/size][size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]Try
[/color][/size][size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]Sub
[/color][/size]

And i want this code to fill the other fields on the form.

VB.NET:
[size=2][color=#0000ff]Private[/color][/size][size=2][color=#0000ff]Sub[/color][/size][size=2] FormShow()
 
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] oCmd [/size][size=2][color=#0000ff]As[/color][/size][size=2] OleDbCommand
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] oDr [/size][size=2][color=#0000ff]As[/color][/size][size=2] OleDbDataReader
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] strSQL [/size][size=2][color=#0000ff]As [/color][/size][size=2][color=#0000ff]String
[/color][/size][size=2]strSQL = "SELECT Voorletters, Aanspreeknaam, Tussenvoegsel, "
strSQL &= "Geslacht, Adres, Postcode, Plaats, Provincie, "
strSQL &= "Geboortedatum, Notities, Telefoon_Prive, Mobiele_Telefoon,"
strSQL &= "Faxnummer, Email_Adres, Telefoon_Werk, Toestelnummer, Functie, "
strSQL &= "Soort_Lid, Lidnummer, Ftk_Nummer, Lid_Sinds, Kilometer_1982, "
strSQL &= "Kilometer_1983, Kilometer_1984, Kilometer_1985, Kilometer_1986, "
strSQL &= "Kilometer_1987, Kilometer_1988, Kilometer_1989, Kilometer_1990,"
strSQL &= " Kilometer_1991, Kilometer_1992, Kilometer_1993, Kilometer_1994, "
strSQL &= "Kilometer_1995, Kilometer_1996, Kilometer_1997, Kilometer_1998, "
strSQL &= "Kilometer_1999, Kilometer_2000, Kilometer_2001, Kilometer_2002, "
strSQL &= "Kilometer_2003, Kilometer_2004, Kilometer_2005, Kilometer_2006, "
strSQL &= "Kilometer_2007, Kilometer_2008, Kilometer_2009, Kilometer_2010, "
strSQL &= "Kilometer_Totaal, Kosten_Lid, Kosten_Lid_Methode "
strSQL &= "FROM Gegevens WHERE Id = "
[/size]
[size=2][color=#0000ff]Try
[/color][/size][size=2]	oCmd = [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbCommand
			[/size][size=2][color=#0000ff]With[/color][/size][size=2] oCmd
				 .Connection = ktcConnection
				 .Connection.Open()
				 .CommandText = strSQL
				 oDr = .ExecuteReader()
			[/size][size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]With
[/color][/size]
[size=2][color=#0000ff]If[/color][/size][size=2] oDr.Read() [/size][size=2][color=#0000ff]Then
[/color][/size][size=2][color=#0000ff]	 With[/color][/size][size=2] oDr
		txtVoorletters.Text = .Item("Voorletters").ToString()
		txtAanspreeknaam.Text = .Item("Aanspreeknaam").ToString()
[/size]
[size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]With
[/color][/size][size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]If[/color][/size]
[size=2][color=#0000ff]
[/color][/size][size=2]oDr.Close()
oCmd.Connection.Close()
 
[/size][size=2][color=#0000ff]Catch[/color][/size][size=2] ex [/size][size=2][color=#0000ff]As[/color][/size][size=2] Exception
MessageBox.Show(ex.Message)
[/size][size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]Try
 
[/color][/size][size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]Sub
 
[/color][/size]

My question is how do i use the Id selected in the ListLoad() Method in the Formload() strSQL ?
 

suddenelfilio

Well-known member
Joined
Jun 3, 2004
Messages
144
Location
Belgium
Programming Experience
5-10
Change:

VB.NET:
lstLeden.Items.Clear() 
DoWhile ODR.Read()
	 lstLeden.Items.Add(ODR.Item("FullName"))
Loop

To:

VB.NET:
lstLeden.Items.Clear() 
DoWhile ODR.Read()
	 [b]dim li as new listitem[/b]
[b]	 with li[/b]
[b]		 .tag = ODR.item("Id")[/b]
[b]		 .text = ODR.item("FullName")[/b]
[b]	 end with[/b]
	 lstLeden.Items.Add(li)
Loop

When you want to get the id from the selected item simply use:
VB.NET:
 dim id as string = lstLeden.SelectedItem.Tag
 

digita

Active member
Joined
Jul 8, 2004
Messages
29
Programming Experience
1-3
Thanks, but there are two problems now:


first of al, that "new listitem" does not exist, (gives me the type listitem not defined error) so i used ListViewItem, but that not good, because that show the ListViewItem text in the listbox.

Second, how do I implement that id in my SQL Select string is that like this:

VB.NET:
SELECT Id, Name, Address FROM MyTable WHERE Id = id

I tried that but that did not work
 

suddenelfilio

Well-known member
Joined
Jun 3, 2004
Messages
144
Location
Belgium
Programming Experience
5-10
i'm truly sorry it is true what you say the ListItem is only when working with ASP.NET. Another solution is to use an hashtable where you can store the Id as value and de name as key then you can search the hashtable by name and get the id returned.

concerning the SQL you can just tape an variable to the string: for example:

dim sqlStr as string = "SELECT Id, Name, Address FROM MyTable WHERE Id = " & id

if the id is an varchar or nvarchar type use it in the following way
dim sqlstr as string = "SELECT Id, Name, Address FROM MyTable WHERE Id ='" & id &"'"
 

digita

Active member
Joined
Jul 8, 2004
Messages
29
Programming Experience
1-3
suddenelfilio said:
i'm truly sorry it is true what you say the ListItem is only when working with ASP.NET. Another solution is to use an hashtable where you can store the Id as value and de name as key then you can search the hashtable by name and get the id returned.
I do make that hashtable solution?
 
Top Bottom