Thursday, 27 July 2017

Dynamically adding and removing form using jquery

Leave a Comment

Hi every one welcome again.It took me long to post on this blog.But i hope this article is very helpful for web developers.
In this article i will get to know about How to create,remove and save dynamically created form like picture above.This task is purely based on JQuery scripting.But,i you will easily understand the code using comments i wrote.And i will share my source code also..

Step1:
1.Create Visual studio solution Web solution(Using ASP.NET MVC template).If you do not know how to create solution refer link: creating-first-application-in-aspnet-mvc.
2.Add a Controller to the solution.
3.Now in controller i prepared a Data source for drop down list and sent that data to view using ViewBag .
HomeController.cs:
public class HomeController : Controller
    {
        // GET: Home
        public ActionResult Index()
        {
            //preparing dropdownlist data..
            List _ddllist = new List()
            {
                new SelectListItem { Text = "Select", Value = "Select" },
                new SelectListItem { Text = "Student", Value = "Student" },
                new SelectListItem { Text = "Professor", Value = "Professor" },
                new SelectListItem { Text = "Volunteer", Value = "Volunteer" },
                new SelectListItem { Text = "Organizer", Value = "Organizer" }
            };
            //sending using ViewBag..
            ViewBag.RegisterTypes = _ddllist;
            return View();
        }
    }
Step2:
1.Add a  view by right clicking on Index controller.
2.Add Jquery and Jquery UI library file in _Layout page or View page created.
<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<link href="~/Content/jquery-ui/theme/jquery-ui.1.11.1.css" rel="stylesheet" />
<script src="~/Scripts/jquery-ui-1.10.0.min.js"></script>

Step3:
1.Now create view with below code.Here, i am not giving any explanation about how to write code but,you can easily understand by seeing code with documentation comments.
@{
    ViewBag.Title = "Index";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<link href="~/Content/jquery-ui/theme/jquery-ui.1.11.1.css" rel="stylesheet" />
<script src="~/Scripts/jquery-ui-1.10.0.min.js"></script>

@using (Html.BeginForm("", "", FormMethod.Post, new { id = "frmEventAttFee" }))
{
    <div id="DivAddFeeDetails" style="display:block;margin-top:50px;">
        <table id="tblEventAttendeeFee">
            <tbody>
                <tr>
                    <td><span style="width:100px;">Reg Types</span></td>
                    <td><span style="width:70px;">Start Date</span></td>
                    <td><span style="width:70px;">End Date</span></td>
                    <td><span style="width:50px;">Seats Avilable</span></td>
                    <td><span style="width:70px;">Amount</span></td>
                    <td><span style="width:70px;">CodeRequired</span></td>
                    <td><span style="width:70px;">Amount</span></td>
                </tr>
                <tr>
                    <td>
                        @Html.DropDownList("ddlRegTypes_0", ViewBag.RegisterTypes as List<SelectListItem>, new { style = "width:100px",@class="form-control" })
                    </td>
                    <td><input class="form-control" style="width:70px;" id="txtRegStartDate_0" type="text" /></td>
                    <td><input class="form-control" style="width:70px;" id="txtRegEndDate_0" type="text" /></td>
                    <td><input class="form-control" style="width:50px;" id="txtNoOfSeatsAvilable_0" type="text" /></td>
                    <td><input class="form-control" style="width:70px;" id="txtAmount_0" type="text" /></td>
                    <td><input class="form-control" type="checkbox" id="chkPromotionCode_0" /></td>
                    <td><input class="btn btn-primary" type="button" id="btnAddNew" value="Add New" /></td>
                </tr>                
            </tbody>
        </table>
    </div>
    <hr />

    <input type="button" class="btn btn-success btn-lg" id="btnSubmit" name="btnSubmit" value="Submit" />
    <input type="hidden" id="hdnCountOfRows" name="hdnCountOfRows" />
}

<script type="text/javascript">

        $(document).ready(function () {
        
        //getting the checkbox value
        if ($("#chkPromotionCode_0").is(':checked')) {
            $("#chkPromotionCode_0").attr('checked', false);
        }
        //initializing date pickers.
        $("#txtRegStartDate_0").datepicker();
        $("#txtRegEndDate_0").datepicker();
        var count = 1;
        
            //creating dynamic new form when click on 'Add New' button
            //and appending to table
        $("#btnAddNew").on("click", function () {
            //count=1;
            $("#tblEventAttendeeFee").append('<tr id="row_'+count+'">'
            + '<td><select class="form-control" style="width:100px;" id="ddlRegTypes_' + count + '" name="ddlRegType_' + count + '"/></select></td>'
            + '<td><input class="form-control" style="width:70px;" type="text" id="txtRegStartDate_' + count + '" name="txtRegStartDate_' + count + '"/></td>'
            + '<td><input class="form-control" style="width:70px;" type="text" id="txtRegEndDate_' + count + '" name="txtRegEndDate_' + count + '"/></td>'
            + '<td><input class="form-control" style="width:50px;" type="text" id="txtNoOfSeatsAvilable_' + count + '" name="txtNoOfSeatsAvilable_' + count + '"/></td>'
            + '<td><input class="form-control" style="width:70px;" type="text" id="txtAmount_' + count + '" name="txtAmount_' + count + '"/></td>'
            + '<td><input class="form-control" type="checkbox" id="chkPromotionCode_' + count + '" /></td>'
            + '<td><input class="btn-remove btn btn-danger" type="button" id="' + count + '" name="' + count + '" value="Remove" /></td>'
            + '</tr>');

            //copying dropdown list data to a variable
            var ddldata = $("#ddlRegTypes_0").html();
            //appending dropdownlist data to dynamically created Dropdownlist
            $("#ddlRegTypes_" + count + "").append(ddldata);
            //initializing dynamically created datepickers
            $("#txtRegStartDate_"+count+"").datepicker();
            $("#txtRegEndDate_"+count+"").datepicker();
            count++;
            $("#hdnCountOfRows").val(count);
        });
        $("#hdnCountOfRows").val(count);

        //Removing dynamic form when click on remove button.
        $(document).on("click", ".btn-remove", function () {
            count=count - 1;
            var button_id = $(this).attr("id");
            $("#row_" + button_id + "").remove();
        });
    });

    //Saving data ww entered to a javascript list 
    $("#btnSubmit").unbind().click(function () {
        var DataList = [];
        var count = $("#hdnCountOfRows").val();
        for (var i = 0; i < count; i++) {
            if ($("#chkPromotionCode_" + i).prop('checked') == true) {
                $("#chkPromotionCode_" + i).val(true);
            }
            else {
                $("#chkPromotionCode_" + i).val(false);
            }
            if ($("#ddlRegTypes_" + i).val() && $("#txtRegStartDate_" + i).val() && $("#txtRegEndDate_" + i).val() && $("#txtNoOfSeatsAvilable_" + i).val() && $("#txtAmount_" + i).val())
                DataList.push({ RegisterTypeCode: $("#ddlRegTypes_" + i).val(), RegStartDate: $("#txtRegStartDate_" + i).val(), RegEndDate: $("#txtRegEndDate_" + i).val(), NoOfSeatsAvilable: $("#txtNoOfSeatsAvilable_" + i).val(), Amount: $("#txtAmount_" + i).val(), PromotionCodeRequired: $("#chkPromotionCode_" + i).val()});
        }
        if ((DataList != null && DataList.length == 0) || DataList == null) {
            alert('please select RegType, Start date, End date, No of seats avilable and Amount for atlease one entry ');
            return false;
        }
        else {
            //displaying list data in alert box
            alert(JSON.stringify(DataList));
        }
        
        @*
            //We can send data to Controller using ajax
        DataList = JSON.stringify({ 'DataList': DataList });
        $.ajax({
            contentType: 'application/json; charset=utf-8',
            dataType: 'json',
            type: 'POST',
            url: 'AddEventAttendeeFee',
            data: DataList
        }).done(function (Result) {
            if (Result == true) {
                alert("Records Inserted successfully");
                var eventid = $("#hdnEventID").val();
                document.forms[0].action = "@Url.Action("EventFeeLookup", "Admin")?eventid=" + eventid;
                document.forms[0].submit();
            }
        }).error(function (Result) {
            alert("Insertion failed");
        });
            *@
    });

</script>
Note:
1.If your form contains more fields(ex:above 10 fields) it is recommend to create dynamic form by taking a Partial View.because it is not recommend in coding standards.
2.Finally after clicking submit button saved data will come in alert box for two rows like below.

3.Download source code for this application from here.. Download.

Conclusion:
I hope this tutorial will help you .To get more updates please subscribe to this blog subscribe. OR follow on Social networks for daily awesome updates[Facebook,twitter,google plus].

Monday, 3 July 2017

How to hide and display no records message in jqgrid

Leave a Comment
Hi every one welcome again.In this tutorial i am going to explain how to hide jqgrid when there is no records and display an message that states 'no records found'.Recently i come across this problem.I searched for online but i did not find suitable solution to my problem.after searching sometime finally i found this solution.I want to share this solution to help the people who also work with jqgrid.

This solution is understandable for developers who have knowledge on jqgrid.If you are newbie then refer this link to know about jqgrid.Implementing jqgrid in asp.net mvc.

I implemented jqgrid with below code..
JqController.cs:
using System;
using System.Data;
using System.Linq;
using System.Data.Entity;
using System.Web.Mvc;
using Jqgrid.Models;
using System.Collections.Generic;

namespace Jqgrid.Controllers
{
    public class JqgridController : Controller
    {
        public ActionResult Index()
        {
            return View();
        }
        //sample data to display in jqgrid
        public List GridData()
        {
            List userlist = new List()
            {
                new User {Id=1,Name="Raviteja",Address="Ongole",DOB="10-02-1994",Phone=1200439495 },
                new User {Id=2,Name="Koti",Address="Addanki",DOB="5-02-2017",Phone=1200439495 },
                new User {Id=3,Name="Sridhar",Address="Nellore",DOB="6-02-1997",Phone=1200439495 },
                new User {Id=4,Name="Rajkumar",Address="Kammam",DOB="9-02-1991",Phone=1200439495 },
                new User {Id=5,Name="Avinash",Address="Ongole",DOB="2-02-1994",Phone=1200439495 }
            };
            return userlist;
        }
        //Method to display jqgrid
        public JsonResult GetValues(string sidx, string sord, int page, int rows)  //Gets the todo Lists.
        {
            int pageIndex = Convert.ToInt32(page) - 1;
            int pageSize = rows;
            List userlist = new List();
            userlist = GridData();
            var Results = userlist.Select(
                    a => new
                    {
                        a.Id,
                        a.Name,
                        a.Phone,
                        a.Address,
                        a.DOB,
                    });
            int totalRecords = Results.Count();
            var totalPages = (int)Math.Ceiling((float)totalRecords / (float)rows);
            if (sord.ToUpper() == "DESC")
            {
                Results = Results.OrderByDescending(s => s.Id);
                Results = Results.Skip(pageIndex * pageSize).Take(pageSize);
            }
            else
            {
                Results = Results.OrderBy(s => s.Id);
                Results = Results.Skip(pageIndex * pageSize).Take(pageSize);
            }
            var jsonData = new
            {
                total = totalPages,
                page,
                records = totalRecords,
                rows = Results
            };
            return Json(jsonData, JsonRequestBehavior.AllowGet);
        }
    }
}
Index.cshtml:
@{
    ViewBag.Title = "Jqgrid";
}

<h2>Jqgrid Application</h2>
<div id="griddisplaylookup">
    <table id="grid"></table>
    <div id="pager"></div>
</div>

<div id="NoRecordsFound" style="display:none;text-align:center;color:red;">No records found</div>

@*<link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">*@
<link href="~/Content/themes/base/jquery-ui.css" rel="stylesheet" />
<link href="~/Content/jquery.jqGrid/ui.jqgrid.css" rel="stylesheet" />
<script src="~/Scripts/jquery-1.10.2.js"></script>
<script src="~/Scripts/jquery.jqGrid.js"></script>
<script src="~/Scripts/i18n/grid.locale-en.js"></script>
<script src="~/Scripts/myjqgrid.js"></script>

1.Jqgrid provides an event called 'gridComplete' will be fired after grid loading completed.
2.I implemented logic to hide and display the 'No records found' message in call back function of this event like below.
//this event fires when grid completes..
        gridComplete: function () {
            debugger;
            //i am getting the number of records in grid...
            var records = parseInt(jQuery("#grid").jqGrid('getGridParam', 'records'));
            //if number of records are 0 then grid will be hided
            //and a div element will showed with 'No records found'
            if (records > 0) {
                $("#griddisplaylookup").show();
                $("#NoRecordsFound").hide();
            }
            else if (isNaN(records)) {
                $("#griddisplaylookup").hide();
                $("#NoRecordsFound").show();
            }
            else {
                $("#griddisplaylookup").hide();
                $("#NoRecordsFound").show();
            }
        }
3.The full script code is like below..
$(function () {
    debugger;
    $("#grid").jqGrid({
        url: "/Jqgrid/GetValues",
        datatype: 'json',
        mtype: 'Get',
        //table header name 
        colNames: ['Id', 'Name', 'Phone', 'Address', 'DOB'],
        //colModel takes the data from controller and binds to grid 
        colModel: [
            { key: true, hidden: true, name: 'Id', index: 'Id', editable: true },
            { key: false, name: 'Name', index: 'Name', editable: true },
            { key: false, name: 'Phone', index: 'Phone', editable: true },
            { key: false, name: 'Address', index: 'Address', editable: true },
            { key: false, name: 'DOB', index: 'DOB', editable: true, formatter: 'date', formatoptions: { newformat: 'd/m/Y' } }],

        pager: jQuery('#pager'),
        rowNum: 10,
        rowList: [10, 20, 30, 40],
        height: '100%',
        viewrecords: true,
        caption: 'Jq grid sample Application',
        emptyrecords: 'No records to display',
        jsonReader: {
            root: "rows",
            page: "page",
            total: "total",
            records: "records",
            repeatitems: false,
            Id: "0"
        },
        autowidth: true,
        multiselect: false,
        //this event fires when grid completes..
        gridComplete: function () {
            debugger;
            //i am getting the number of records in grid...
            var records = parseInt(jQuery("#grid").jqGrid('getGridParam', 'records'));
            //if number of records are 0 then grid will be hided
            //and a div element will showed with 'No records found'
            if (records > 0) {
                $("#griddisplaylookup").show();
                $("#NoRecordsFound").hide();
            }
            else if (isNaN(records)) {
                $("#griddisplaylookup").hide();
                $("#NoRecordsFound").show();
            }
            else {
                $("#griddisplaylookup").hide();
                $("#NoRecordsFound").show();
            }
        }
        //pager-you have to choose here what icons should appear at the bottom
        //like edit,create,delete icons
    }).navGrid('#pager', { edit: true, add: true, del: true, search: false, refresh: true });
});
4.Now grid with records look like below...
5.The grid will like below if there is no records in jqgrid.(i commented data in controller).

Download source code for this application from here...Download

Conclusion:
I hope this tutorial will help you .To get more updates please subscribe to this blog subscribe. OR follow on Social networks for daily awesome updates[Facebook,twitter,google plus].

Friday, 30 June 2017

How to remove close button on jquery UI dialog

Leave a Comment
This tutorial tells us how to remove close button ('X' button at top right side of the jquery UI dialog).
1.This close button is by default will displayed in the ui dialog.There is no options in dialog also.So we have to do it using CSS.
2.Before to implement code we have to know about the Open event in jquery dialog.
open: This event is triggered when dialog is opened.
syntax:
$( ".selector" ).dialog({
  open: function( event, ui ) {}
});
3.Now come to the actual question to remove the close button ..use this below code..
$("#div2").dialog({
    closeOnEscape: false,
    open: function(event, ui) {
        $(".ui-dialog-titlebar-close", ui.dialog | ui).hide();
    }
});
4.Above code removes the close button on particular dialog.
5.To hide the close button on all dialogs you can use below CSS code..
.ui-dialog-titlebar-close {
    visibility: hidden;
}
Conclusion:
I hope  this tip or tutorial will help you in real time development...(i already come across this issue in my work)..

Wednesday, 28 June 2017

How to Delete Duplicate records from Table in Sql Server

Leave a Comment
In this post i will  show you how to delete or eliminate redundant(duplicate) rows with same data(including id column also).I will demonstrate this using  a simple example..
1.Create a table with below schema..and insert some data...
Employee.sql
CREATE TABLE [dbo].[Employee](
 [EmpId] [int] NULL,
 [EmpName] [varchar](50) NULL,
 [EmpMobi] [int] NULL
) ON [PRIMARY]

INSERT INTO Employee(EmpId,EmpName,EmpMobi) VALUES(1,'Varma',954455)
INSERT INTO Employee(EmpId,EmpName,EmpMobi) VALUES(2,'Gopal',783493)
INSERT INTO Employee(EmpId,EmpName,EmpMobi) VALUES(3,'Ragav',123456)
INSERT INTO Employee(EmpId,EmpName,EmpMobi) VALUES(3,'Ragav',123456)
INSERT INTO Employee(EmpId,EmpName,EmpMobi) VALUES(3,'Ragav',123456)
INSERT INTO Employee(EmpId,EmpName,EmpMobi) VALUES(4,'Suresh',984357)
2.Now i have 3 records with duplicate details.Now my task is to remove to two records from three.It is not possible to delete using 'delete' command because it requires one column value.
3.If i apply delete command all three records will be deleted.but,i need one record(i want to remove duplicate records only).
3.I created a temporary Object and stored above table in it..
select * into #temp from Employee

select * from #temp

4.Now run the below query to remove the duplicate records from the Temporary Table..

; with cte
as(

select * ,ROW_NUMBER() over (partition by EmpId,EmpName,EmpMobi order by EmpId)as rownumber from #temp
)
delete from cte where rownumber>1
5.We will get a table without duplicate records like below after executing above query..


Monday, 5 June 2017

Bulk insert into Sql server using Excel sheet

Leave a Comment
Hi every welcome back again.I took a long time to upload a new post in this blog.I was very busy with the production work of my company project this time.In today post i will explain how to import Bulk data into Sql server tables using Excel sheet.For this we need to write any programming.Microsoft has made this facility available for their client and provided an interface in Sql server.I will ran you through that process.Like Excel import we can also import the Microsoft Access database data.We will talk about it in further posts.

Importing or Bulk insert in Sql server using Excel sheet  

Step 1: 
1.Prepare an Excel document with bulk data as shown below.


2.This Excel document contains 33 columns and 6604 rows.
Note: If you have already have table in your sql server make sure that the headers of the each column matched with column names of respective table.
If there is no table in server Sql server automatically creates a table with table name as excel sheet name(ex:Person.xls) and table columns with names of the headers of excel columns(ex:Person_Id,FirstName,LastName etc).
Step 2:
1.Open Sql server Management Studio 2012 or higher..
2.Open Object explorer and navigate to the database where you want to perform bulk insert..
3.Here i created a MitechdevDB and i will insert data into it.
4.Right click MitechdevDB--> Tasks --> Import data.

5.Next Sql server Import and Export Wizard window will appear like below.


6.In this window select Data source,Excel file path and Excel version and check the checkbox.
Note: Please close the Excel sheet that you want to insert into Sql server if it is opened..Otherwise it will show error like below.


7.After clicking next it will ask for destination and server credentials.



8.after that choose one of the below options.I chooses option1


9.I already created a Table in sql server with below schema code.
GO
/****** Object:  Table [dbo].[Person]    Script Date: 5/24/2017 7:30:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Person](
 [Person_Id] [int] IDENTITY(1,1) NOT NULL,
 [Prefix] [nvarchar](225) NULL,
 [FirstName] [nvarchar](225) NOT NULL,
 [MiddleName] [nvarchar](225) NULL,
 [LastName] [nvarchar](225) NOT NULL,
 [Title] [nvarchar](225) NULL,
 [Company] [nvarchar](225) NULL,
 [Industry] [nvarchar](225) NULL,
 [Address1] [nvarchar](225) NULL,
 [Address2] [nvarchar](225) NULL,
 [City] [nvarchar](225) NULL,
 [State] [nvarchar](225) NULL,
 [Zip] [int] NULL,
 [Country] [nvarchar](225) NULL,
 [Work_Phone] [nvarchar](225) NULL,
 [Home_Phone] [nvarchar](225) NULL,
 [Mobile_Phone] [bigint] NULL,
 [Fax] [nvarchar](225) NULL,
 [Email] [nvarchar](225) NOT NULL,
 [Secondary_Email] [nvarchar](225) NULL,
 [Person_Status] [int] NULL,
 [Username] [nvarchar](225) NULL,
 [Password] [nvarchar](225) NULL,
 [HintQuestion] [nvarchar](225) NULL,
 [HintAnswer] [nvarchar](225) NULL,
 [FirstLoggedIn] [int] NULL,
 [Role_Id] [int] NOT NULL,
 [Status] [int] NULL,
 [Created_By] [nvarchar](225) NULL,
 [Created_Date] [datetime] NULL,
 [Modified_By] [nvarchar](225) NULL,
 [Modified_Date] [datetime2](7) NULL,
 [Alumnus] [nvarchar](20) NULL,
 CONSTRAINT [PK_MGCS_PERSON] PRIMARY KEY CLUSTERED 
(
 [Person_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
10.Now select Tables and views.We can edit the mappings and preview the table with data..


11.We can table schema(if we have not created table in sql server).
12.I already created a table in server.so i select Person table from Dropdown.


13.Now review the DataType Mapping in next windows.


14.Now click on Next button


15.we can also encrypt the sensitive data using password but here i am not using encryption.
16.Now finally we will get below screen that shows the status of the operation.

17.Now check the inserted data in Database...

 We got 6603 records inserted into the sql server at a time...

Conclusion

I hope this tutorial may helpful for you.In next tutorial i will come with another topic
If you are not subscribed to this blog subscribe.follow on Social networks for daily awesome updates[Facebook,twitter,google plus].