The sample process below shows how to configure a custom SQL datasource to populate a drop-down list.
The SQL datasource has a parameter named ID_DIR in the SQL command WHERE clause. This parameter receives the selected value of the form’s REQUEST_DIRECTORIES drop-down list.
It is possible to add multiple parameters to the same SQL command in this way.
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MainDbSource"].ToString();
string providerName = System.Configuration.ConfigurationManager.ConnectionStrings["MainDbSource"].ProviderName;
protected void Page_Load(object sender, EventArgs e)
{
base.Page_Load(sender, e);
if(!IsPostBack)
{
// Configure directories data source
SqlDataSource directoriesDataSource = new SqlDataSource();
directoriesDataSource.ConnectionString = connectionString;
directoriesDataSource.ProviderName = providerName;
directoriesDataSource.SelectCommand = "SELECT ID_DIRECTORY, DIRNAME FROM DIRECTORY;";
// Bind the dropdown list to the directories data source and set the value and text properties
REQUEST_DIRECTORIES.DataSource = directoriesDataSource;
REQUEST_DIRECTORIES.DataValueField = "ID_DIRECTORY";
REQUEST_DIRECTORIES.DataTextField = "DIRNAME";
REQUEST_DIRECTORIES.DataBind();
// Refresh users dropdown list
RefreshUsersDropDownList();
}
}
protected void DIRECTORY_CHANGED(object sender, EventArgs e)
{
// This method is called when the Directories DropDownListBox selection is changed
RefreshUsersDropDownList();
}
private void RefreshUsersDropDownList()
{
// Configure users data source
SqlDataSource usersDataSource = new SqlDataSource();
usersDataSource.ConnectionString = connectionString;
usersDataSource.ProviderName = providerName;
usersDataSource.SelectCommand = "SELECT ID_USER, USERNAME FROM USERS WHERE ACTIVE = 'Y' AND ID_DIRECTORY = @ID_DIR";
// Configure the ID_DIR parameter
Parameter directoryParameter = new Parameter();
directoryParameter.Name = "ID_DIR";
directoryParameter.DefaultValue = REQUEST_DIRECTORIES.SelectedValue
// Add the parameter to the SelectCommand parameters list
usersDataSource.SelectParameters.Add(directoryParameter);
// Bind the dropdown list to the users data source and set the value and text properties
REQUEST_USERS.DataSource = usersDataSource;
REQUEST_USERS.DataValueField = "ID_USER";
REQUEST_USERS.DataTextField = "USERNAME";
REQUEST_USERS.DataBind();
}