Friday, February 15, 2013

Custom DropDown/DropDownList/ComboBox with built-in Validator(Asp .NET/C#)

In my last post I described how to create a custom text box control with a built in required validator.  In this post I will describe how to do the same to a drop down list control.  When I first started to work on this example I ran into an issue where I couldn't add the validator control to the control collection.  My next approach involved creating a composite control, this approach is a lot more flexible but also requires significantly more work.  Fortunately I came across this article which provided me with a simple solution to my original problem.  The steps below are almost identical to the ones found in my previous post.  There is also a link to a working demo of the control at the end of the post.

Step 1:  Create a class extending the DropDownList control and add private RequiredFieldValidator variable.

public class DropDownReqValidator : DropDownList 
{
    #region Validator
    /// <summary>
    /// Validator
    /// </summary>
    private RequiredFieldValidator req;
    #endregion

Step 2:  Add properties you will want to set on the validator.

Notice that the Validation Group property overrides the textbox property and also sets the corresponding property on the validator if the validator is not null.  The Required property will be used to enable and disable the validator.  The rest of the properties will be used to set properties on the validator control.

#region Properties
/// <summary>
/// ValidationGroup Property
/// </summary>
public virtual string ValidationGroup
{
    get
    {
        string o = base.ValidationGroup;
        return o;
    }
    set
    {
        String oldValue = ValidationGroup;
        if (value != oldValue)
        {
            if (req != null)
            {
                req.ValidationGroup = value;
            }
            base.ValidationGroup = value;
        }
    }
}

/// <summary>
/// InvalidMessage Property
/// </summary>
[Browsable(true)]
[Description("InvalidMessage"), Category("Custom"), DefaultValue("")]
public virtual String InvalidMessage
{
    get
    {
        object o = ViewState["InvalidMessage"];
        if (o != null)
            return (String)o;
        return string.Empty; // Default value
    }
    set
    {
        String oldValue = InvalidMessage;
        if (value != oldValue)
        {
            if (req != null)
            {
                req.ErrorMessage = value;
            }
            ViewState["InvalidMessage"] = value;
        }
    }
}

/// <summary>
/// Required Property
/// </summary>
[Browsable(true)]
[Description("Required"), Category("Custom"), DefaultValue(false)]
public virtual bool Required
{
    get
    {
        object o = ViewState["Required"];
        if (o != null)
            return (bool)o;
        return false; // Default value
    }
    set
    {
        bool oldValue = Required;
        if (value != oldValue)
        {
            ViewState["Required"] = value;
        }
    }
}

/// <summary>
/// ClientScript Property
/// </summary>
[Browsable(true)]
[Description("ClientScript"), Category("Custom"), DefaultValue(true)]
public virtual bool ClientScript
{
    get
    {
        object o = ViewState["ClientScript"];
        if (o != null)
            return (bool)o;
        return true; // Default value
    }
    set
    {
        bool oldValue = ClientScript;
        if (value != oldValue)
        {
            if (req != null)
            {
                req.EnableClientScript = value;
            }
            ViewState["ClientScript"] = value;
        }
    }
}
#endregion

Step 3:  On Init

We will instantiate the RequiredFieldValidator in the OnInit event, we also set the validator properties.  You can see the full life cycle of the page and controls and the order of the events here.  Since we don't want our code to execute at design time we check if we're in design mode at the top of our method.

#region On Init
/// <summary>
/// Init
/// </summary>
/// <param name="e"></param>
protected override void OnInit(EventArgs e)
{
    //if we want this control to render at design time we need this code.
    if (this.DesignMode)
    {
        base.OnInit(e);
        return;
    }
    //instantiate and setup the validator
    req = new RequiredFieldValidator();
    req.Enabled = false;
    req.ControlToValidate = this.ID;
    req.ErrorMessage = this.InvalidMessage;
    req.Text = "Required";
    req.EnableClientScript = ClientScript;
    req.ValidationGroup = this.ValidationGroup;
    req.Display = ValidatorDisplay.Dynamic;
    Controls.Add(req);
}
#endregion

Step 4: Render

If the field is required we will render the textbox  and the validator controls.
#region Render
/// <summary>
/// Render 
/// </summary>
/// <param name="writer">Writer</param>
public override void RenderControl(HtmlTextWriter writer)
{
    //if we want this control to render at design time we need this code.        
    if (this.DesignMode)
    {
        base.RenderControl(writer);
        return;
    }
    //if required property is set to true enable the validator
    if (Required == true)
    {
        base.Render(writer);
        writer.Write("<span style=\"color:red;\">*</span>");
        req.Enabled = true;
        req.RenderControl(writer);
    }
    else
    {
        base.RenderControl(writer);
    }
}
#endregion

Step 5: Override the CreateCustomControl method.

In order to add the validator control to the drop down list we need to override the Create Custom Control method.
#region Create Control Collection
/// <summary>
/// Override of the Create Control Collection Method
/// based on http://www.codeproject.com/Articles/20055/Does-Not-Allow-Child-Controls-Error 
/// </summary>
/// <returns></returns>
protected override System.Web.UI.ControlCollection CreateControlCollection()
{
    return new ControlCollection(this);
}
#endregion

Usage Example:

Markup
<%@ Page Title="" Language="C#" MasterPageFile="~/Site1.Master" AutoEventWireup="true" CodeBehind="DropDownReq.aspx.cs" Inherits="CustomControls.DropDownReq" %>
<%@ Register assembly="CustomControls" namespace="CustomControls.Controls" tagprefix="cc1" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
    <cc1:DropDownReqValidator ID="DropDownReqValidator1" runat="server" Required="true">
        <asp:ListItem Text="" Value=""></asp:ListItem>
        <asp:ListItem Text="Option 1" Value="1"></asp:ListItem>
        <asp:ListItem Text="Option 2" Value="2"></asp:ListItem>
    </cc1:DropDownReqValidator>
    <br />
    <asp:checkbox id="DropDownReqCheckBox" runat="server" checked="true" text="Required"
        autopostback="true" oncheckedchanged="DropDownReqCheckBox_CheckedChanged" />
    <asp:button id="Submit" runat="server" causesvalidation="true" text="Submit" />
</asp:Content>

Code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace CustomControls
{
    public partial class DropDownReq : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void DropDownReqCheckBox_CheckedChanged(object sender, EventArgs e)
        {
            DropDownReqValidator1.Required = DropDownReqCheckBox.Checked;
        }
    }
}

Examples:

Required Field:

Required Field after validation:



Complete Source:

 CustomControls_DropDown.zip

Demo:

Demo 1

Demo 2 

Tuesday, February 5, 2013

Compare Excel Spreadsheets

Below is a VBA script that will compare 2 spreadsheets, highlight the differences and add a note to each cell to show the value on the other sheet.  Additionally all the differences are noted on the third sheet.  The sample sheet can be found here.












Sub CompareWorkSheets()
    Dim c1 As Range
    Dim c2 As Range
    Dim RowPos As Long
     
    Sheet3.Range("A1") = "Row"
    Sheet3.Range("B1") = "Column"
    Sheet3.Range("C1") = "Sheet1"
    Sheet3.Range("D1") = "Sheet2"
     
    RowPos = 1
     
    For Each c1 In Sheet1.UsedRange       
        If c1.Text <> Sheet2.Cells(c1.Row, c1.Column).Text Then
            Sheet1.Cells(c1.Row, c1.Column).Interior.Color = RGB(255, 251, 204)
            Sheet1.Cells(c1.Row, c1.Column).ClearComments
            Sheet1.Cells(c1.Row, c1.Column).AddComment Sheet2.Cells(c1.Row, c1.Column).Text
             
            Sheet2.Cells(c1.Row, c1.Column).Interior.Color = RGB(255, 251, 204)
            Sheet2.Cells(c1.Row, c1.Column).ClearComments
            Sheet2.Cells(c1.Row, c1.Column).AddComment c1.Text
             
            RowPos = RowPos + 1
            Sheet3.Cells(RowPos, 1) = c1.Row
            Sheet3.Cells(RowPos, 2) = c1.Column
            Sheet3.Cells(RowPos, 3) = c1
            Sheet3.Cells(RowPos, 4) = Sheet2.Cells(c1.Row, c1.Column)
        End If
    Next c1
End Sub

Alternative version of the code leaves the first 2 sheets unchanged and shows all of the differences on the third sheet.

Sub CompareWorkSheets()
    Dim c1 As Range
    Dim c2 As Range
    Dim RowPos As Long
     
    Sheet3.Range("A1") = "Row"
    Sheet3.Range("B1") = "Column"
    Sheet3.Range("C1") = "Sheet1"
    Sheet3.Range("D1") = "Sheet2"
     
    RowPos = 1
     
    For Each c1 In Sheet1.UsedRange
        If c1.Text <> Sheet2.Cells(c1.Row, c1.Column).Text Then
            Sheet3.Cells(c1.Row, c1.Column).Interior.Color = RGB(255, 251, 204)
            Sheet3.Cells(c1.Row, c1.Column).ClearComments
            Sheet3.Cells(c1.Row, c1.Column).AddComment Sheet2.Cells(c1.Row, c1.Column).Text
            Sheet3.Cells(c1.Row, c1.Column) = c1
            
        Else
            Sheet3.Cells(c1.Row, c1.Column).Interior.Color = RGB(255, 255, 255)
            Sheet3.Cells(c1.Row, c1.Column).ClearComments
            Sheet3.Cells(c1.Row, c1.Column) = c1
        End If
    Next c1
End Sub