Grid-view Foreign Key representation

I've been attempting to insert a value from another table while holding the value of the original one in order to make it clear to the user what they are selecting (I'm sorry it's extremely hard to explain).

Here's my current sql source and Select Command: Select Command: # To make it clear the below code is were the problem is apparently! #

SelectCommand="SELECT c.ClassID, c.TeacherID, c.ClassYear, c.SubjectID, t.TeacherForename FROM (Class c INNER JOIN Teacher t ON c.TeacherID = t.TeacherID), s.SubjectName FROM (Class c INNER JOIN Subject s ON c.SubjectID = s.SubjectID)" 

SQL Data Source's:

 <asp:TemplateField HeaderText="SubjectName">
            <ItemTemplate>
            <%# Eval("SubjectName")%>
            </ItemTemplate>
             <EditItemTemplate>
                <asp:DropDownList ID="DropDownList2" runat="server" 
                    DataSourceID="SqlDataSource2" DataTextField="SubjectName" 
                    DataValueField="SubjectID" SelectedValue='<%# Bind("SubjectID") %>'>
                </asp:DropDownList>
                <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
                    ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
                    ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 
                    SelectCommand="SELECT Subject.SubjectName, Class.SubjectID FROM (Class INNER JOIN Subject ON Class.SubjectID = Subject.SubjectID)">
                </asp:SqlDataSource>
             </EditItemTemplate>
          </asp:TemplateField>

          <asp:TemplateField HeaderText="TeacherName">
            <ItemTemplate>
            <%# Eval("TeacherForename") %>
            </ItemTemplate>
                <EditItemTemplate>
                    <asp:DropDownList ID="DropDownList1" runat="server" 
                        DataSourceID="SqlDataSource3" DataTextField="TeacherForename" 
                        DataValueField="TeacherID" SelectedValue='<%# Bind("TeacherID") %>'>
                    </asp:DropDownList>
                    <asp:SqlDataSource ID="SqlDataSource3" runat="server" 
                        ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
                        ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 
                        SelectCommand="SELECT Teacher.TeacherForename, Class.TeacherID FROM (Class INNER JOIN Teacher ON Class.TeacherID = Teacher.TeacherID)">
                    </asp:SqlDataSource>
                </EditItemTemplate>
          </asp:TemplateField>

The end result im hoping is that the current values of SubjectID and TeacherID will be represented (when the user wishes to edit them) in the gridview by a dropdown option holding the values of SubjectName and TeacherForename (I hope this makes it clearer).

But it's saying there's a problem with my FROM statement in my select command, i tried this select command with just the TeacherID to TeacherForename and it worked fine, but once I added my SubjectID to SubjectName it created an error and I cant for my own sake locate it.

Any help would be greatly appreciated!

Answers


Try this query..

SELECT 
c.ClassID
, c.TeacherID
, c.ClassYear
, c.SubjectID
, t.TeacherForename
, s.SubjectName

 FROM Class c 
 INNER JOIN Teacher t ON c.TeacherID = t.TeacherID
 INNER JOIN Subject s ON c.SubjectID = s.SubjectID

Need Your Help

Transactional Camel JMS Consumer

xml jms apache-camel spring-transactions

I have a route which is for reading items from JMS queue and writing them into DB.

UITextbox and Navigationcontroller

ios swift

I'm creating an iOS app with swift and XCode 6. I have a multiple page app with a navigation controller. So far so good. When I enter text in one of the subpages go back to the front and reenter this