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