Load two fields in one listbox

[digita]

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]

I would use SQL to return the Surname and Name fields as one field:

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]

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]

Thanks it works, but i have another question.

I use this code to populate the listbox

[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.

[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]

Change:

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

To:

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:

 dim id as string = lstLeden.SelectedItem.Tag

 

[digita]

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:

SELECT Id, Name, Address FROM MyTable WHERE Id = id

I tried that but that did not work

 

[suddenelfilio]

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]

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?