I have a grid view like below, that I want to show the firstName and LastName of professor in the ProfessorId field:
<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="3"
DataKeyNames="Id" DataSourceID="SsCourse" Visible="False" Width="845px" AutoGenerateDeleteButton="True" AutoGenerateEditButton="True" CssClass="auto-style19">
<Columns>
<asp:BoundField DataField="Code" HeaderText="Code" SortExpression="Code" />
<asp:TemplateField HeaderText=" Course Title" SortExpression="Title">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Title") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("Title") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Professor" SortExpression="ProfessorId">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList3" runat="server" DataSourceID="ShowProf" DataTextField="FirstName" DataValueField="FirstName" SelectedValue='<%# ShowProf(Eval("SubjectStudyId")) %>'>
</asp:DropDownList>
<asp:SqlDataSource ID="ShowProf" runat="server" ConnectionString="<%$ ConnectionStrings:KDUIS-v1ConnectionString %>" SelectCommand="SELECT IMS_Person.FirstName, IMS_Person.LastName, EDU_SubjectStudy_Professor.SubjectStudyId FROM EDU_Professor INNER JOIN EDU_SubjectStudy_Professor ON EDU_Professor.Id = EDU_SubjectStudy_Professor.ProfessorId INNER JOIN IMS_Person ON EDU_Professor.PersonId = IMS_Person.Id WHERE (EDU_SubjectStudy_Professor.SubjectStudyId = @SSId)">
<SelectParameters>
<asp:ControlParameter ControlID="LblSubjectStudyId" Name="SSId" PropertyName="Text" />
</SelectParameters>
</asp:SqlDataSource>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# ShowProfessor(Eval("ProfessorId")) %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Sexuality" SortExpression="Sexuality">
<EditItemTemplate>
<asp:DropDownList ID="SexulityList" runat="server">
<asp:ListItem Value="1">آقایان</asp:ListItem>
<asp:ListItem Value="2">خانم ها</asp:ListItem>
<asp:ListItem Value="3">مختلط</asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# ShowSexuality(Eval("Sexuality")) %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="SessionNo" SortExpression="SessionNo">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="ShowProf" DataTextField="FirstName" DataValueField="FirstName">
</asp:DropDownList>
<asp:SqlDataSource ID="ShowProf" runat="server" ConnectionString="<%$ ConnectionStrings:KDUIS-v1ConnectionString %>" SelectCommand="SELECT IMS_Person.FirstName, IMS_Person.LastName FROM EDU_SubjectStudy_Professor INNER JOIN EDU_Professor ON EDU_SubjectStudy_Professor.ProfessorId = EDU_Professor.Id INNER JOIN IMS_Person ON EDU_Professor.PersonId = IMS_Person.Id WHERE (EDU_SubjectStudy_Professor.SubjectStudyId = @SSId)">
<SelectParameters>
<asp:ControlParameter ControlID="LblSubjectStudyId" Name="SSId" PropertyName="Text" />
</SelectParameters>
</asp:SqlDataSource>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# ShowProfessor(Eval("SessionNo")) %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:HyperLinkField DataNavigateUrlFields="Code" DataNavigateUrlFormatString="EditCourseSession.aspx?code={0}" HeaderText="مشاهده برنامه هفتگی" Text="مشاهده برنامه هفتگی" />
</Columns>
</asp:GridView>
And I use this query to bind the data of the grid:
SELECT EDU_Lesson.Title, EDU_Course.ProfessorId, EDU_Course.Sexuality, EDU_Course.SessionNo, EDU_SubjectStudy_Lesson.SubjectStudyId,
EDU_Course.Description, EDU_Course.Id, EDU_Course.Code FROM EDU_Course INNER JOIN EDU_Lesson ON EDU_Course.LessonId = EDU_Lesson.Id
INNER JOIN EDU_SubjectStudy_Lesson ON EDU_Lesson.Id = EDU_SubjectStudy_Lesson.LessonId WHERE (EDU_SubjectStudy_Lesson.SubjectStudyId =
@subject)
now for showing the firstName and LastName of professor, I use this code:
protected string ShowProfessor(object i)
{
/// Get the PersonId of professor Table
SqlCommand cm = new SqlCommand("select PersonId from EDU_Professor where Id Like N'%" + Convert.ToInt32(i) + "%'", con);
SqlDataReader dr;
con.Open();
sqlcom.Connection = con;
dr = cm.ExecuteReader();
while (dr.Read())
{
LblPersonId.Text = dr["PersonId"].ToString();
}
sqlcom.Connection.Close();
/// Get the PersonId of professor Table
SqlCommand cm2 = new SqlCommand("select FirstName,LastName from IMS_Person where Id Like N'%" + LblPersonId.Text + "%'", con);
SqlDataReader dr2;
con.Open();
sqlcom.Connection = con;
dr2 = cm2.ExecuteReader();
while (dr2.Read())
{
FirstName.Text = dr2["FirstName"].ToString();
LastName.Text = dr2["LastName"].ToString();
}
sqlcom.Connection.Close();
return FirstName.Text + "" + LastName.Text;
}
but it returns the wrong name to me:
and I have this table for the info of person and professor:
how can I do this correctly?
