Geek asks and answers

Set gridview column value based on MS Access query result

I have a query that pulls values ​​from a table in 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 data binding to gridview

On my aspx page, I use the following method to build columns in gridview

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

I had a problem here when I get an error with several returned data, however, each teacher has only one role, so I'm trying to get the role of an instructor, i.e. depending on whether this is true using my SQL statement above and then set the instructor role column with the instructor role

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

3 . , , , / . ( ) InstructorRole gridview, i.e.Football

:

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"

- , , .

+3
2

Case SQL-. :

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

SQL-, Linq, - :

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

formatRole

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.
}

results.

0

More articles:


All Articles