locked
Two Bound Listboxes RRS feed

  • Question

  • User-1122062568 posted

    Forgive me if this has already been answered but I could not find it when searching.

     

    I have a databound table containing uncompleted tasks which, when clicking on a task, a listbox shows all users assigned to that task.

    I also have another listbox which I wish to be able to select additional users to assign the task to.

    My current objective however is to only list the users in the listbox that are not already assigned to the task.

    I am not sure whether the code required to do this would be within the .aspx file or whether it would need to be done in the aspx.vb file.

    This is the code I have so far, sqlAssignTasks2 works, however my problem lies in sqlAssignTasks.

        <!-- Assigned Users: -->
        <asp:SqlDataSource ID="sqlAssignTasks2" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>"       
            
            SelectCommand="SELECT tblpersonnel.[fullname] FROM tblPersonnel INNER JOIN tblTaskMembers ON tblPersonnel.domainusername = tblTaskMembers.taskusername WHERE tblTaskMembers.TaskID = @TaskID">
            <SelectParameters>
                <asp:ControlParameter ControlID="gvMyTasks" DefaultValue="4" Name="TaskID" 
                    PropertyName="SelectedValue" Type="Int32" />
            </SelectParameters>
        </asp:SqlDataSource> 
        
        <!-- Unassigning Users: -->
        <asp:SqlDataSource ID="sqlAssignTasks" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>"        
            
            
            SelectCommand="SELECT [fullname], [domainusername] FROM [tblPersonnel] WHERE ([JobTitleID] >= @JobTitleID2) AND ([domainusername] <> @tmselected)">
            <SelectParameters>
                <asp:QueryStringParameter DefaultValue="4" Name="JobTitleID2" 
                    QueryStringField="4" Type="Int32" />
                <asp:QueryStringParameter DefaultValue="" Name="tmselected" 
                    QueryStringField="sqlAssignTasks2" />
            </SelectParameters>
        </asp:SqlDataSource>


    Thanks in advance.

    Thursday, November 4, 2010 10:34 AM

Answers

  • User-1316079624 posted

    I made a mistake:


    SELECT [fullname], [domainusername] FROM [tblPersonnel] WHERE ([JobTitleID] >= @JobTitleID2) AND ([domainusername] <> @tmselected)

    and (domainusername not in (select taskusername from tblTaskMembers where TaskID = @TaskID))


    i hope this helps.


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 5, 2010 12:05 PM

All replies

  • User-1316079624 posted

    try something like that in SelectCommand of sqlAssignTasks:


    SELECT [fullname], [domainusername] FROM [tblPersonnel] WHERE ([JobTitleID] >= @JobTitleID2) AND ([domainusername] <> @tmselected)

    and (domainusername not int (select taskusername from tblTaskMembers where TaskID = @TaskID))


    Also you will need to add a ControlParameter for sqlAssignTasks SqlDataSource.


    I hope this helps.



     

    Thursday, November 4, 2010 8:24 PM
  • User-1122062568 posted

    Thanks for your response. Unfortunately this did not work. There was an error on the use of 'Not Int'

    Just to simplify it, ive decided to display all the users within the listbox, then when attempting to add them to the task, if they appear in the second listbox, an error will appear.

    This isn't quite the way i wanted it but its just as effective really.

    What I intend to do now is create a button that can remove assigned users from the task by deleting the record from the table tblTaskMembers.

    Anybody know a good method of doing this using the listbox?

     

    Thanks again!

    Friday, November 5, 2010 11:39 AM
  • User-1316079624 posted

    I made a mistake:


    SELECT [fullname], [domainusername] FROM [tblPersonnel] WHERE ([JobTitleID] >= @JobTitleID2) AND ([domainusername] <> @tmselected)

    and (domainusername not in (select taskusername from tblTaskMembers where TaskID = @TaskID))


    i hope this helps.


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 5, 2010 12:05 PM