We help IT Professionals succeed at work.

C# Gridview Conditional Format Help

rochestermn
rochestermn asked
on
Medium Priority
1,277 Views
Last Modified: 2012-05-11
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

Comment
Watch Question

Carlos VillegasFull Stack .NET Developer

Commented:
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

Carlos VillegasFull Stack .NET Developer

Commented:
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

Author

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

Carlos VillegasFull Stack .NET Developer

Commented:
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

Miguel OzSenior Software Engineer
CERTIFIED EXPERT
Top Expert 2009

Commented:
Please post your SqlDataSource markup and check that the SQL statement works withoout the gridview

Author

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

Carlos VillegasFull Stack .NET Developer

Commented:
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

Carlos VillegasFull Stack .NET Developer

Commented:
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

Author

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

Carlos VillegasFull Stack .NET Developer

Commented:
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() ?

Author

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
Carlos VillegasFull Stack .NET Developer

Commented:
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

Carlos VillegasFull Stack .NET Developer

Commented:
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

Author

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



Carlos VillegasFull Stack .NET Developer

Commented:
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

Carlos VillegasFull Stack .NET Developer

Commented:
I changed the command also, look all the code.

Author

Commented:
Here is a screen of my data in sql server.
dblayout.JPG

Author

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.
Carlos VillegasFull Stack .NET Developer

Commented:
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>

Author

Commented:
Nope.  Let me try that. Missed it.  Thanks.
Carlos VillegasFull Stack .NET Developer

Commented:
Well if your table contains null values its not going to work well, Im working on that

Author

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.

Author

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

Carlos VillegasFull Stack .NET Developer

Commented:
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

Full Stack .NET Developer
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
This fantastic help.
Carlos VillegasFull Stack .NET Developer

Commented:
Thanks buddy
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.