Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have a Visual Studio 2010 asp.net page in VB where I have joined multiple tables with the SqlDataSource to display specific columns in a gridview. After the table join, I have a dropdownlist where I want it to display only those rows whose "SKillGroup" column value is equivalent to the selected item on the dropdownlist. However when running the page, the dropdownlist only has one selected item (the item being Hardware) which is duplicated in the list.

My problem is how do I make it so that the dropdownlist shows all three items of 'Hardware' 'Software' and 'Network' and depending on the selected search populate it into the gridview?

Here is my code for dropdownlist and gridview

<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" 
            DataSourceID="ProblemDataSource" DataTextField="SkillGroup" 
            DataValueField="SkillGroup">
            <asp:ListItem Selected="True">Hardware</asp:ListItem>
            <asp:ListItem Selected="True">Software</asp:ListItem>
            <asp:ListItem Selected="True">Network</asp:ListItem>
        </asp:DropDownList>

     <asp:GridView ID="ProblemGridView" runat="server" AutoGenerateColumns="False" 
            DataSourceID="ProblemDataSource" style="margin-top: 0px" 
            AllowPaging="True">
            <Columns>
                <asp:BoundField DataField="ProblemID" HeaderText="ProblemID" 
                    SortExpression="ProblemID" />
                <asp:BoundField DataField="CustomerID" HeaderText="CustomerID" 
                    SortExpression="CustomerID" />
                <asp:BoundField DataField="Summary" HeaderText="Summary" 
                    SortExpression="Summary" />
                <asp:BoundField DataField="DateLogged" HeaderText="DateLogged" 
                    SortExpression="DateLogged" />
                <asp:BoundField DataField="DateUpdated" HeaderText="DateUpdated" 
                    SortExpression="DateUpdated" />
                <asp:BoundField DataField="Status" HeaderText="Status" 
                    SortExpression="Status" />
                <asp:BoundField DataField="Priority" HeaderText="Priority" 
                    SortExpression="Priority" />
                <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
                <asp:BoundField DataField="Surname" HeaderText="Surname" 
                    SortExpression="Surname" />
                <asp:BoundField DataField="SkillGroupID" HeaderText="SkillGroupID" 
                    SortExpression="SkillGroupID" />
                <asp:BoundField DataField="SkillGroup" HeaderText="SkillGroup" 
                    SortExpression="SkillGroup" />
                <asp:BoundField DataField="Expr1" HeaderText="Expr1" SortExpression="Expr1" />
                <asp:BoundField DataField="Expr2" HeaderText="Expr2" SortExpression="Expr2" />
                <asp:BoundField DataField="NoteID" HeaderText="NoteID" 
                    SortExpression="NoteID" />
                <asp:BoundField DataField="ResolutionID" HeaderText="ResolutionID" 
                    SortExpression="ResolutionID" />
            </Columns>
        </asp:GridView>

Here is my SqlDataSource for the tables join and filtering skills group column for dropdown list

 <asp:SqlDataSource ID="ProblemDataSource" runat="server" 
        ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
        SelectCommand="SELECT p.ProblemID, p.CustomerID, p.Summary, p.DateLogged, p.DateUpdated, s.Status, pr.Priority, t.Name, t.Surname, sg.SkillGroupID, sg.SkillGroup, ho.Name AS Expr1, ho.Surname AS Expr2, p.NoteID, p.ResolutionID FROM Problem AS p LEFT OUTER JOIN Status AS s ON p.StatusID = s.StatusID INNER JOIN HelpdeskOperator AS ho ON p.HelpdeskID = ho.HelpdeskID LEFT OUTER JOIN Priority AS pr ON p.PriorityID = pr.PriorityID LEFT OUTER JOIN [Skill Group] AS sg ON p.SkillGroupID = sg.SkillGroupID INNER JOIN Technician AS t ON p.ProblemID = t.ProblemID AND s.StatusID = t.StatusID AND pr.PriorityID = t.PriorityID WHERE ([SkillGroup] = @SkillGroup) " 
        <FilterParameters>
            <asp:Parameter Name="newparameter" />
        </FilterParameters>
        <InsertParameters>
            <asp:Parameter Name="SkillGroup" />
        </InsertParameters>
        <SelectParameters>
            <asp:ControlParameter ControlID="DropDownList1" Name="SkillGroup" 
                PropertyName="SelectedValue" />
        </SelectParameters>
    </asp:SqlDataSource>

I have also posted a link to the screenshot that displays what the problem is below

http://helios.hud.ac.uk/u0356716/Helpdesk.html

Can someone please advise me on this as it has been bugging me for a while.

Thank you in advance

share|improve this question

1 Answer

up vote 1 down vote

You've defined your DropDownList in the following way:

<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" 
     DataSourceID="ProblemDataSource" DataTextField="SkillGroup" 
     DataValueField="SkillGroup">
       <asp:ListItem Selected="True">Hardware</asp:ListItem>
       <asp:ListItem Selected="True">Software</asp:ListItem>
       <asp:ListItem Selected="True">Network</asp:ListItem>
 </asp:DropDownList>

For a start, you can't have all items selected. Only Hardware will be selected as it's the first in the list.

This leads to your second problem;

The query you then run is (reduced for brevity):

SELECT blah
FROM Tables
 WHERE ([SkillGroup] = @SkillGroup)

Which, of course, will be Hardware in your example above.

So you will only ever get skill groups of type Hardware back. As it's the data source for your drop down list, you'll therefore only get 'Hardware' in the list.

Note also, you get Hardware twice. This is because you have two records with a SkillGroup of Hardware.

You have a couple of choices here to start with. You can either not make your drop down list databound:

<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True">
     <asp:ListItem>Hardware</asp:ListItem>
     <asp:ListItem>Software</asp:ListItem>
     <asp:ListItem>Network</asp:ListItem>
</asp:DropDownList>

Or you can bind your DropDownList to another data source, which simply runs the query:

SELECT DISTINCT SkillGroup FROM [Skill Group]
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.