[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1223
  • Last Modified:

C# Gridview Conditional Format Help

Im having a hard time trying to accomplish this in a grid.

I have a status column which is Active or Inactive.

In Sql Server the column is stored as a bit field.

In the gridview im showing each row with a label of "Active" or "Inactive" for the
status column.

Its a row that can be edited so when the user clicks the edit link then the EditItemTemplate brings up a Dropdownlist that can be changed to "Active" or "Inactive".  The value stored in sql server db would be 1 or 0 to represent the bit value.  There SqlDataSource has an Update Command and Update Parameters to handle this.  Also the values were ending up in sql server as nulls.

The problem Im having is doing the IIF type comparison on the label to display "Active" or "Inactive"

See example code.  
<asp:TemplateField HeaderText="Status">
                           <EditItemTemplate>
                            <asp:DropDownList ID="ddlStatus" runat="server">
                                <asp:ListItem Text="Active" Value="True"></asp:ListItem>
                                <asp:ListItem Text="Inactive" Value="False"></asp:ListItem>
                            </asp:DropDownList>
                           </EditItemTemplate>                              
                           <ItemTemplate>
                            <asp:Label ID="lblStatus" runat="server" Text='<%# ((string)Eval("Status") = "0") ? "Active":"Inactive" %>'></asp:Label>
                           </ItemTemplate> 
                           <HeaderTemplate>
                               <asp:Label ID="Label3" runat="server" Text="Status"></asp:Label>
                           </HeaderTemplate>
                          
                           <ItemStyle Width="100px" />
                       </asp:TemplateField>

Open in new window

0
rochestermn
Asked:
rochestermn
  • 15
  • 11
1 Solution
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Hello, Can you check this??

<asp:Label ID="lblStatus" runat="server" Text='<%# ((string)Eval("Status") = "0") ? "Active":"Inactive" %>'></asp:Label>

Open in new window


Change to:
<asp:Label ID="lblStatus" runat="server" Text='<%# ((string)Eval("Status") == "0") ? "Active":"Inactive" %>'></asp:Label>

Open in new window

0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
To be more clear:

change to:
<asp:Label ID="lblStatus" runat="server" Text='<%# ((string)Eval("Status").Equals("0")) ? "Active":"Inactive" %>'></asp:Label>

Open in new window

0
 
rochestermnAuthor Commented:
Actually I cant compare a boolean to a string so the attached code handles the item template label just fine.  Now how to get it into the database as a bit is the next step.  Before I was trying this and it kept inserting nulls.  Not sure if there is something wrong with my update command or a parameter.
<ItemTemplate>
                            <asp:Label ID="lblStatus" runat="server" Text='<%# Eval("Status").Equals("0") ? "Active":"Inactive" %>'></asp:Label>
                           </ItemTemplate>

Open in new window

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
Carlos VillegasFull Stack .NET DeveloperCommented:
You parameter is declared in this way?
<asp:SqlDataSource ID="SqlDataSource1" runat="server">
    <UpdateParameters>
        <asp:ControlParameter ControlID="ddlStatus" Name="pStatus" DbType="Boolean" PropertyName="SelectedValue"
            Type="Boolean" />
    </UpdateParameters>
</asp:SqlDataSource>

Open in new window

0
 
Miguel OzSoftware EngineerCommented:
Please post your SqlDataSource markup and check that the SQL statement works withoout the gridview
0
 
rochestermnAuthor Commented:
There error im getting when clicking update after selecting a value in the status dropdown is:
Must declare the scalar variable "@status".

SqlDataSource code below.


<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:Investments_DEVConnectionString %>" 
        SelectCommand="SELECT * FROM [BankCodes]"
        UpdateCommand="Update BankCodes SET BankCode=@bankcode,Description=@description,Status=@status WHERE BankCode=@bankcode">
        <UpdateParameters>
            <asp:Parameter Name="@bankcode" Type="String" />
            <asp:Parameter Name="@description" Type="String" />
            <asp:Parameter Name="@status" DbType="Single" />
        </UpdateParameters>
      </asp:SqlDataSource>

</asp:Content>

Open in new window

0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Did you try?:
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Investments_DEVConnectionString %>"
            SelectCommand="SELECT * FROM [BankCodes]" UpdateCommand="Update BankCodes SET BankCode=@bankcode,Description=@description,Status=@status WHERE BankCode=@bankcode">
            <UpdateParameters>
                <asp:Parameter Name="@bankcode" Type="String" />
                <asp:Parameter Name="@description" Type="String" />
                <asp:Parameter Name="@status" Type="Boolean" />
            </UpdateParameters>
        </asp:SqlDataSource>

Open in new window

0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Or this?
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Investments_DEVConnectionString %>"
            SelectCommand="SELECT * FROM [BankCodes]" UpdateCommand="Update BankCodes SET BankCode=@bankcode,Description=@description,Status=@status WHERE BankCode=@bankcode">
            <UpdateParameters>
                <asp:Parameter Name="@bankcode" Type="String" />
                <asp:Parameter Name="@description" Type="String" />
                <asp:ControlParameter Name="@status" Type="Boolean" ControlID="ddlStatus" PropertyName="SelectedValue" />
            </UpdateParameters>
        </asp:SqlDataSource>

Open in new window

0
 
rochestermnAuthor Commented:
Good idea.  Tried it but I got this.

Could not find control 'ddlStatus' in ControlParameter '@status'



<asp:TemplateField HeaderText="Status">
                           <EditItemTemplate>
                            <asp:DropDownList ID="ddlStatus" runat="server">
                                <asp:ListItem Text="Active" Value="True"></asp:ListItem>
                                <asp:ListItem Text="Inactive" Value="False"></asp:ListItem>
                            </asp:DropDownList>
                           </EditItemTemplate>                              
                           <ItemTemplate>
                            <asp:Label ID="lblStatus" runat="server" Text='<%# Eval("Status").Equals("0") ? "Active":"Inactive" %>'></asp:Label>
                           </ItemTemplate> 
                           <HeaderTemplate>
                               <asp:Label ID="Label3" runat="server" Text="Status"></asp:Label>
                           </HeaderTemplate>
                          
                           <ItemStyle Width="100px" />
                       </asp:TemplateField>

Open in new window

0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Oh I see, that is because your control is inside a template field, please can you provide your grid code?, and also the code that you use to call SqlDataSource1.Update() ?
0
 
rochestermnAuthor Commented:
The grid is attached.
As far as the sqldatasource update it contains an update command.  So when I press the edit link for that row in the grid it pops up the edit row I change the value in the dropdown and press update.

See screen attached.
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
                    DataSourceID="SqlDataSource1" CellPadding="4" ForeColor="#333333" 
                    GridLines="None" DataKeyNames="BankCode">
                   <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                   <Columns>
                       <asp:CommandField HeaderText="Edit" ShowEditButton="True" ShowHeader="True" />
                       <asp:BoundField DataField="BankCode" HeaderText="Bank Code" 
                           SortExpression="BankCode" />
                       <asp:BoundField DataField="Description" HeaderText="Description" 
                           SortExpression="Description" />
                        <asp:TemplateField HeaderText="Status">
                           <EditItemTemplate>
                            <asp:DropDownList ID="ddlStatus" runat="server">
                                <asp:ListItem Text="Active" Value="True"></asp:ListItem>
                                <asp:ListItem Text="Inactive" Value="False"></asp:ListItem>
                            </asp:DropDownList>
                           </EditItemTemplate>                              
                           <ItemTemplate>
                            <asp:Label ID="lblStatus" runat="server" Text='<%# Eval("Status").Equals("0") ? "Active":"Inactive" %>'></asp:Label>
                           </ItemTemplate> 
                           <HeaderTemplate>
                               <asp:Label ID="Label3" runat="server" Text="Status"></asp:Label>
                           </HeaderTemplate>
                          
                           <ItemStyle Width="100px" />
                       </asp:TemplateField>
                   </Columns>
                   <EditRowStyle BackColor="#999999" />
                   <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                   <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                   <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                   <RowStyle BackColor="#F7F6F3" ForeColor="#333333" HorizontalAlign="Center" />
                   <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                   <SortedAscendingCellStyle BackColor="#E9E7E2" />
                   <SortedAscendingHeaderStyle BackColor="#506C8C" />
                   <SortedDescendingCellStyle BackColor="#FFFDF8" />
                   <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
                </asp:GridView>

Open in new window

bankcode.JPG
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Hello buddy, this must work, try it:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Investments_DEVConnectionString %>"
    SelectCommand="SELECT * FROM [BankCodes]" UpdateCommand="Update BankCodes SET Description=@description,Status=@status WHERE BankCode=@bankcode">
    <UpdateParameters>
        <asp:Parameter Name="BankCode" DbType="String" />
        <asp:Parameter Name="Description" DbType="String" />
        <asp:Parameter Name="Status" DbType="Boolean" />
    </UpdateParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1"
    CellPadding="4" ForeColor="#333333" GridLines="None" DataKeyNames="BankCode">
    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
    <Columns>
        <asp:CommandField HeaderText="Edit" ShowEditButton="True" ShowHeader="True" />
        <asp:BoundField DataField="BankCode" HeaderText="Bank Code" SortExpression="BankCode" />
        <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />
        <asp:TemplateField HeaderText="Status">
            <EditItemTemplate>
                <asp:DropDownList ID="ddlStatus" runat="server" SelectedValue='<%# Bind("Status") %>'>
                    <asp:ListItem Text="Active" Value="True"></asp:ListItem>
                    <asp:ListItem Text="Inactive" Value="False"></asp:ListItem>
                </asp:DropDownList>
            </EditItemTemplate>
            <ItemTemplate>
                <asp:Label ID="lblStatus" runat="server" Text='<%# Convert.ToBoolean(Eval("Status")) ? "Active":"Inactive" %>'></asp:Label>
            </ItemTemplate>
            <HeaderTemplate>
                <asp:Label ID="Label3" runat="server" Text="Status"></asp:Label>
            </HeaderTemplate>
            <ItemStyle Width="100px" />
        </asp:TemplateField>
    </Columns>
    <EditRowStyle BackColor="#999999" />
    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" HorizontalAlign="Center" />
    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
    <SortedAscendingCellStyle BackColor="#E9E7E2" />
    <SortedAscendingHeaderStyle BackColor="#506C8C" />
    <SortedDescendingCellStyle BackColor="#FFFDF8" />
    <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
</asp:GridView>

Open in new window

0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Be aware that you cant update your BankCode field with that data schema, I recommend you to use a BankId Identity field as PrimaryKey, and the BankCode as UniqueKey
0
 
rochestermnAuthor Commented:
Yes thats right.  I did add an Id column which has a seed value.  Plus I added it as the DataKeyName property of the gird.  And made it a hidden field in the grid because I dont need to show it.  However I am still getting this error which I think is unrelated.

Must declare the scalar variable "@status".



0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Good, remember add this to the parameters collection:

Updated code:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Investments_DEVConnectionString %>"
    SelectCommand="SELECT * FROM [BankCodes]" UpdateCommand="Update BankCodes SET BankCode = @BankCode, Description = @Description, Status=@Status WHERE BankId = @BankId">
    <UpdateParameters>
        <asp:Parameter Name="BankCode" DbType="String" />
        <asp:Parameter Name="Description" DbType="String" />
        <asp:Parameter Name="Status" DbType="Boolean" />
        <asp:Parameter Name="BankId" DbType="Int32" />
    </UpdateParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1"
    CellPadding="4" ForeColor="#333333" GridLines="None" DataKeyNames="BankId">
    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
    <Columns>
        <asp:CommandField HeaderText="Edit" ShowEditButton="True" ShowHeader="True" />
        <asp:BoundField DataField="BankCode" HeaderText="Bank Code" SortExpression="BankCode" />
        <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />
        <asp:TemplateField HeaderText="Status">
            <EditItemTemplate>
                <asp:DropDownList ID="ddlStatus" runat="server" SelectedValue='<%# Bind("Status") %>'>
                    <asp:ListItem Text="Active" Value="True"></asp:ListItem>
                    <asp:ListItem Text="Inactive" Value="False"></asp:ListItem>
                </asp:DropDownList>
            </EditItemTemplate>
            <ItemTemplate>
                <asp:Label ID="lblStatus" runat="server" Text='<%# Convert.ToBoolean(Eval("Status")) ? "Active":"Inactive" %>'></asp:Label>
            </ItemTemplate>
            <HeaderTemplate>
                <asp:Label ID="Label3" runat="server" Text="Status"></asp:Label>
            </HeaderTemplate>
            <ItemStyle Width="100px" />
        </asp:TemplateField>
    </Columns>
    <EditRowStyle BackColor="#999999" />
    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" HorizontalAlign="Center" />
    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
    <SortedAscendingCellStyle BackColor="#E9E7E2" />
    <SortedAscendingHeaderStyle BackColor="#506C8C" />
    <SortedDescendingCellStyle BackColor="#FFFDF8" />
    <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
</asp:GridView>

Open in new window

0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
I changed the command also, look all the code.
0
 
rochestermnAuthor Commented:
Here is a screen of my data in sql server.
dblayout.JPG
0
 
rochestermnAuthor Commented:
Ok that took care of the @status parm error.  However still inserts NULL into table when I select a value in the dropdown and choose update.

Thanks for all your good help sir.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Hey did you make this change??
            <EditItemTemplate>
                <asp:DropDownList ID="ddlStatus" runat="server" SelectedValue='<%# Bind("Status") %>'>
                    <asp:ListItem Text="Active" Value="True"></asp:ListItem>
                    <asp:ListItem Text="Inactive" Value="False"></asp:ListItem>
                </asp:DropDownList>
            </EditItemTemplate>
0
 
rochestermnAuthor Commented:
Nope.  Let me try that. Missed it.  Thanks.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Well if your table contains null values its not going to work well, Im working on that
0
 
rochestermnAuthor Commented:
Ok the update is working but...my asp listitems for the dropdowns arent selecting the right value.

So I set all items to 0 in the db.  Then changed a couple to Inactive and they became 1 in the db.

But in the grid everyrow shows as Inactive.  Can you take another look at my EditItemTemplate in the grid where the ddl is.  I think im doing something wrong there.

0
 
rochestermnAuthor Commented:
Here is a code update now after the changes.
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
                    DataSourceID="SqlDataSource1" CellPadding="4" ForeColor="#333333" 
                    GridLines="None" DataKeyNames="Id" AllowPaging="True">
                   <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                   <Columns>
                       <asp:CommandField HeaderText="Edit" ShowEditButton="True" ShowHeader="True" />
                       <asp:BoundField Visible="false" DataField="Id" />
                       <asp:BoundField DataField="BankCode" HeaderText="Bank Code" 
                           SortExpression="BankCode" />
                       <asp:BoundField DataField="Description" HeaderText="Description" 
                           SortExpression="Description" />
                        <asp:TemplateField HeaderText="Status">
                           <EditItemTemplate>
                            <asp:DropDownList ID="ddlStatus" runat="server" SelectedValue='<%# Bind("Status") %>'>
                                <asp:ListItem Text="Active" Value="True"></asp:ListItem>
                                <asp:ListItem Text="Inactive" Value="False"></asp:ListItem>
                            </asp:DropDownList>
                           </EditItemTemplate>                              
                           <ItemTemplate>
                            <asp:Label ID="lblStatus" runat="server" Text='<%# Eval("Status").Equals("0") ? "Active":"Inactive" %>'></asp:Label>
                           </ItemTemplate> 
                           <HeaderTemplate>
                               <asp:Label ID="Label3" runat="server" Text="Status"></asp:Label>
                           </HeaderTemplate>
                          
                           <ItemStyle Width="100px" />
                       </asp:TemplateField>
                   </Columns>
                   <EditRowStyle BackColor="#999999" />
                   <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                   <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                   <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                   <RowStyle BackColor="#F7F6F3" ForeColor="#333333" HorizontalAlign="Center" />
                   <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                   <SortedAscendingCellStyle BackColor="#E9E7E2" />
                   <SortedAscendingHeaderStyle BackColor="#506C8C" />
                   <SortedDescendingCellStyle BackColor="#FFFDF8" />
                   <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
                </asp:GridView>

Open in new window

0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Hello again! this must work for your table:
Please see all the changes:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Investments_DEVConnectionString %>"
    SelectCommand="SELECT Id, BankCode, Description, ISNULL(Status, 0) AS Status FROM [BankCodes]"
    UpdateCommand="Update BankCodes SET BankCode = @BankCode, Description = @Description, Status = @Status WHERE Id = @Id">
    <UpdateParameters>
        <asp:Parameter Name="BankCode" DbType="String" Size="2" />
        <asp:Parameter Name="Description" DbType="String" Size="50" />
        <asp:Parameter Name="Status" DbType="Boolean" />
        <asp:Parameter Name="Id" DbType="Int32" />
    </UpdateParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1"
    CellPadding="4" ForeColor="#333333" GridLines="None" DataKeyNames="Id">
    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
    <Columns>
        <asp:CommandField HeaderText="Edit" ShowEditButton="True" ShowHeader="True" />
        <asp:BoundField DataField="BankCode" HeaderText="Bank Code" SortExpression="BankCode" />
        <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />
        <asp:TemplateField HeaderText="Status">
            <EditItemTemplate>
                <asp:DropDownList ID="ddlStatus" runat="server" SelectedValue='<%# Bind("Status") %>'>
                    <asp:ListItem Text="Active" Value="True"></asp:ListItem>
                    <asp:ListItem Text="Inactive" Value="False"></asp:ListItem>
                </asp:DropDownList>
            </EditItemTemplate>
            <ItemTemplate>
                <asp:Label ID="lblStatus" runat="server" Text='<%# Convert.ToBoolean(Eval("Status")) ? "Active":"Inactive" %>'></asp:Label>
            </ItemTemplate>
            <HeaderTemplate>
                <asp:Label ID="Label3" runat="server" Text="Status"></asp:Label>
            </HeaderTemplate>
            <ItemStyle Width="100px" />
        </asp:TemplateField>
    </Columns>
    <EditRowStyle BackColor="#999999" />
    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" HorizontalAlign="Center" />
    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
    <SortedAscendingCellStyle BackColor="#E9E7E2" />
    <SortedAscendingHeaderStyle BackColor="#506C8C" />
    <SortedDescendingCellStyle BackColor="#FFFDF8" />
    <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
</asp:GridView>

Open in new window

0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Hello, that is fixed in my code, see:
<asp:Label ID="lblStatus" runat="server" Text='<%# Convert.ToBoolean(Eval("Status")) ? "Active":"Inactive" %>'></asp:Label>
0
 
rochestermnAuthor Commented:
This fantastic help.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Thanks buddy
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 15
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now