BOEING39
asked on
LISTBOX ISSUES
The following markup has two listboxes. One for station "sta" and the other employee "emp". The employee box fills with data dependant upon the station selected. Both controls are auto postback.
I am having difficulty with the code behind for the Statiion "sta" dropdown control. When I select a single station it populates the employee box correctly. However, I need assistance with the code behind for a "Select All" statement
for the "sta" control that when selected will populate the listbox with all the stations listed personnel.
I am having difficulty with the code behind for the Statiion "sta" dropdown control. When I select a single station it populates the employee box correctly. However, I need assistance with the code behind for a "Select All" statement
for the "sta" control that when selected will populate the listbox with all the stations listed personnel.
ASKER
Are we taking about the select statement for "emp" correct?
If you know the index of "Select All" option within the list box, e.g. first in list box will have the index 0, then check if SelectedIndex == 0
OR
ini other way, check if SelectedText == "Select All" //string case insesitive
in both cases, if true, the the query should be SELECT [Name] FROM [Emp]
The query result could be then filled to a data table using a data adapter, then the data table could be set as data source for the list box.
Hope it works
OR
ini other way, check if SelectedText == "Select All" //string case insesitive
in both cases, if true, the the query should be SELECT [Name] FROM [Emp]
The query result could be then filled to a data table using a data adapter, then the data table could be set as data source for the list box.
Hope it works
ASKER
The "Select All" is indexed to zero in "sta" which is a "ListItem". The remainder of the items are tied to another DB "personnel" & TBL "results" . The "Select All" item is listed at "0" for the ListBox. In the first exaple above are you saying in the Where caluse if the SelectedIndex == 0 to set it up as a Query Parameter?
If possible could show me how the code should look. I have been orking this for a few days now and haven't gotten anywhere. I am relatively new to this.
<asp:ListBox runat="server" id="sta" DataValueField="Station" AppendDataBoundItems="true " DataTextField="Station" DataSourceID="AccessDataSo urce1" Width="82px" Height="127px" AutoPostBack="True" SelectionMode="Multiple">
<asp:ListItem>Select All "</asp:ListItem>
</asp:ListBox>
If possible could show me how the code should look. I have been orking this for a few days now and haven't gotten anywhere. I am relatively new to this.
<asp:ListBox runat="server" id="sta" DataValueField="Station" AppendDataBoundItems="true
<asp:ListItem>Select All "</asp:ListItem>
</asp:ListBox>
I was talking about
<asp:AccessDataSource runat="server" ID="AccessDataSource3" DataFile="../Admin/personn el.mdb" SelectCommand="SELECT DISTINCT [Name] FROM [Results] WHERE ([Station] = ?)">
<asp:AccessDataSource runat="server" ID="AccessDataSource3" DataFile="../Admin/personn
ASKER
How would the Select parameter look?
/asp:DropDownList>
<asp:AccessDataSource runat="server" ID="AccessDataSource3" DataFile="../Admin/personn el.mdb" SelectCommand="SELECT DISTINCT [Name] FROM [Results] WHERE ([Station] = ?) OR (1=?)">
/<SelectParameters>
<asp:controlparameter ControlID="sta" PropertyName="SelectedValu e" Name="Station" Type="String" />
/SelectParameters>
</asp:AccessDataSource>
/asp:DropDownList>
<asp:AccessDataSource runat="server" ID="AccessDataSource3" DataFile="../Admin/personn
/<SelectParameters>
<asp:controlparameter ControlID="sta" PropertyName="SelectedValu
/SelectParameters>
</asp:AccessDataSource>
ASKER
I put this together no help.
<asp:ListBox runat="server" id="sta" DataValueField="Station" AppendDataBoundItems="true
<asp:ListItem>Select All</asp:ListItem>
</asp:ListBox>
/asp:DropDownList>
<asp:AccessDataSource runat="server" ID="AccessDataSource3" DataFile="../Admin/personn
<SelectParameters>
<asp:QueryStringParameter Name="Station" QueryStringField="(station
<asp:QueryStringParameter Name="Station" QueryStringField="(0,1)" Type="String" />
</SelectParameters>
Are you still having a problem?
ASKER
Yes, I am not clear on how to setup the parameters on the Dropdown "emp" . Previously it was explained I could use a where clause with the parameters. I have a Where caluse setup when a station "sta" is selected the employees populate the ListBox for "emp". That works fine but when all stations are selected "Select All" the ListBox does not populate with all the personnel.
************************** ********** ********** ********** ********** ********** ********** *******
SELECT DISTINCT [Name] FROM [Results] WHERE ([Station] = ?) OR (1=?)
If you execute it with parameters (station Id,0) - it will return personal for the station
f you execute it with parameters (0,1) - it will return all personal
************************** ********** ********** ********** ********** ********** ********** ********
I currently have:
<asp:ListBox runat="server" id="sta" DataValueField="Station" AppendDataBoundItems="true " DataTextField="Station" DataSourceID="AccessDataSo urce1" Width="82px" Height="127px" AutoPostBack="True" SelectionMode="Multiple">
<asp:ListItem>Select All</asp:ListItem>
</asp:ListBox>
<asp:AccessDataSource runat="server" ID="AccessDataSource1" DataFile="../Admin/personn el.mdb" SelectCommand="SELECT DISTINCT [Station] FROM [Results]">
</asp:AccessDataSource>
</td>
</tr>
<tr>
<td class="style1" style="width: 150px"> </td>
<td> </td>
</tr>
<tr>
<td class="style1" style="width: 150px">Employees:</td>
<td>
<asp:ListBox runat="server" id="emp" Width="156px" Height="179px" DataValueField="Name" AppendDataBoundItems="true " DataTextField="Name" DataSourceID="AccessDataSo urce3" AutoPostBack="True" SelectionMode="Multiple">
<asp:ListItem>Select All</asp:ListItem>
</asp:ListBox>
<asp:AccessDataSource runat="server" ID="AccessDataSource3" DataFile="../Admin/personn el.mdb" SelectCommand="SELECT DISTINCT [Name] FROM [Results] WHERE ([Station] = ?)">
<SelectParameters>
<asp:controlparameter ControlID="sta" PropertyName="SelectedValu e" Name="Station" Type="String" />
</SelectParameters>
</asp:AccessDataSource>
**************************
SELECT DISTINCT [Name] FROM [Results] WHERE ([Station] = ?) OR (1=?)
If you execute it with parameters (station Id,0) - it will return personal for the station
f you execute it with parameters (0,1) - it will return all personal
**************************
I currently have:
<asp:ListBox runat="server" id="sta" DataValueField="Station" AppendDataBoundItems="true
<asp:ListItem>Select All</asp:ListItem>
</asp:ListBox>
<asp:AccessDataSource runat="server" ID="AccessDataSource1" DataFile="../Admin/personn
</asp:AccessDataSource>
</td>
</tr>
<tr>
<td class="style1" style="width: 150px"> </td>
<td> </td>
</tr>
<tr>
<td class="style1" style="width: 150px">Employees:</td>
<td>
<asp:ListBox runat="server" id="emp" Width="156px" Height="179px" DataValueField="Name" AppendDataBoundItems="true
<asp:ListItem>Select All</asp:ListItem>
</asp:ListBox>
<asp:AccessDataSource runat="server" ID="AccessDataSource3" DataFile="../Admin/personn
<SelectParameters>
<asp:controlparameter ControlID="sta" PropertyName="SelectedValu
</SelectParameters>
</asp:AccessDataSource>
That sounds like you need to dynamically adjust the AccessDataSource parameters in the code-behind, but the exact place depends on the page life cycle. My first guess would be to set the parameter QueryStringField in the Page.Load event handler, depending on the criteria that you just explained.
ASKER
Would you be able to help me with this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I placed the code in the following way but no help.
// Process the queries
if ((emp.SelectedIndex != -1) && (emp.SelectedItem.Text == "Select All"))
{
foreach (ListItem employee in emp.Items)
{
if (employee.Text != "Select All") // Ignore the "Select All" option
{
// Set the value of the @Emp parameter and execute the query for each employee
myCommand.Parameters["@Emp "].Value = employee.Text;
myCommand.ExecuteNonQuery( );
}
}
}
else if (emp.SelectedIndex != -1)
{
ListItem employee = emp.SelectedItem;
// Insert into database for employee
myCommand.Parameters["@Emp "].Value = employee.Text;
myCommand.ExecuteNonQuery( );
}
QueryStringParameter stationParameter = (QueryStringParameter)Acce ssDataSour ce3.Select Parameters ["Station" ];
stationParameter.QueryStri ngField = "(0,1)";
if ((sta.SelectedIndex != -1) && (sta.SelectedItem.Text == "Select All"))
foreach (ListItem Station in sta.Items)
{
if ((Station.Selected) && (Station.Text != "Select All"))
{
myCommand.Parameters["@sta "].Value = Station.Text;
myCommand.ExecuteNonQuery( );
}
}
// Process the queries
if ((emp.SelectedIndex != -1) && (emp.SelectedItem.Text == "Select All"))
{
foreach (ListItem employee in emp.Items)
{
if (employee.Text != "Select All") // Ignore the "Select All" option
{
// Set the value of the @Emp parameter and execute the query for each employee
myCommand.Parameters["@Emp
myCommand.ExecuteNonQuery(
}
}
}
else if (emp.SelectedIndex != -1)
{
ListItem employee = emp.SelectedItem;
// Insert into database for employee
myCommand.Parameters["@Emp
myCommand.ExecuteNonQuery(
}
QueryStringParameter stationParameter = (QueryStringParameter)Acce
stationParameter.QueryStri
if ((sta.SelectedIndex != -1) && (sta.SelectedItem.Text == "Select All"))
foreach (ListItem Station in sta.Items)
{
if ((Station.Selected) && (Station.Text != "Select All"))
{
myCommand.Parameters["@sta
myCommand.ExecuteNonQuery(
}
}
It is crucial that you get the right timing, and maybe that is not the correct approach. Another approach is to handle the OnSelecting event, and work with the passed in OleDbCommand.
Programmatically accessing data from DataSource controls
http://www.mikesdotnetting.com/Article/45/Programmatically-accessing-data-from-DataSource-controls
Programmatically accessing data from DataSource controls
http://www.mikesdotnetting.com/Article/45/Programmatically-accessing-data-from-DataSource-controls
protected void AccessDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
e.Command.Parameters["EmployeeID"].Value = 1;
}
ASKER
Thx, this worked out for me.
If you execute it with parameters (station Id,0) - it will return personal for the station
f you execute it with parameters (0,1) - it will return all personal