Validate File format and Size in ASP.NET using JAVASCRIPT

In this article, i am going to show how to valid file type and size of the file upload control in asp.net using JavaScript.

JavaScript Function

  <script type="text/javascript">
        // allowed file formats
        var validFilesType = ["bmp", "gif", "png", "jpg", "jpeg"];
        function ValidateFileTypeWithSize() {
            var fluploadImage = document.getElementById("<%=fluploadImage.ClientID%>");
            var lblMsg = document.getElementById("<%=lblMsg.ClientID%>");
            lblMsg.style.color = "red";
            lblMsg.innerHTML = '';
            var path = fluploadImage.value;
            // to get file extenstion like bmp, gif etc.
            var extension = path.substring(path.lastIndexOf(".") + 1, path.length).toLowerCase();
            var isValidFile = false;
            for (var i = 0; i < validFilesType.length; i++) {
                if (extension == validFilesType[i]) {
                    isValidFile = true;
                    // mention maximum size 
                    if (fluploadImage.files[0].size > 4194304) {
                        fluploadImage.value = ''; // to clear fileuploader content
                        lblMsg.innerHTML = "Each image file can't exceed 4 MB";
                    }
                    break;
                }
            }

            // execute only in case of any file format which was not mentioned in 'validFilesType' variable
            if (!isValidFile) {
                fluploadImage.value = ''; // to clear fileuploader content
                lblMsg.innerHTML = "File Not Valid. Please upload a File with" +
                 " extension:\n\n" + validFilesType.join(", ");
            }
            return isValidFile;
        }
    </script>

Controls

  <asp:FileUpload ID="fluploadImage" onchange="ValidateFileTypeWithSize()" runat="server" />
            <asp:Label ID="lblMsg" runat="server"></asp:Label>

Output

That’s it!!…..Happy Programming...

Something about Empty String

In this article, i am going to explain meaning of an Empty String in int type column.

create table tblEmptyString (SrNo int null)
insert into tblEmptyString (SrNo) values (1)
insert into tblEmptyString (SrNo) values (2)
insert into tblEmptyString (SrNo) values ('')
insert into tblEmptyString (SrNo) values (null)

select * from tblEmptyString

Output

As we can see, implicit conversion of the empty string to a value of 0 happened here. These Issues sometimes can create problems for us. So from now, we have to care about Empty String more.

That’s it!!…..Happy Programming...

CSS tooltip on hover

In this article, i am going to show how to apply custom styles on Tooltip.

Aspx Code

   <table style="width: 300px;">
        <tr>
            <td>Username:
            </td>
            <td>
                <a href="#" accesskey="Please enter your username" class="tooltipshow">
                    <input id="txtUsername" type="text" /></a>
            </td>
        </tr>
        <tr>
            <td>Password:
            </td>
            <td>
                <a href="#" accesskey="Please enter your password" class="tooltipshow">
                    <input id="txtPassword" type="text" /></a>
            </td>
        </tr>
        <tr>
            <td></td>
            <td>
                <input id="btnSubmit" type="button" value="Login" />
            </td>
        </tr>
    </table>

 CSS Code

.tooltipshow {
display:inline;
position:relative;
text-decoration:none;
top:0;
left:10px;
}

.tooltipshow:hover:after {
background:rgba(0,0,0,.8);
border-radius:5px;
top:-5px;
color:#fff;
content:attr(accesskey);
left:160px;
position:absolute;
z-index:98;
width:150px;
padding:5px 15px;
}

.tooltipshow:hover:before {
border:solid;
bottom:20px;
content:"";
left:155px;
position:absolute;
z-index:99;
top:3px;
border-color:transparent #000;
border-width:6px 6px 6px 0;
}

In CSS, we have set the content attribute to content: attr(accesskey); this property will display the tooltip by using the accesskey attribute of the anchor tag. Whatever we pass in to the accesskey attribute of the anchor tag it will be displayed as a tooltip. Have something to add to this post? Share it in the comments.

 Output

That’s it!!…..Happy Programming... 

Bind Category and Subcategory in asp.net

In this article, i am going to explain how to maintain Category and Subcategory hierarchy in asp.net.

Aspx Page

   <form id="form1" runat="server">
        <div>
            <asp:Label ID="lblMsg" runat="server"></asp:Label>
            <ul>
                <asp:Repeater ID="repCategory" runat="server" OnItemDataBound="repCategory_ItemDataBound">
                    <ItemTemplate>
                        <li>
                            <asp:Label Font-Size="Large" Font-Bold="true" ID="lblCategoryName" runat="server"
                                Text='<%# Eval("CategoryName") %>' />
                        </li>
                        <ul>
                            <asp:Repeater ID="repSubCategory" runat="server">
                                <ItemTemplate>
                                    <li style="background-color:lightblue">
                                        <asp:HyperLink ID="hlkSubCategory" runat="server" Text='<%# Eval("SubCategoryName")%>' />
                                    </li>
                                </ItemTemplate>
                            </asp:Repeater>
                        </ul>
                    </ItemTemplate>
                </asp:Repeater>
            </ul>
        </div>
    </form>

 CS Page

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;

public partial class Category : System.Web.UI.Page
{
    string strConnection = @"Data Source=AMREEK\SQLEXPRESS; uid=sa; pwd=12345;database=demo;";
    
    protected void Page_Load(object sender, EventArgs e)
    {
        BindData();
    }

    private void BindData()
    {
        try
        {
            SqlConnection myConnection = new SqlConnection(strConnection);
            SqlCommand myCommand = new SqlCommand("[dbo].[GetCategoryWithSubCategory]", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter ad = new SqlDataAdapter(myCommand);
            DataSet ds = new DataSet();
            ad.Fill(ds);
            // Attach the relationship to the dataSet with relationship name, parent column, child column
            ds.Relations.Add(new DataRelation("CategoriesRelation", ds.Tables[0].Columns["CategoryID"],
            ds.Tables[1].Columns["CategoryID"]));
            repCategory.DataSource = ds.Tables[0];
            repCategory.DataBind();
        }

        catch(Exception ex)
        { Response.Write(ex.Message); }

    }

    protected void repCategory_ItemDataBound(object sender, RepeaterItemEventArgs e)
    {
        if (e.Item.ItemType == ListItemType.Item ||
        e.Item.ItemType == ListItemType.AlternatingItem)
        {
            DataRowView drv = e.Item.DataItem as DataRowView;
            Repeater innerRep = e.Item.FindControl("repSubCategory") as Repeater;
            innerRep.DataSource = drv.CreateChildView("CategoriesRelation");
            innerRep.DataBind();
        }
    }
}

SQL Query 

-- category table --
create table category
(
categoryId int identity primary key not null,
categoryName nvarchar(100) not null
)

insert category values ('a')
insert category values ('b')
insert category values ('c')
select * from category


-- sub category table --
create table subcategory
(
categoryId int not null FOREIGN KEY REFERENCES category(categoryId),
subcategoryId int identity not null,
subcategoryName nvarchar(100) not null,
)

insert subcategory values (1,'one')
insert subcategory values (1,'two')
insert subcategory values (1,'three')
insert subcategory values (2,'one')
insert subcategory values (2,'two')
insert subcategory values (3,'one')
insert subcategory values (3,'two')
insert subcategory values (3,'three')
insert subcategory values (3,'four')
select * from subcategory



-- procedure to select both tables data --
Go
Create PROCEDURE [dbo].[GetCategoryWithSubCategory]
AS

-- only those categories selected whose sub-categories exist
SELECT * FROM category WHERE CategoryID IN
( SELECT CategoryID FROM subcategory )

-- select all records from subcategories, here p just used as alias
SELECT p.subcategoryId , p.SubCategoryName ,p.CategoryID FROM subcategory p
Go

 Output

That’s it!!…..Happy Programming...

How to Add Dynamic Rows in Gridview in ASP.NET

In this article, i am going to show how to add dynamic rows in gridview control..

aspx page

 <asp:GridView ID="gvRecords" runat="server" OnRowCommand="gvRecords_RowCommand" DataKeyNames="SrNo" AutoGenerateColumns="false">
        <Columns>
            <asp:TemplateField HeaderStyle-HorizontalAlign="Left" HeaderText="Sr No.">
                <ItemTemplate>
                    <asp:TextBox ID="txtSrNo" runat="server" Text='<%# Eval("SrNo") %>'></asp:TextBox>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderStyle-HorizontalAlign="Left" HeaderText="Name">
                <ItemTemplate>
                    <asp:TextBox ID="txtName" runat="server" Text='<%# Eval("Name") %>'></asp:TextBox>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderStyle-HorizontalAlign="Left" HeaderText="Salary">
                <ItemTemplate>
                    <asp:TextBox ID="txtAmount" runat="server" Text='<%# Eval("Salary") %>'></asp:TextBox>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:ButtonField ButtonType="Button" CommandName="cmd_delete" Text="Delete" />
        </Columns>
    </asp:GridView>
    <asp:Button ID="btnAddRow" runat="server" OnClick="btnAddRow_Click" Text="Add Row" />

cs page

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;

public partial class dynamicgridview : System.Web.UI.Page
{
    DataTable dt = new DataTable();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            gvRecords.DataSource = GetTableWithInitialData(); // get initial data
            gvRecords.DataBind();
        }
    }

    public DataTable GetTableWithInitialData()
    {
        DataTable table = new DataTable();
        table.Columns.Add("SrNo", typeof(string));
        table.Columns.Add("Name", typeof(string));
        table.Columns.Add("Salary", typeof(double));

        table.Rows.Add(1, "Manpreet", 25000);
        table.Rows.Add(2, "Gurpreet", 20000);
        table.Rows.Add(3, "Prabhjot", 33000);
        SaveGridInViewstate(table);
        return table;
    }

    protected void btnAddRow_Click(object sender, EventArgs e)
    {
        DataTable dt = GetTableWithNoData(); // get table structure by calling the specified function
        DataRow dr;

        foreach (GridViewRow gvr in gvRecords.Rows)
        {
            dr = dt.NewRow();

            TextBox txtSrNo = gvr.FindControl("txtSrNo") as TextBox;
            TextBox txtName = gvr.FindControl("txtName") as TextBox;
            TextBox txtAmount = gvr.FindControl("txtAmount") as TextBox;
            dr[0] = txtSrNo.Text;
            dr[1] = txtName.Text;

            if (txtAmount.Text == string.Empty)
            {
                dr[2] = DBNull.Value;
            }

            else
            {
                dr[2] = txtAmount.Text;
            }

            dt.Rows.Add(dr); // add grid values in to row and add row to the blank table
        }

        dr = dt.NewRow(); // add new empty row
        dt.Rows.Add(dr);

        gvRecords.DataSource = dt; // bind newly created datatable to grid
        gvRecords.DataBind();
        SaveGridInViewstate(dt);
    }

    public DataTable GetTableWithNoData() // returns only table structure 
    {
        DataTable table = new DataTable();
        table.Columns.Add("SrNo", typeof(string));
        table.Columns.Add("Name", typeof(string));
        table.Columns.Add("Salary", typeof(double));
        return table;
    }

    // save datatable into viewstate which would be useful for deletion
    public void SaveGridInViewstate(DataTable dt)
    {
        ViewState["gvRecords"] = dt;
    }

    protected void gvRecords_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName == "cmd_delete")
        {
            int index = Convert.ToInt32(e.CommandArgument);
            if (ViewState["gvRecords"] != null)
            {
                dt = ViewState["gvRecords"] as DataTable;
                DataRow row = dt.Rows[index];
                dt.Rows.Remove(row);
                gvRecords.DataSource = dt; // bind newly created datatable to grid
                gvRecords.DataBind();
            }
        }
    }
}

Output

That’s it!!…..Happy Programming...

How to disable html tags inserting in a TextBox

Javascript function

    <script type="text/javascript">

    var IsShiftPressed = false;
    function PreventHtml(Sender, e) {
        var key = e.which ? e.which : e.keyCode;
        // 16 code stands for shift key
        if (key == 16) {
            IsShiftDown = true;
        }
        // 188 code for '<', 190 for '>', 53 for '%' and 54 for '^'
        else if ((IsShiftDown == true) && ((key == 188) || (key == 190) || (key == 53) || (key == 54))) {
            return false;
        }
    }
    </script>

<asp:TextBox ID="txtPreventHtmlTags" runat="server" onkeydown="return PreventHtml(this,event);" />

That’s it!!…..Happy Programming...

Selecting Top N Records on the basis of Group in SQL

In this article, i am going to explain how to select Top N Records from each group in SQL. First you have to create two temporary table like this:-

CREATE TABLE [#Producttable](
    [ProductName] [NVARCHAR](50) NOT NULL,
    [ProductID] [INT] NOT NULL
) 

INSERT [#Producttable] ([ProductName], [ProductID]) VALUES ('abc', 707)
INSERT [#Producttable] ([ProductName], [ProductID]) VALUES ('bcd', 708)
INSERT [#Producttable] ([ProductName], [ProductID]) VALUES ('cde', 709)
INSERT [#Producttable] ([ProductName], [ProductID]) VALUES ('def', 710)
INSERT [#Producttable] ([ProductName], [ProductID]) VALUES ('fgh', 711)



CREATE TABLE [#Saleordertable](
    [SalesOrderID] [INT] NOT NULL,
    [ProductID] [INT] NOT NULL
) 
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71782, 707)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71783, 707)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71784, 707)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71782, 708)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71783, 708)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71784, 708)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71782, 711)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71783, 711)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71784, 711)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71782, 712)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71783, 712)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71784, 712)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71782, 714)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71783, 714)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71784, 714)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71782, 715)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71783, 715)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71784, 715)

Now we want to select only one salesorderID for each product which exist in '[#Producttable]' table. We have to do something like this:-

    SELECT 
         p.[ProductName], sod.SalesOrderID,
        sod.ProductID
    FROM [#Saleordertable] sod inner join [#Producttable] as p
    on sod.ProductID = p.ProductID
    WHERE sod.SalesOrderID IN
    (
        SELECT TOP 1 SalesOrderID
        FROM [#Saleordertable]
        WHERE ProductID = sod.ProductID
        ORDER BY SalesOrderID
    )

Output

That’s it!!…..Happy Programming...

How to calculate sum of columns total in gridview footer in ASP.NET

In this, I am going to explain how to display the sum of total of columns in gridview footer. I have a gridview with multiple rows and columns and wanna display sum of total of columns in gridview footer, that's why using DataBound.
protected void gridview1_DataBound(object sender, EventArgs e)
    {
        {
            int TotalRows = gridview1.Rows.Count; // to count total rows
            if (TotalRows > 0) // check if any rows exist
            {
                // to count total no. of column exist
                int TotalCol = gridview1.Rows[0].Cells.Count;

                gridview1.FooterRow.Cells[2].Text = "<b>" + "Total : " + "</b>";
                gridview1.FooterRow.Cells[2].ForeColor = System.Drawing.Color.Green;
                
                // 'i=3' means sum-up start from fourth column
                for (int i = 3; i < TotalCol; i++)
                {
                    // to skip any specific column sum
                    if (gridview1.HeaderRow.Cells[i].Text == "Remarks")
                    {
                        continue; // loop continues to execute with the next iteration
                    }
                    double sum = 0.0;

                    for (int j = 0; j < TotalRows; j++)
                    {
                        sum += gridview1.Rows[j].Cells[i].Text != "&nbsp;" ? double.Parse(gridview1.Rows[j].Cells[i].Text) : 0.00;
                    }
                    
                    // bind it to on the basis of their respective column(s)
                    gridview1.FooterRow.Cells[i].Text = "<b>" + sum.ToString() + "</b>";
                }
            }
        }
    }



That’s it!!…..Happy Programming...

How to avoid the divide by zero error in SQL Server

In this article, i am going to explain how to avoid divide by zero error in SQL. As we know, anytime we are dividing we need to think of the divide by zero scenario. Let’s look out below query

select
  count(sname) / count(*) as "Average"
from mytable

This will result in a division by error if there are no rows returned.

Solution 

This error can be avoided by using the NULLIF function, which returns a null value if the two passed-in expressions are equal.

Syntax of NULLIF Function :

NULLIF (aspect1, aspect2)

Here, aspect1 and aspect2 are any valid sql server expression.

select
  count(sname) / NULLIF( count(*), 0)
from mytable

So, if count(*) returns 0, a null value is returned. Any number divided by NULL gives NULL. While that isn’t a useful result but i think, it’s better than getting an error!! There you go. No more divide by zero errors in SQL.

That’s it!!…..Happy Programming...

How to remove ASP.NET membership provider objects from SQL Server

In this article, i am going to explain how to remove membership provider objects from SQL.

By Running the Command

%WINDIR%\Microsoft.NET\Framework\v4.0.30319\aspnet_regsql.exe

By Running the Query

drop table aspnet_PersonalizationAllUsers
drop table aspnet_PersonalizationPerUser
drop table aspnet_Profile
drop table aspnet_SchemaVersions
drop table aspnet_UsersInRoles
drop table aspnet_WebEvent_Events
drop table aspnet_Paths
drop table aspnet_Membership
drop table aspnet_Roles
drop table aspnet_Users
drop table aspnet_Applications

drop view vw_aspnet_Applications
drop view vw_aspnet_MembershipUsers
drop view vw_aspnet_Profiles
drop view vw_aspnet_Roles
drop view vw_aspnet_Users
drop view vw_aspnet_UsersInRoles
drop view vw_aspnet_WebPartState_Paths
drop view vw_aspnet_WebPartState_Shared
drop view vw_aspnet_WebPartState_User

drop procedure aspnet_AnyDataInTables
drop procedure aspnet_Applications_CreateApplication
drop procedure aspnet_CheckSchemaVersion
drop procedure aspnet_Membership_ChangePasswordQuestionAndAnswer
drop procedure aspnet_Membership_CreateUser
drop procedure aspnet_Membership_FindUsersByEmail
drop procedure aspnet_Membership_FindUsersByName
drop procedure aspnet_Membership_GetAllUsers
drop procedure aspnet_Membership_GetNumberOfUsersOnline
drop procedure aspnet_Membership_GetPassword
drop procedure aspnet_Membership_GetPasswordWithFormat
drop procedure aspnet_Membership_GetUserByEmail
drop procedure aspnet_Membership_GetUserByName
drop procedure aspnet_Membership_GetUserByUserId
drop procedure aspnet_Membership_ResetPassword
drop procedure aspnet_Membership_SetPassword
drop procedure aspnet_Membership_UnlockUser
drop procedure aspnet_Membership_UpdateUser
drop procedure aspnet_Membership_UpdateUserInfo
drop procedure aspnet_Paths_CreatePath
drop procedure aspnet_Personalization_GetApplicationId
drop procedure aspnet_PersonalizationAdministration_DeleteAllState
drop procedure aspnet_PersonalizationAdministration_FindState
drop procedure aspnet_PersonalizationAdministration_GetCountOfState
drop procedure aspnet_PersonalizationAdministration_ResetSharedState
drop procedure aspnet_PersonalizationAdministration_ResetUserState
drop procedure aspnet_PersonalizationAllUsers_GetPageSettings
drop procedure aspnet_PersonalizationAllUsers_ResetPageSettings
drop procedure aspnet_PersonalizationAllUsers_SetPageSettings
drop procedure aspnet_PersonalizationPerUser_GetPageSettings
drop procedure aspnet_PersonalizationPerUser_ResetPageSettings
drop procedure aspnet_PersonalizationPerUser_SetPageSettings
drop procedure aspnet_Profile_DeleteInactiveProfiles
drop procedure aspnet_Profile_DeleteProfiles
drop procedure aspnet_Profile_GetNumberOfInactiveProfiles
drop procedure aspnet_Profile_GetProfiles
drop procedure aspnet_Profile_GetProperties
drop procedure aspnet_Profile_SetProperties
drop procedure aspnet_RegisterSchemaVersion
drop procedure aspnet_Roles_CreateRole
drop procedure aspnet_Roles_DeleteRole
drop procedure aspnet_Roles_GetAllRoles
drop procedure aspnet_Roles_RoleExists
drop procedure aspnet_Setup_RemoveAllRoleMembers
drop procedure aspnet_Setup_RestorePermissions
drop procedure aspnet_UnRegisterSchemaVersion
drop procedure aspnet_Users_CreateUser
drop procedure aspnet_Users_DeleteUser
drop procedure aspnet_UsersInRoles_AddUsersToRoles
drop procedure aspnet_UsersInRoles_FindUsersInRole
drop procedure aspnet_UsersInRoles_GetRolesForUser
drop procedure aspnet_UsersInRoles_GetUsersInRoles
drop procedure aspnet_UsersInRoles_IsUserInRole
drop procedure aspnet_UsersInRoles_RemoveUsersFromRoles
drop procedure aspnet_WebEvent_LogEvent

drop schema aspnet_Membership_FullAccess
drop schema aspnet_Membership_BasicAccess
drop schema aspnet_Membership_ReportingAccess
drop schema aspnet_Personalization_BasicAccess
drop schema aspnet_Personalization_FullAccess
drop schema aspnet_Personalization_ReportingAccess
drop schema aspnet_Profile_BasicAccess
drop schema aspnet_Profile_FullAccess
drop schema aspnet_Profile_ReportingAccess
drop schema aspnet_Roles_BasicAccess
drop schema aspnet_Roles_FullAccess
drop schema aspnet_Roles_ReportingAccess
drop schema aspnet_WebEvent_FullAccess

drop role aspnet_Membership_FullAccess
drop role aspnet_Membership_BasicAccess
drop role aspnet_Membership_ReportingAccess
drop role aspnet_Personalization_FullAccess
drop role aspnet_Personalization_BasicAccess
drop role aspnet_Personalization_ReportingAccess
drop role aspnet_Profile_FullAccess
drop role aspnet_Profile_BasicAccess
drop role aspnet_Profile_ReportingAccess
drop role aspnet_Roles_FullAccess
drop role aspnet_Roles_BasicAccess
drop role aspnet_WebEvent_FullAccess
drop role aspnet_Roles_ReportingAccess

That’s it!!…..Happy Programming...

How to Create DataTable Programmatically in C#, ASP.NET

Many times we have the requirement where we have to create DataTable dynamically. In this Post, I have put the simple code for creating DataTable programmatically in C#.

        // Create a DataTable instance
        DataTable dtable = new DataTable();

        // Create columns for DataTable named 'dtable'
        DataColumn col1 = new DataColumn("ID");
        DataColumn col2 = new DataColumn("Name");
        DataColumn col3 = new DataColumn("City");
        DataColumn col4 = new DataColumn("State");

        // Define DataType of the Columns
        col1.DataType = System.Type.GetType("System.Int");
        col2.DataType = System.Type.GetType("System.String");
        col3.DataType = System.Type.GetType("System.String");
        col4.DataType = System.Type.GetType("System.String");

        // Add Columns into DataTable named 'dtable'
        dtable.Columns.Add(col1);
        dtable.Columns.Add(col2);
        dtable.Columns.Add(col3);
        dtable.Columns.Add(col4);

        // Create a Row in the DataTable named 'dtable'
        DataRow row = dtable.NewRow();

        // Fill All Columns with Data
        row[col1] = 111;
        row[col2] = "Demo";
        row[col3] = "My City";
        row[col4] = "My State";

        // Add the Row into DataTable
        dtable.Rows.Add(row);

Note:- DataTable is a class which comes under System.Data namespace of Dot Net Framework. So you have to include this namespace before using it.

That’s it!!…..Happy Programming...

How to Set MaxLength property for ASP.NET Multiline Textbox

Once there was a requirement in a project where I had to set the max length of TextBox in Multiline mode so I just set the MaxLength property of TextBox to the length which was required but it doesn’t worked. When i Searched on this, i found that when we put TextBox in our WebForm it’ll be rendered to HTML <input> tag but when we set the TextMode to multiline it’ll be rendered to <textarea> tag not an <input > tag, and MaxLength attribute is in <input> but it’s not for <textarea>. That's why i had to face this problem. Finally, I solved this problem by making javascript function.

aspx page

<asp:TextBox ID="txtName" runat="server" TextMode="MultiLine" onkeypress="return SetMaxLength(this,10);">

javascript function

    // maxLength value supplied by user
      function SetMaxLength(txt, maxLength) {

          if (txt.value.length >= (maxLength)) {
                return false;
          }
          else {
                return true;
          }
      }

As you can see, i am passing 10 in it that's why it will make the max number of characters user can input in the TextBox is 10. You can set it to accrording to your requirement. That's it.. Hope it will help you..

That’s it!!…..Happy Programming...

How to find non-matching rows using EXCEPT in SQL

In this article, i am going to explain how to select non-matching rows using EXCEPT in SQL. I am creating two temporary table to demonstrate the same.In Short, EXCEPT operator returns all distinct rows from left hand side table which does not exist in right hand side table. One more interesting point to know, NOT IN also works like EXCEPT operator but it will not remove duplicate rows from the result.

to create and insert table data

create table #one (id int,name nvarchar(50))
insert into #one (id , name) values(1,'a') 
insert into #one (id , name) values(2,'b') 
insert into #one (id , name) values(3,'c') 

create table #two (id int,name nvarchar(50))
insert into #two (id , name) values(1,'a') 
insert into #two (id , name) values(3,'b') 
insert into #two (id , name) values(4,'c') 

to select by using query

select id from #one EXCEPT select id from #two 

Output

One basic rule of EXCEPT operator, you must have to specify number of columns and order same in all queries.

That’s it!!…..Happy Programming...

Persisting Row Selection of Data Controls in ASP.NET

Some times we need show large amount of data like hundred of rows but its very difficult to show that in a single web page so we use the paging mechanism to handle this kind of situation. ASP.NET Data Controls row selection feature was based on row index , this of course produce an issue if you try to select an item in the first page then navigate to the second page without select any record you will find the same row selected in the second page( this occurs due to same row index).

For Example:

• Select the third row in the GridView.
• Navigate to second page without doing any selection
• You will find the third row in the second page selected.

I discovered that Microsoft has introduced a new property in ASP .Net 4.0 for data bound control like GridView,ListView named as EnablePersistedSelection. Now you are thinking why EnablePersistedSelection introduced ??

It is a new feature which replace the old selection mechanism which based on row index to be based on the row data key instead.

So now by making EnablePersistedSelection="True", if a row is selected on first page, later you move to next page, no row is selected on the next page but on returning back to first page, old row is still selected.Here is the code for that:-

 <asp:GridView ID="PersistedGridView" runat="server" EnablePersistedSelection="true">
 </asp:GridView>

Don't forget to add the DataKeyNames when you enable this property, since the property is fully based on the DataKeyNames associated with that row.If not, following error occurs:-

That’s it.. Hope it will help you..

Happy Programming...