How to retreive data from the database to the second table after selecting the user in the first table?

Question

1.00/5 (1 vote)

See more:

I have three tables in my database as following:

**Employee Table: Username, Name, Job, DivisonCode**

**Course Table: CourseID, CourseName, GroupID**

**Employee_Course Table: Username, CourseID (The first key in each table is the primary key)**

I created two ListViews; one for retrieving the employee information from the Employee Table and the second one to show the taken courses (from Employee_Course Table) of the selected employee in the first ListView.

For the first table, everything works well and fine, but for the second table, I could not be able to retrieve the taken courses of the selected employee in the first table.

So how can I do that?

My ASP.NET code:

XML

<asp:ListView ID="ListView1" runat="server" DataKeyNames="Username"
                DataSourceID="SqlDataSource1" InsertItemPosition="LastItem">

                <EditItemTemplate>
                    <tr style="">
                        <td>
                            <asp:ImageButton ID="UpdateButton" ImageUrl="images/update4.png" Width="20px" runat="server" CommandName="Update" />
                            <asp:ImageButton ID="CancelButton" ImageUrl="images/cancel.png" Width="20px" runat="server" CommandName="Cancel" />
                        </td>
                        <td>
                            <asp:TextBox ID="NameTextBox" runat="server" Text='<%# Bind("Name") %>' />
                        </td>
                        <td>
                            <asp:Label ID="UsernameLabel1" runat="server" Text='<%# Eval("Username") %>' />
                        </td>
                        <td>
                            <asp:TextBox ID="JobTitleTextBox" runat="server"
                                Text='<%# Bind("JobTitle") %>' />
                        </td>
                        <td>
                            <asp:TextBox ID="BadgeNoTextBox" runat="server" Text='<%# Bind("BadgeNo") %>' />
                        </td>
                        <td>
                            <asp:TextBox ID="EmpOrgTypeTextBox" runat="server"
                                Text='<%# Bind("EmpOrgType") %>' />
                        </td>
                        <td>
                            <asp:DropDownList ID="DropDownList1" runat="server" SelectedValue='<%# Bind("DivisionCode") %>'
                                DataSourceID="SqlDataSource1" DataTextField="DivisionName"
                                DataValueField="SapCode">
                            </asp:DropDownList>
                            <asp:SqlDataSource ID="SqlDataSource1" runat="server"
                                ConnectionString="<%$ ConnectionStrings:testConnectionString %>"
                                DeleteCommand="DELETE FROM [Divisions] WHERE [SapCode] = @SapCode"
                                InsertCommand="INSERT INTO [Divisions] ([SapCode], [DivisionName]) VALUES (@SapCode, @DivisionName)"
                                SelectCommand="SELECT * FROM [Divisions]"
                                UpdateCommand="UPDATE [Divisions] SET [DivisionName] = @DivisionName WHERE [SapCode] = @SapCode">
                                <DeleteParameters>
                                    <asp:Parameter Name="SapCode" Type="Double" />
                                </DeleteParameters>
                                <InsertParameters>
                                    <asp:Parameter Name="SapCode" Type="Double" />
                                    <asp:Parameter Name="DivisionName" Type="String" />
                                </InsertParameters>
                                <UpdateParameters>
                                    <asp:Parameter Name="DivisionName" Type="String" />
                                    <asp:Parameter Name="SapCode" Type="Double" />
                                </UpdateParameters>
                            </asp:SqlDataSource>
                        </td>
                    </tr>
                </EditItemTemplate>
                <EmptyDataTemplate>
                    <table runat="server" style="">
                        <tr>
                            <td>
                                No data was returned.</td>
                        </tr>
                    </table>
                </EmptyDataTemplate>
                <InsertItemTemplate>
                    <tr style="">
                        <td>
                            <asp:ImageButton ID="ImageButton1" ImageUrl="images/insert.png" Width="20px" runat="server" CommandName="Insert" />
                            <asp:ImageButton ID="CancelButton" ImageUrl="images/clear3.png" Width="20px" runat="server" CommandName="Cancel" />
                        </td>
                        <td>
                            <asp:TextBox ID="NameTextBox" runat="server" Text='<%# Bind("Name") %>' />
                        </td>
                        <td>
                            <asp:TextBox ID="UsernameTextBox" runat="server"
                                Text='<%# Bind("Username") %>' />
                        </td>
                        <td>
                            <asp:TextBox ID="JobTitleTextBox" runat="server"
                                Text='<%# Bind("JobTitle") %>' />
                        </td>
                        <td>
                            <asp:TextBox ID="BadgeNoTextBox" runat="server" Text='<%# Bind("BadgeNo") %>' />
                        </td>
                        <td>
                            <asp:TextBox ID="EmpOrgTypeTextBox" runat="server"
                                Text='<%# Bind("EmpOrgType") %>' />
                        </td>
                        <td>
                            <asp:TextBox ID="DivisionCodeTextBox" runat="server"
                                Text='<%# Bind("DivisionCode") %>' />
                        </td>
                    </tr>
                </InsertItemTemplate>
                <ItemTemplate>
                    <tr style="">
                        <td>
                            <asp:ImageButton ID="DeleteButton" ImageUrl="images/delete.png" Width="20px" runat="server" CommandName="Delete" />
                            <asp:ImageButton ID="EditButton" ImageUrl="images/edit.png" Width="20px" runat="server" CommandName="Edit" />
                            <asp:ImageButton ID="SelectButton" ImageUrl="images/select.png" Width="20px" runat="server" CommandName="Select" />
                        </td>
                        <td>
                            <asp:Label ID="NameLabel" runat="server" Text='<%# Eval("Name") %>' />
                        </td>
                        <td>
                            <asp:Label ID="UsernameLabel" runat="server" Text='<%# Eval("Username") %>' />
                        </td>
                        <td>
                            <asp:Label ID="JobTitleLabel" runat="server" Text='<%# Eval("JobTitle") %>' />
                        </td>
                        <td>
                            <asp:Label ID="BadgeNoLabel" runat="server" Text='<%# Eval("BadgeNo") %>' />
                        </td>
                        <td>
                            <asp:Label ID="EmpOrgTypeLabel" runat="server"
                                Text='<%# Eval("EmpOrgType") %>' />
                        </td>
                        <td>
                            <asp:Label ID="DivisionCodeLabel" runat="server"
                                Text='<%# Eval("DivisionCode") %>' />
                        </td>
                    </tr>
                </ItemTemplate>
                <LayoutTemplate>
                    <table ID="Table1" runat="server" border="0" style="" class="listView">
                        <tr runat="server">
                            <td runat="server">
                                <table ID="itemPlaceholderContainer" runat="server" border="0" style="" class="listView">
                                    <tr runat="server" style="">
                                        <th runat="server">
                                        </th>
                                        <th runat="server">
                                            Name</th>
                                        <th runat="server">
                                            Username</th>
                                        <th runat="server">
                                            JobTitle</th>
                                        <th runat="server">
                                            BadgeNo</th>
                                        <th runat="server">
                                            EmpOrgType</th>
                                        <th runat="server">
                                            DivisionCode</th>
                                    </tr>
                                    <tr ID="itemPlaceholder" runat="server">
                                    </tr>
                                </table>
                            </td>
                        </tr>
                        <tr runat="server">
                            <td runat="server" style="">
                                <asp:DataPager ID="DataPager1" runat="server">
                                    <Fields>
                                        <asp:NextPreviousPagerField ButtonType="Button" ShowFirstPageButton="True"
                                            ShowLastPageButton="True" />
                                    </Fields>
                                </asp:DataPager>
                            </td>
                        </tr>
                    </table>
                </LayoutTemplate>
                <SelectedItemTemplate>
                    <tr style="">
                        <td>
                            <asp:ImageButton ID="DeleteButton" ImageUrl="images/delete.png" Width="20px" runat="server" CommandName="Delete" />
                            <asp:ImageButton ID="EditButton" ImageUrl="images/edit.png" Width="20px" runat="server" CommandName="Edit" />
                        </td>
                        <td>
                            <asp:Label ID="NameLabel" runat="server" Text='<%# Eval("Name") %>' />
                        </td>
                        <td>
                            <asp:Label ID="UsernameLabel" runat="server" Text='<%# Eval("Username") %>' />
                        </td>
                        <td>
                            <asp:Label ID="JobTitleLabel" runat="server" Text='<%# Eval("JobTitle") %>' />
                        </td>
                        <td>
                            <asp:Label ID="BadgeNoLabel" runat="server" Text='<%# Eval("BadgeNo") %>' />
                        </td>
                        <td>
                            <asp:Label ID="EmpOrgTypeLabel" runat="server"
                                Text='<%# Eval("EmpOrgType") %>' />
                        </td>
                        <td>
                            <asp:Label ID="DivisionCodeLabel" runat="server"
                                Text='<%# Eval("DivisionCode") %>' />
                        </td>
                    </tr>
                </SelectedItemTemplate>
            </asp:ListView>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server"
                ConnectionString="<%$ ConnectionStrings:testConnectionString %>"
                DeleteCommand="DELETE FROM [employee] WHERE [Username] = @Username"
                InsertCommand="INSERT INTO [employee] ([Name], [Username], [JobTitle], [BadgeNo], [EmpOrgType], [DivisionCode]) VALUES (@Name, @Username, @JobTitle, @BadgeNo, @EmpOrgType, @DivisionCode)"
                SelectCommand="SELECT * FROM [employee]"
                UpdateCommand="UPDATE [employee] SET [Name] = @Name, [JobTitle] = @JobTitle, [BadgeNo] = @BadgeNo, [EmpOrgType] = @EmpOrgType, [DivisionCode] = @DivisionCode WHERE [Username] = @Username">
                <DeleteParameters>
                    <asp:Parameter Name="Username" Type="String" />
                </DeleteParameters>
                <InsertParameters>
                    <asp:Parameter Name="Name" Type="String" />
                    <asp:Parameter Name="Username" Type="String" />
                    <asp:Parameter Name="JobTitle" Type="String" />
                    <asp:Parameter Name="BadgeNo" Type="Double" />
                    <asp:Parameter Name="EmpOrgType" Type="Double" />
                    <asp:Parameter Name="DivisionCode" Type="Double" />
                </InsertParameters>
                <UpdateParameters>
                    <asp:Parameter Name="Name" Type="String" />
                    <asp:Parameter Name="JobTitle" Type="String" />
                    <asp:Parameter Name="BadgeNo" Type="Double" />
                    <asp:Parameter Name="EmpOrgType" Type="Double" />
                    <asp:Parameter Name="DivisionCode" Type="Double" />
                    <asp:Parameter Name="Username" Type="String" />
                </UpdateParameters>
            </asp:SqlDataSource>
        </div>

        <div>

            <asp:ListView ID="ListView2" runat="server" DataKeyNames="ID"
                DataSourceID="SqlDataSource2" InsertItemPosition="LastItem">
                <EditItemTemplate>
                    <tr style="">
                        <td>
                            <asp:ImageButton ID="UpdateButton" ImageUrl="images/update4.png" Width="20px" runat="server" CommandName="Update" />
                            <asp:ImageButton ID="CancelButton" ImageUrl="images/cancel.png" Width="20px" runat="server" CommandName="Cancel" />
                        </td>
                        <%--
                        <td>
                            <asp:TextBox ID="employeeIdTextBox" runat="server"
                                Text='<%# Bind("employeeId") %>' />
                        </td>--%>
                        <td>
                            <asp:TextBox ID="courseIdTextBox" runat="server"
                                Text='<%# Bind("courseId") %>' />
                        </td>
                    </tr>
                </EditItemTemplate>
                <EmptyDataTemplate>
                    <table runat="server" style="">
                        <tr>
                            <td>
                                No data was returned.</td>
                        </tr>
                    </table>
                </EmptyDataTemplate>
                <InsertItemTemplate>
                    <tr style="">
                        <td>
                            <asp:ImageButton ID="ImageButton1" ImageUrl="images/insert.png" Width="20px" runat="server" CommandName="Insert" />
                            <asp:ImageButton ID="CancelButton" ImageUrl="images/clear3.png" Width="20px" runat="server" CommandName="Cancel" />
                        </td>
                        <%--<td>
                            <asp:TextBox ID="employeeIdTextBox" runat="server"
                                Text='<%# Bind("employeeId") %>' />
                        </td>--%>
                        <td>
                            <asp:TextBox ID="courseIdTextBox" runat="server"
                                Text='<%# Bind("courseId") %>' />
                        </td>
                    </tr>
                </InsertItemTemplate>
                <ItemTemplate>
                    <tr style="">
                        <td>
                            <asp:ImageButton ID="DeleteButton" ImageUrl="images/delete.png" Width="20px" runat="server" CommandName="Delete" />
                            <asp:ImageButton ID="EditButton" ImageUrl="images/edit.png" Width="20px" runat="server" CommandName="Edit" />
                        </td>
                        <%--<td>
                            <asp:Label ID="employeeIdLabel" runat="server"
                                Text='<%# Eval("employeeId") %>' />
                        </td>--%>
                        <td>
                            <asp:Label ID="courseIdLabel" runat="server" Text='<%# Eval("courseId") %>' />
                        </td>
                    </tr>
                </ItemTemplate>
                <LayoutTemplate>
                                <table ID="itemPlaceholderContainer" runat="server" border="0" style="" class="listView">
                                    <tr runat="server" style="">
                                        <th runat="server">
                                        </th>
                                        <%--<th runat="server">
                                            employeeId</th>--%>
                                        <th runat="server">
                                            courseId</th>
                                    </tr>
                                    <tr ID="itemPlaceholder" runat="server">
                                    </tr>
                                </table>
                </LayoutTemplate>
                <SelectedItemTemplate>
                    <tr style=""><br /> <br />
                        <td>
                            <asp:ImageButton ID="DeleteButton" ImageUrl="images/delete.png" Width="20px" runat="server" CommandName="Delete" />
                            <asp:ImageButton ID="EditButton" ImageUrl="images/edit.png" Width="20px" runat="server" CommandName="Edit" />
                        </td>
                        <%--<td>
                            <asp:Label ID="employeeIdLabel" runat="server"
                                Text='<%# Eval("employeeId") %>' />
                        </td>--%>
                        <td>
                            <asp:Label ID="courseIdLabel" runat="server" Text='<%# Eval("courseId") %>' />
                        </td>
                    </tr>
                </SelectedItemTemplate>
            </asp:ListView>
            <asp:SqlDataSource ID="SqlDataSource2" runat="server"
                ConnectionString="<%$ ConnectionStrings:testConnectionString %>"
                DeleteCommand="DELETE FROM [employee_courses] WHERE [ID] = @ID"
                InsertCommand="INSERT INTO [employee_courses] ([employeeId], [courseId]) VALUES (@employeeId, @courseId)"
                SelectCommand="SELECT * FROM [employee_courses] WHERE ([employeeId] = @Username)"
                UpdateCommand="UPDATE [employee_courses] SET [employeeId] = @employeeId, [courseId] = @courseId WHERE [ID] = @ID">
                <DeleteParameters>
                    <asp:Parameter Name="ID" Type="Int32" />
                </DeleteParameters>
                <InsertParameters>
                    <asp:Parameter Name="employeeId" Type="String" />
                    <asp:Parameter Name="courseId" Type="Int32" />
                    <asp:ControlParameter ControlID="ListView1" Name="Username" PropertyName="SelectedValue" Type="String" />
                </InsertParameters>
                <SelectParameters>
                    <asp:ControlParameter ControlID="ListView1" Name="Username"
                        PropertyName="SelectedValue" Type="String" />
                </SelectParameters>
                <UpdateParameters>
                    <%--<asp:Parameter Name="employeeId" Type="String" />--%>
                    <asp:Parameter Name="courseId" Type="Int32" />
                    <asp:Parameter Name="ID" Type="Int32" />
                </UpdateParameters>
            </asp:SqlDataSource>

Maybe this should be done programmatically in the Code-behind, but I don't know how to do it

Any Help PLEASE?

Posted 23-Dec-11 3:01am

matrix388

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**R. Giskard Reventlov** · Answer 1 · 2011-12-23T03:46:00

Solution 1

1: Looks like you've posted the same question twice - don't do that!
2: Way too much code: no one is going to wade through all of this to provide you with a solution. Try to be more precise - highlight the actual problem and show us what you have already tried for yourself.

Sorry, not much of a solution, but it wasn't much of a question.

Posted 23-Dec-11 3:46am

R. Giskard Reventlov

Comments

Wendelius 23-Dec-11 10:59am

I have to defend the OP a little bit. The first question won't open for some reason.

R. Giskard Reventlov 23-Dec-11 11:02am

A little bit? LOL.

Wendelius 23-Dec-11 11:10am

Well, didn't comment on the code dump :)