Changing the value of a dropdown list in ASP.net based on SQL query

See more:

ASP.NET

SQL

form

Dear all I am having a go at Using ASP.Net forms.

To state my requirments

I need to create a form that enables users to read from one SQL table and write to another, I belive I have this part covered except for a major issue.

I understand and have added the two data sources which display both sets of columns but am uncertain of how to go about mapping the tables. The problem is, as a new student is added to the table on the left the rows would become de synchronised.

Additionally I need to drill down on the masses of information stored in these databases, so I have created a landing page with 3 drop down lists that map to SQL however I am unsure of how to pass on the infomation to the 2nd - 3rd drop down box to create a view based on the selections.

Please see my current code below.

<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
    CodeFile="Default.aspx.cs" Inherits="_Default" %>

<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <asp:DropDownList ID="DropDownList1" runat="server"
        DataSourceID="SqlDataSource1" DataTextField="DeptName" DataValueField="SID">
    </asp:DropDownList>
    <br />
    <asp:DropDownList ID="DropDownList2" runat="server"
        DataSourceID="SqlDataSource2" DataTextField="DeptName" DataValueField="SID">
    </asp:DropDownList>
    <br />
    <asp:DropDownList ID="DropDownList3" runat="server"
        DataSourceID="SqlDataSource3" DataTextField="OfferingCode"
        DataValueField="OfferingID">
    </asp:DropDownList>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
        ConnectionString="<%$ ConnectionStrings:BISConnectionString %>" SelectCommand="SELECT DISTINCT CL.SID, CL.CODE AS DeptCode, CL.Name AS DeptName
FROM  ProSolution.dbo.CollegeLevel CL INNER JOIN [ProSolution].[dbo].[StaffCollegeLevelMembership] AS SCLM
ON CL.SID = SCLM.SID INNER JOIN ProSolution.dbo.Staff AS S
ON SCLM.StaffID = S.StaffID
WHERE (CL.SID &lt;&gt; '0')
AND (CL.LevelNum = '1')
AND (CL.Enabled = '1')
AND (CL.MaxAcademicYearID IS NULL)
AND (RTRIM(Code) NOT IN ('PHY', 'PY2', 'PYE', 'PYW', 'CDU'))
ORDER BY DeptCode"></asp:SqlDataSource>
    <asp:SqlDataSource ID="SqlDataSource2" runat="server"
        ConnectionString="<%$ ConnectionStrings:BISConnectionString %>" SelectCommand="
SELECT DISTINCT CL.SID, CL.Code AS DeptCode, CL.Name AS DeptName
FROM [ProSolution].[dbo].Staff AS S INNER JOIN [ProSolution].[dbo].StaffCollegeLevelMembership AS SCLM
ON S.StaffID = SCLM.StaffID RIGHT OUTER JOIN [ProSolution].[dbo].CollegeLevel AS CL
ON SCLM.SID = CL.SID
WHERE (CL.SID &lt;&gt; '0')
AND (CL.LevelNum = '2')
AND (CL.Enabled = '1')
AND (CL.MaxAcademicYearID IS NULL)
ORDER BY DeptCode            "></asp:SqlDataSource>
    <asp:SqlDataSource ID="SqlDataSource3" runat="server"
        ConnectionString="<%$ ConnectionStrings:BISConnectionString %>" SelectCommand="SELECT DISTINCT O.OfferingID AS OfferingID, O.AcademicYearID, O.Code AS OfferingCode,
O.Name AS OfferingName, OS.Description AS OfferingStatus
FROM ProSolution.dbo.Offering AS O INNER JOIN ProSolution.dbo.OfferingStatus AS OS
ON O.OfferingStatusID = OS.OfferingStatusID INNER JOIN [BIS].[dbo].[vIP_FunctionalSkillsEnrolments_14] AS VFS
ON O.OfferingID = VFS.OfferingID
WHERE O.AcademicYearID = '14/15'
ORDER BY OfferingName"></asp:SqlDataSource>
</asp:Content>

Any suggestions are greatly appreciated.

Regards,

Michael.