Asp.net - Add imagefield to gridview, and link it to datatype field

Question

I'm trying to add the "asp:Imagefield" to my gridview so it can show an image based on a room type. Lets say the room types we have are: Single, Double and Family. Based on these i want it too show an image. In my images folder i have these images:

Double.jpg
Single.jpg
Family.jpg

So for all Double rows (where Type = "Double") it should show the Double.jpg image.

Right now my gridview looks like this:

Nr    Type    Price    Select (checkbox)

I have added the image field so it looks like this now:

Photo   Nr   Type   Price    Select

Some code:

 <!-- Source control -->
                <asp:SqlDataSource ID="SqlDataSource1" runat="server"   ConnectionString="<%$ ConnectionStrings:DefaultConnection %>"     SelectCommand="SELECT Nr, Type, Price
FROM AspRooms r
WHERE  @adults &lt;= r.Adult AND @children &lt;= r.Children AND NOT EXISTS
(
    SELECT 1 FROM Booking b
    WHERE b.RoomNr = r.Nr
    AND 
    (
         (CAST(@Checkin AS date) &gt;= b.CheckIn AND CAST(@Checkin AS date)  &lt;= b.CheckOut)
      OR (CAST(@Checkin AS date) &lt;= b.CheckIn AND CAST(@Checkout AS date) &gt;= b.CheckIn)
    )
)

">
                    <SelectParameters>                            
                        <asp:ControlParameter ControlID="adults"  Name="adults" PropertyName="SelectedValue" />
                        <asp:ControlParameter ControlID="childrens" Name="children" PropertyName="SelectedValue" />
                        <asp:ControlParameter ControlID="datetimepicker1" Name="Checkin" PropertyName="Text" />
                        <asp:ControlParameter ControlID="datetimepicker2" Name="Checkout" PropertyName="Text" />                           
                    </SelectParameters>
                </asp:SqlDataSource>
                <!-- Gridview table -->
                <asp:GridView ID="GridView1" runat="server" CssClass="table table-hover table-condensed" GridLines="None" AutoGenerateColumns="False"  DataKeyNames="Nr" DataSourceID="SqlDataSource1" Width="100%">
                    <Columns>
                        <asp:ImageField HeaderText="Rooms"   DataImageUrlField="PhotoPath" ControlStyle-Height="150px" ControlStyle-Width="150px">

                        </asp:ImageField>
                        <asp:BoundField DataField="Nr" HeaderText="Nr"  ReadOnly="True" SortExpression="Nr" />
                        <asp:BoundField DataField="Type" HeaderText="Type"  SortExpression="Type" />
                        <asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" />

                        <asp:TemplateField HeaderText="Select Room">
                            <EditItemTemplate>
                                <asp:CheckBox ID="CheckBox1" runat="server" />
                            </EditItemTemplate>
                            <ItemTemplate>
                                <asp:CheckBox ID="CheckBox1" runat="server" />
                            </ItemTemplate>
                        </asp:TemplateField>                          
                    </Columns>
                </asp:GridView>

As you can see in the code i'm using the DataImageUrlField="PhotoPath" in the ImageField. In my database i have created a field called PhotoPath which contains an datatype of nchar(20). The form is like this: images/single.jpg

But when this is running i am getting an error, where it says: A field or property with the name 'PhotoPath' was not found on the selected data source.

Can any see what is currently wrong in my code?

Answer 1

You need to add the PhotoPath column to the SelectCommand, and update the ImageField to use that column.

Since you have AutoGenerateColumns="False" set, this column will not automatically appear as a separate column.

You can also simplify the test for overlapping bookings to:

[my check in] <= [booking check out] 
AND 
[my check out] >= [booking check in]

This will find any existing booking which overlaps the new booking. Your current query only finds bookings which completely contain the new booking, or which start during the new booking. A booking which starts before the new booking and ends part way through it will not be found.

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:DefaultConnection %>"
    SelectCommand="SELECT Nr, Type, Price, PhotoPath
FROM AspRooms r
WHERE  @adults &lt;= r.Adult AND @children &lt;= r.Children AND NOT EXISTS
(
    SELECT 1 FROM Booking b
    WHERE b.RoomNr = r.Nr
    AND CAST(@Checkin AS date) &lt;= b.CheckOut
    AND CAST(@Checkout AS date) &gt;= b.CheckIn
)">
<SelectParameters>                            
    <asp:ControlParameter ControlID="adults"  Name="adults" PropertyName="SelectedValue" />
    <asp:ControlParameter ControlID="childrens" Name="children" PropertyName="SelectedValue" />
    <asp:ControlParameter ControlID="datetimepicker1" Name="Checkin" PropertyName="Text" />
    <asp:ControlParameter ControlID="datetimepicker2" Name="Checkout" PropertyName="Text" />                           
</SelectParameters>
</asp:SqlDataSource>


<asp:GridView ID="GridView1" runat="server" 
    DataSourceID="SqlDataSource1" DataKeyNames="Nr"
    CssClass="table table-hover table-condensed" GridLines="None" AutoGenerateColumns="False" Width="100%"
>
<Columns>
    <asp:ImageField HeaderText="Rooms"
        DataImageUrlField="PhotoPath" 
        ControlStyle-Height="150px" 
        ControlStyle-Width="150px"
    />
    <asp:BoundField DataField="Nr" HeaderText="Nr"  ReadOnly="True" SortExpression="Nr" />
    <asp:BoundField DataField="Type" HeaderText="Type"  SortExpression="Type" />
    <asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" />

    <asp:TemplateField HeaderText="Select Room">
    <EditItemTemplate>
        <asp:CheckBox ID="CheckBox1" runat="server" />
    </EditItemTemplate>
    <ItemTemplate>
        <asp:CheckBox ID="CheckBox1" runat="server" />
    </ItemTemplate>
    </asp:TemplateField>                          
</Columns>
</asp:GridView>