Stanly's Blog™

ASP.NET, C#, SQL Server and general software development topics.

  • Delete Duplicate Value from SQL Data Table

    In this article, let’s see how to delete a duplicate record which does not have primary key.

    For example, in the below table Total, we could find duplicate values occurring. The task is how to delete the duplicates  and retain the unique value.

     

    tempName

    GAURI ANSHU

    GAURI ANSHU

    ISHAN

    ISHAN

    RISHIKA MUSKAN

    SHASWITA

    SIDDHARTH SINGH

    SIDDHARTH SINGH

    SHWET RAJ

     

     Below is the query to accomplish the task.

     

    WITH CTE (tempName, DuplicateCount)

    AS

    (

    SELECT tempName,

    ROW_NUMBER() OVER(PARTITION BY tempName ORDER BY tempName) AS DuplicateCount

    FROM tempTable

    )

    DELETE

    FROM CTE

    WHERE DuplicateCount > 1 and tempName !='' and tempName !=null

    GO

    Initially just create a temp result using CTE having new columns which uses ROW_NUMBER using partitions and delete the duplicates.

     

    And thus the output will be

     

    tempName

    GAURI ANSHU

    ISHAN

    RISHIKA MUSKAN

    SHASWITA

    SIDDHARTH SINGH

    SHWET RAJ

     

     

     

     

     

     

     

     

     

     

  • Replace null value databind item with default value

    Here’s  the code to set default value to gridview/datalist itemtemplate if it is bound with null value or the dataset returns null value.

     

    <asp:TemplateField HeaderText="Header">

                    <ItemTemplate>

                    <asp:Label ID="lbl_amt" runat="server"  Text='<%# string.Format("{0}",string.IsNullOrEmpty(Eval("YourDataColumn").ToString())?"NA":Eval("YourDataColumn")) %>'></asp:Label>

                    </ItemTemplate>

                    </asp:TemplateField>

     

    The above Code results in NA if YourdataColumn is returned as null. Writing code in Rowdatabound to display “NA” for null values will be avoided with this.

  • Send GridView as Excel attachment in Mail

     This code illustrates you how to send Gridview content as an Excel Attachment in Email.

     

    The Gridview Markup looks like this…

     

     

    <%@ Page Language="C#" AutoEventWireup="false" CodeFile="GridMail.aspx.cs" Inherits="GridMail" %>

     

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

     

    <html xmlns="http://www.w3.org/1999/xhtml" >

    <head runat="server">

        <title>Untitled Page</title>

    </head>

    <body>

        <form id="form1" runat="server">

        <div>

         <asp:Button ID="btnMail" runat="server" Text="Send Mail"

                />

            <asp:Label ID="lbldisplay" runat="server" Text="Label"></asp:Label>

            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"

                DataSourceID="SqlDataSource1">

                <Columns>

                  

                    <asp:BoundField DataField="rollno" HeaderText="rollno"

                        SortExpression="rollno" />

                    <asp:BoundField DataField="stuname" HeaderText="stuname"

                        SortExpression="stuname" />

                    <asp:BoundField DataField="stufathername" HeaderText="stufathername"

                        SortExpression="stufathername" />

                    <asp:BoundField DataField="parentmobileno" HeaderText="parentmobileno"

                        SortExpression="parentmobileno" />

                    <asp:BoundField DataField="parentemail" HeaderText="parentemail"

                        SortExpression="parentemail" />

                    <asp:BoundField DataField="classname" HeaderText="classname"

                        SortExpression="classname" />

                    <asp:BoundField DataField="sectionname" HeaderText="sectionname"

                        SortExpression="sectionname" />

                    

                    <asp:BoundField DataField="gender" HeaderText="gender"

                        SortExpression="gender" />

                

                    <asp:BoundField DataField="loginid" HeaderText="loginid"

                        SortExpression="loginid" />

                </Columns>

            </asp:GridView>

            <asp:SqlDataSource ID="SqlDataSource1" runat="server"

                ConnectionString="<%$ ConnectionStrings:parentsalarmConnection %>"

                SelectCommand="SELECT top 10 * FROM [studentmaster] WHERE ([userid] = @userid) ORDER BY [loginid]">

                <SelectParameters>

                    <asp:Parameter DefaultValue="1001" Name="userid" Type="String" />

                </SelectParameters>

            </asp:SqlDataSource>

        </div>

        </form>

    </body>

    </html>

     

     

    Moving to code behind the function to convert Gridview content as Excel attachment is as follows:

     

    public void fn_AttachGrid()

        {

            StringWriter stw = new StringWriter();

            HtmlTextWriter hw = new HtmlTextWriter(stw);

            GridView1.RenderControl(hw);

            MailMessage mail = new MailMessage();

            mail.IsBodyHtml = true;

            mail.To.Add(new MailAddress("toMail@domain.com"));

            mail.Subject = "Sales Report";

            System.Text.Encoding Enc = System.Text.Encoding.ASCII;

            byte[] mBArray = Enc.GetBytes(stw.ToString());

            System.IO.MemoryStream mAtt = new System.IO.MemoryStream(mBArray, false);

            mail.Attachments.Add(new Attachment(mAtt, "sales.xls"));

            mail.Body = "Hi PFA";

            SmtpClient smtp = new SmtpClient();

            mail.From = new MailAddress("fromMail@domain.com", "Your Name");

            smtp.Host = "mail.domain.com";

            smtp.UseDefaultCredentials = false;

            smtp.Credentials = new System.Net.NetworkCredential(@"Username", "Password");

            smtp.EnableSsl = true;

            smtp.Send(mail);

            lbldisplay.Text = "Email Sent";

        }

     

    Calling this method in the button click event will mail  you the Gridview data as Excel attachment.

    CodeBehind :

    using System;

    using System.Data;

    using System.Configuration;

    using System.Collections;

    using System.Web;

    using System.Web.Security;

    using System.Web.UI;

    using System.Web.UI.WebControls;

    using System.Web.UI.WebControls.WebParts;

    using System.Web.UI.HtmlControls;

    using System.Net.Mail;

     

    public partial class Csharp : System.Web.UI.Page

    {

        protected void Page_Load(object sender, EventArgs e)

        {

     

        }

     

        public void fn_AttachGrid()

        {

            StringWriter stw = new StringWriter();

            HtmlTextWriter hw = new HtmlTextWriter(stw);

            GridView1.RenderControl(hw);

            MailMessage mail = new MailMessage();

            mail.IsBodyHtml = true;

            mail.To.Add(new MailAddress("toMail@domain.com"));

            mail.Subject = "Sales Report";

            System.Text.Encoding Enc = System.Text.Encoding.ASCII;

            byte[] mBArray = Enc.GetBytes(stw.ToString());

            System.IO.MemoryStream mAtt = new System.IO.MemoryStream(mBArray, false);

            mail.Attachments.Add(new Attachment(mAtt, "sales.xls"));

            mail.Body = "Hi PFA";

            SmtpClient smtp = new SmtpClient();

            mail.From = new MailAddress("fromMail@domain.com", "Your Name");

            smtp.Host = "mail.domain.com";

            smtp.UseDefaultCredentials = false;

            smtp.Credentials = new System.Net.NetworkCredential(@"Username", "Password");

            smtp.EnableSsl = true;

            smtp.Send(mail);

            lbldisplay.Text = "Email Sent";

        }

     

     

     

        public override void VerifyRenderingInServerForm(Control control)

        {

                   

        }

     

     

     

     

        protected void btnSendMail_Click(object sender, EventArgs e)

        {

     

            fn_AttachGrid();

     

        }

    }

     

     

  • Prevent Flicker from Ajax CollapsiblePanelExtender on Postback

    Let’s see how to fix the flickering of the CollapsiblePanelExtender when collapsed is set to true (Collapsed="true")

    Here is my CollapsiblePanelExtender

    <cc1:CollapsiblePanelExtender ID="CollapsePanel1" runat="server" SuppressPostBack="true" CollapseControlID="btnCancel" Collapsed="true" CollapsedText="Show Time" ExpandControlID="imgPostQuestion" ExpandedText="Hide Time" TargetControlID="panelPost"> cc1:CollapsiblePanelExtender>

    Your TargetPanel is here

    <asp:Panel ID="panelPost" runat="server" CssClass="cpBody">

    <div>

    //Ur collapsible Content

    div>

    asp:Panel>

    And now we are preventing the flickering by the CSS style and the style is as follows.

    .cpBody

    {

    height:0px;

    overflow: hidden;

    }

    There we found the solution for the flickering..

  • Ajax AutoComplete Extender Content Overridden Issue

    While using AutocompleteExtender, we could notice where the other controls are been overridden and the autocomplete extender text being overlapped by the other controls near by.This could be overcome by calling the OnClientShown property of the AutocompleteExtender control.

    <cc1:AutoCompleteExtender ID="ace" runat="server" OnClientShown="ShowOptions">

    cc1:AutoCompleteExtender>

    <script language="javascript" type="text/javascript">

    function ShowOptions(control, args) {

    control._completionListElement.style.zIndex = 10000001;

    }

    script>

  • Implementing Search In Datalist and Highlight Search Text

     Let us see how to highlight the search keywords to make it easy for the user to identify his search results better. To perform this am using the Regex function to Highlight the search keywords.

    This is the function used for highlighting ..

    protected string HighlightText(string searchWord, string inputText)

    {

    if (searchWord != null && searchWord != "")

    {

    Regex expression = new Regex(searchWord.Replace(" ", "|"), RegexOptions.IgnoreCase);

    return expression.Replace(inputText, new MatchEvaluator(ReplaceKeywords));

    }

    return inputText;

    }

    public string ReplaceKeywords(Match m)

    {

    return "" + m.Value + "";

    }

    To highlight the keyword u need to design your datalist lik this..

    <asp:DataList ID="DataList1" runat="server">

    <ItemTemplate>

    <asp:Label ID="label1" runat="server"

    Text='<%# Highlight(Eval("YourField").ToString()) %>'>

    asp:Label>

    ItemTemplate>

    asp:DataList>

    This ('<%# Highlight(Eval("YourField").ToString()) %>'>) is the one which highlights your Keyword. This is how we highlight the search keywords..