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