Solved

LISTBOX ISSUES

Posted on 2011-09-03
17
280 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:BOEING39
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 2
  • +1
17 Comments
 
LVL 8

Expert Comment

by:Andrei Fomitchev
ID: 36480145
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


0
 

Author Comment

by:BOEING39
ID: 36480627
Are we taking about the select statement for "emp" correct?
0
 
LVL 2

Expert Comment

by:MoatazGad
ID: 36480864
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
0
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 

Author Comment

by:BOEING39
ID: 36480922
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="AccessDataSource1" Width="82px" Height="127px" AutoPostBack="True" SelectionMode="Multiple">
<asp:ListItem>Select All "</asp:ListItem>
</asp:ListBox>
0
 
LVL 8

Expert Comment

by:Andrei Fomitchev
ID: 36480960
I was talking about

      <asp:AccessDataSource runat="server" ID="AccessDataSource3" DataFile="../Admin/personnel.mdb" SelectCommand="SELECT DISTINCT [Name] FROM [Results] WHERE ([Station] = ?)">
   
0
 

Author Comment

by:BOEING39
ID: 36482089
How would the Select parameter look?

/asp:DropDownList>
<asp:AccessDataSource runat="server" ID="AccessDataSource3" DataFile="../Admin/personnel.mdb" SelectCommand="SELECT DISTINCT [Name] FROM [Results] WHERE ([Station] = ?) OR (1=?)">

/<SelectParameters>
<asp:controlparameter ControlID="sta" PropertyName="SelectedValue" Name="Station" Type="String" />
/SelectParameters>
</asp:AccessDataSource>
0
 

Author Comment

by:BOEING39
ID: 36482108


I put this together no help.    

<asp:ListBox runat="server" id="sta" DataValueField="Station" AppendDataBoundItems="true" DataTextField="Station" DataSourceID="AccessDataSource1" Width="82px" Height="127px" AutoPostBack="True" SelectionMode="Multiple">
<asp:ListItem>Select All</asp:ListItem>
</asp:ListBox>


/asp:DropDownList>
<asp:AccessDataSource runat="server" ID="AccessDataSource3" DataFile="../Admin/personnel.mdb" SelectCommand="SELECT DISTINCT [Name] FROM [Results] WHERE ([Station] = ?) OR (1=?)">

<SelectParameters>
<asp:QueryStringParameter Name="Station" QueryStringField="(station Id,0)" Type="String" />
 <asp:QueryStringParameter Name="Station" QueryStringField="(0,1)" Type="String" />
                   
                        </SelectParameters>
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 36506893
Are you still having a problem?
0
 

Author Comment

by:BOEING39
ID: 36507521
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="AccessDataSource1" Width="82px" Height="127px" AutoPostBack="True" SelectionMode="Multiple">
<asp:ListItem>Select All</asp:ListItem>
</asp:ListBox>
                  
<asp:AccessDataSource runat="server" ID="AccessDataSource1" DataFile="../Admin/personnel.mdb" SelectCommand="SELECT DISTINCT [Station] FROM [Results]">
</asp:AccessDataSource>
                  &nbsp;</td>
            </tr>
            <tr>
                  <td class="style1" style="width: 150px">&nbsp;</td>
                  <td>&nbsp;</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="AccessDataSource3" AutoPostBack="True" SelectionMode="Multiple">
               
<asp:ListItem>Select All</asp:ListItem>
                        
</asp:ListBox>
                  <asp:AccessDataSource runat="server" ID="AccessDataSource3" DataFile="../Admin/personnel.mdb" SelectCommand="SELECT DISTINCT [Name] FROM [Results] WHERE ([Station] = ?)">
                        <SelectParameters>
                              <asp:controlparameter ControlID="sta" PropertyName="SelectedValue" Name="Station" Type="String" />
                        </SelectParameters>
                  </asp:AccessDataSource>






0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 36507609
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.
0
 

Author Comment

by:BOEING39
ID: 36507902
Would you be able to help me with this?
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 36513002
I would think something like this might get you started:

QueryStringParameter stationParameter = (QueryStringParameter)   AccessDataSource3.SelectParameters["Station"];        
stationParameter.QueryStringField = "(0,1)";

Open in new window

0
 

Author Comment

by:BOEING39
ID: 36514946
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)AccessDataSource3.SelectParameters["Station"];
            stationParameter.QueryStringField = "(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();
                    }
                }
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 36517152
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

protected void AccessDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
    e.Command.Parameters["EmployeeID"].Value = 1;
}

Open in new window

0
 

Author Closing Comment

by:BOEING39
ID: 36895459
Thx, this worked out for me.
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question