根据来自MS访问查询的结果设置gridview列的值

I have a query which is pulling values from a table in MS Access

我有一个查询，它在MS Access中从表中提取值。

SELECT
    tblInstructor.InstructorID,
    tblInstructor.Football AS InstructorRole,
    tblInstructor.Gym AS InstructorRole,
    tblInstructor.Yoga AS InstructorRole
FROM
    tblInstructor
WHERE
    tblInstructor.InstructorID = @InstructID
    OR tblInstructorRole.Football = 1
    OR tblInstructorRole.Gym = 1
    OR tblInstructor.Yoga = 1

Then i do the databind to the gridview

然后对gridview执行databind

In my aspx page im using the following method to construct my columns within the gridview

在我的aspx页面中，我使用以下方法在gridview中构造列

<asp:TemplateField HeaderText="Instructor ID">
    <ItemTemplate >
        <asp:Label ID="lblInstructorID" runat="server" Text='<%# Eval("InstructorID") %>' >
        </asp:Label>
    </ItemTemplate>
</asp:TemplateField>

I am having the issue here where I am getting the error of multiple returns, however each instructor has only one role, so I am trying to get the role of the instructor i.e. depending on whether it is true using my sql statement above and then set the instructor role column with the role of the instructor

我在这里的问题我在哪里得到多个返回的错误,然而每个老师只有一个角色,所以我想让老师的角色即取决于的确使用我上面的sql语句,然后设置教师角色列与教练的角色

 <asp:TemplateField HeaderText="Instructor Role">
     <ItemTemplate >
        <asp:Label ID="lblInstructRole" runat="server" Text='<%# Eval("InstructorRole") %>' >
        </asp:Label>
     </ItemTemplate>
 </asp:TemplateField>

My table has 3 separate field for Instructor Roles Football, Gym, Yoga which are true/false values in the Instructor timetable. I have been trying (with no luck) to get the column InstructorRole in the gridview to display the text of their role i.e.Football

我的桌子有3个单独的场地用于教练角色足球，健身房，瑜伽，这些都是教练时间表中真实/错误的价值。我一直在尝试(运气不太好)在gridview中获取列指示角色，以显示他们的角色I .e. football的文本。

I was also trying to do the following:

我也在尝试做以下事情:

If myReader(3) = True then
    Dim Role1 As String = DataBinder.Eval(e.Item.DataItem,"InstructorRole")
    Role1 = "Football"
elseif myReader(4) = true then
    Dim Role2 As String = DataBinder.Eval(e.Item.DataItem,"InstructorRole")
    Role1 = "Gym"

If anyone could advise me what to do, I can't seem to figure this out.

如果有人能指点我该怎么办，我似乎也搞不清楚。

2 个解决方案

#1

You have to use a Case statement in your SQL query. Your Final query will look like:

您必须在SQL查询中使用Case语句。您的最终查询将如下:

SELECT
tblInstructor.InstructorID,
(case when tblInstructor.Football = 1 then tblInstructor.Football
 case when tblInstructor.Gym = 1 then tblInstructor.Gym
 case when tblInstructor.Yoga = 1 then tblInstructor.Yoga
 else '' END) as InstructorRole
FROM
    tblInstructor
WHERE
    tblInstructor.InstructorID = @InstructID

#2

If you can't modify the SQL query you could do it in code using Linq, something along the lines of:

如果不能修改SQL查询，可以使用Linq进行代码修改，代码如下:

var results = from p in [query results]
              select new {
                 // other columns you need
                 InstructorRole = formatRole(p.IsFootball, p.IsGym, p.IsYoga )
              }

and formatRole function is pretty straight forward

格式函数非常直接

private string formatRole(bool football, bool gym, bool, yoga){
    if( football )
        return "Football";
    else if( gym )
        return "Gym";
    else if( yoga )
        return "Yoga";
    else
        // whatever the appropriate thing is, return "N/A" or throw an exception, up to you really.
}

You would then databind your grid to the results collection.

然后将网格绑定到结果集合。

#1