CodeCharge Studio

All ASP PHP Java C#.NET VB.NET Perl ColdFusion

Dynamically Modify the WHERE and ORDER BY Clauses of a ListBox

When the the List Source Type property of a list box is set to Table/View, the values that are used to populate the Listbox come from a database table or view. In this case, it is possible to use event code to specify a Where clause as well as a Order By clause which will be used in the SQL statement that retrieves the records to be shown in the Listbox.

In our example, we have a Status List Box that is located in a Tasks Record form.

1. In the Properties Window for the Listbox, set the Source Type property to Table/View.
2. Add the Before Build Select event to the Listbox.
3. Within the event, add the code below:

Function Tasks_Status_DataSource_BeforeBuildSelect()
  
  Tasks.Status.DataSource.Where = " status_id >2" 
  Tasks.Status.DataSource.Order = " status_id DESC"

End Function

function Tasks_Status_ds_BeforeBuildSelect() {
global $Tasks;

  $Tasks->Status->ds->Where = " status_id >2"; 
  $Tasks->Status->ds->Order = " status_id DESC";

}

sub Tasks_Status_ds_BeforeBuildSelect() {

  $Tasks->{Status}->{ds}->{Where} = " status_id >2"; 
  $Tasks->{Status}->{ds}->{Order} = " status_id DESC";

}

<!---Tasks_status_BeforeBuildSelect --->

  <CFSET strWhereStatus=" status_id > 2"> 
  <CFSET strOrderStatus=" status_id DESC"> 

//Tasks_Status_BeforeBuildSelect

  e.getCommand().setWhere(" status_id > 2"); 
  e.getCommand().setOrder(" status_id DESC");

'Tasks_Status_BeforeBuildSelect

CType(statusDataCommand,TableCommand).Where="age_id > 5"
statusDataCommand.OrderBy="age_name"

//Tasks_Status_BeforeBuildSelect

((TableCommand)statusDataCommand).Where="age_id > 5";
statusDataCommand.OrderBy="age_name";

See Also: