Download
1 / 7
70 likes | 156 Views
CTEC2902 Advanced Programming. Using list boxes to fetch specific records. Viewing The Data. Populating a list box from UKCities table. Data Table. Assume table definition to be UKCities ( ID , City, County, Population). Also assume we want to Show City field Get ID field. Record
E N D
CTEC2902Advanced Programming Using list boxes to fetch specific records Fetching Clicked Item
Viewing The Data Populating a list box from UKCities table Data Table Assume table definition to be UKCities (ID, City, County, Population) Also assume we want to Show City field Get ID field Record Record Record Record Etc. lstCities.DataSource = dtCities lstCities.DataTextField = “City“ lstCities.DataValueField = “ID” lstCities.DataBind() Fetching Clicked Item
Viewing The Data What happens when you click on an item? On screen, clicked item is highlighted In the background, zero-based index value of clicked item is placed in SelectedIndex property the clicked item itself is placed in SelectedItem property the corresponding value field data is placed in SelectedValue property Fetching Clicked Item
Viewing The Data In a data-bound list box • Dim DB As New clsSQLServer(“MyDB.mdf") • Dim dtEmps As New DataTable • DB.Execute("sp_tblStaff_GetAll") • dtEmps = DB.QueryResults • lstSurnames.DataSource = dtEmps • lstSurnames.DataTextField = “Surname" • lstSurnames.DataValueField = “AccNo" • lstSurnames.DataBind() • You can now say things like … • txtAccountNumber.Text = lstSurnames.SelectedValue Corresponding value is in this property Fetching Clicked Item
Viewing The Data In a data-bound list box • Dim DB As New clsSQLServer(“MyDB.mdf") • Dim dtEmps As New DataTable • DB.Execute("sp_tblStaff_GetAll") • dtEmps = DB.QueryResults • lstSurnames.DataSource = dtEmps • lstSurnames.DataTextField = “Surname" • lstSurnames.DataValueField = “AccNo" • lstSurnames.DataBind() • Dim PKey As Long • PKey = lstSurnames.SelectedValue You can now execute an SQL like = Dim SQL As String SQL = "SELECT * FROM employees “ & _ “WHERE ID=“ & PKey Fetching Clicked Item
Viewing The Data Getting Data Out of a data-bound list box Another example ‘Create your database and data table objects ‘Populate your data table ‘Set data source property to data table name lstCities.DataSource = dtCities ‘Set display field lstCities.DataTextField = “City" 'Set the value field; i.e. the data that will be ‘available when a city is clicked on lstCities.DataValueField = “ContactName“ lstCities.DataBind() ‘Carry out the specified actions Dim TheName As String TheName = lstCities.SelectedValue You can now use TheName as a parameter in your SQL SELECT command Q: how to run a stored procedure with parameters? When you click a city name, the corresponding ContactName will be automatically placed in SelectedValue property Fetching Clicked Item
Viewing The Data Summary Use DataTextField to say which field value is to be displayed Use DataValueField to say which field value will be in SelectedValue Data Table Record Record Record Record Etc. When an item is clicked, SelectedValue contains value of field specified by DataValueField SelectedIndex returns index of clicked item (zero-based) SelectedItem represents the complete record, as DataRecordView, so you NEED TO use .ToString Items listed in same order as the records in data table; i.e. use ORDER BY in SELECT Fetching Clicked Item 7
