CODEWITHSUNDEEP
asp.netsqlvb.netms-accessgridview
JHouston86
JHouston86

Reputation: 33

set value of a gridview column depending on result from MS Access query

I have a query which is pulling 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 the databind to the gridview

In my aspx page im using the following method to construct my columns within the 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 

 <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

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.

Upvotes: 3

Views: 1494

Answers (2)

Muhammad Akhtar
Muhammad Akhtar

Reputation: 52241

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

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

Upvotes: 2

Roman
Roman

Reputation: 20246

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

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.

Upvotes: 0

Related Questions