8 September 2013

Watermark Text for Textbox by using JavaScript - DotNet Brother

This is the simple demonstration in JavaScript how to create watermark text for textbox control. This requirement is the mostly used in web application.

Here the given codes to create the watermark text for textbox control.

For Example:

<title>Sample for Watermark Text Using JavaScript</title>

function FuncWaterMark(txtEmail, event) {

var strVal = "Enter EmailID Here";

//Here to check textbox length and event type

if (txtEmail.value.length == 0 & event.type == "blur") {

txtEmail.style.color = "Gray";//setting text color

txtEmail.value = strVal; //setting default text in textbox

}

// Here to check textbox value and event type

if (txtEmail.value == strVal & event.type == "focus") {

txtEmail.style.color = "black";

txtEmail.value = "";

}

</script>

</head>

<body>

<div>

<b>Email ID:</b>

<asp:TextBox ID="txtEmailID" runat="server" Text="Enter EmailID Here" ForeColor="Gray"

onblur="FuncWaterMark(this, event);" onfocus="FuncWaterMark(this, event);" />

</div>

</form>

</body>

</html>

Output:

How to Drop Primary Key Constraint - DotNet Brother

Here I described how to drop primary key form table. There are two way for doing this.

One is when we are not providing constraint key name during creating table.

Second is given constraint name during creating table.

Without giving constraint key

First create a table in SQL server with primary key only like below code.

create table info

(

id int primary key,

[name] varchar(50) not null,

address varchar(50) not null

)

Copy the key value from created table ‘Keys’. This key automatic created by sql server.

Write code for drop primary key

alter table info

drop PK__info__1A14E395

With giving constraint key

Create table like below

create table info

(

id int,

[name] varchar(50) not null,

address varchar(50) not null

constraint pk_info_id primary key clustered(id asc)

)

Drop primary key

alter table info

drop pk_info_id

DELETE, TRUNCATE with RESEED Identity in SQL Server - DotNet Brother

There is miner difference between DELETE, TRUNCATE with RESEED Identity in SQL Server. For demonstration first we create a table with Identity column then perform DELETE and TRUNCATE operation.

-- CREATE TABLE

CREATE TABLE STUDENT_INFO

(

[ID] INT IDENTITY(1,1) PRIMARY KEY,

[ROLL_NO]INT NOT NULL,

[NAME] VARCHAR(50) NOT NULL,

)

-- INSERT RECORDS

INSERT INTO STUDENT_INFO VALUES(101,'HAIDAR')

INSERT INTO STUDENT_INFO VALUES(102,'ARUN')

-- DISPLAY TABLE RECORD

SELECT * FROM STUDENT_INFO

Screen Shot

DELETE, TRUNCATE with RESEED Identity in SQL Server

Effect of DELETE statement

Delete records from table using DELETE command then insert new record and see table records.

-- DELETE RECORDS

DELETE FROM STUDENT_INFO

--INSERT SOME NEW RECORD

INSERT INTO STUDENT_INFO VALUES(103,'MANOJ')

INSERT INTO STUDENT_INFO VALUES(104,'SAURABH')

-- DISPLAY TABLE RECORDS

SELECT * FROM STUDENT_INFO

Screen Shot

Here we see, When the DELETE statement is executed without WHERE clause it will delete all the rows. However, when a new record is inserted the identity value (ID column) is increased from 2 to 3. It does not reset but keep on increasing.

Effect of TRUNCATE statement

Delete records from table using TRUNCATE command then insert new record and see table records.

-- TRUNCATE RECORDS

TRUNCATE TABLE STUDENT_INFO

-- INSERT NEW RECORDS

INSERT INTO STUDENT_INFO VALUES(105,'SANDEEP')

INSERT INTO STUDENT_INFO VALUES(106,'ROHIT')

-- DISPLAY TABLE RECORDS

SELECT * FROM STUDENT_INFO

Screen Shot

Now we see, When the TRUNCATE statement is executed it will remove all the rows. However, when a new record is inserted the identity value (ID column) is increased from 1 (which is original value). TRUNCATE resets the identity value to the original seed value of the table.

Create a user define function using SQL Server 2008 R2 - DotNet Brother

In this blog I am trying to explain how create a user define function using SQL Server 2008 R2.

In the SQL Server 2008 R2 have many predefined functions aggregate functions and Scalar functions which are worked for resolve our complex problems, and we are also known the method or functions resolve the complexity.

Aggregate functions:

SQL aggregate functions return a single value, which is calculated from values in a column. Such as

1. AVG () - Returns the average value.

2. COUNT () - Returns the number of rows.

Scalar functions:

SQL scalar functions return a single value, based on the input value.

1. UCASE () - Converts a field to upper case.

2. LCASE () - Converts a field to lower case.

UDF (User Define Function):

UDF is the concept of User-Defined Functions that allow you to define your own T-SQL functions that can allow zero or more parameters and return a single scalar data value or a table data type.

We can create three types of UDF in SQL Server 2008 R2 and they are:

1. Scalar

2. Inline Table-Valued

3. Multi-statement Table-valued.

Below I am giving an example for User Define Scalar Function which is single parameterizes function.

Function:

CREATE FUNCTION whichCountry

(@City nvarchar(15))

RETURNS varchar(30)

BEGIN

declare @Return varchar(30)

select @return = case @City

when 'New Delhi' then 'India'

when 'Banglore' then 'India'

when 'Colombo' then 'SriLanka'

when 'New York' then 'U.S.A'

when 'Chicago' then 'U.S.A'

when 'Dhaka' then 'Bangladesh'

when 'Beijing' then 'China'

when 'Kyoto' then 'Japan'

else 'Unknown'

end

return @return

end

Above function will accept a value City name and return its Country name .

Now we will create a table:

Create table tblEmployeeCity

(

vEmpID varchar(10),

eName varchar(50),

vCity varchar(50)

)

Insert Values in table:

INSERT INTO [dbo].[tblEmployeeCity] VALUES ('E001','Ajay Shukla','New York')

INSERT INTO [dbo].[tblEmployeeCity] VALUES ('E002','Sanjay Shukla','Beijing')

INSERT INTO [dbo].[tblEmployeeCity] VALUES ('E003','Vijay Shukla','Banglore')

Create a user define function using SQL Server 2008 R2

Use our function:

select Country=dbo.whichCountry(tblEmployeeCity.vCity), tblEmployeeCity.*

from tblEmployeeCity

Above statement will make a virtual column Country’s name and whichCountry () method accepts the value of vCity column and whichCountry () method match the vCity columns values in its code such as if value is Banglore then whichCountry () method will return the India. After that below will show on your screen:

Below I’m using the UDF whichCountry () method during creating a table:

create table tblEmployeeFullInformation

(

vName varchar(50),

vCity varchar(15),

vCountry as (dbo.WhichCountry(vCity))

)

When we will insert values in the tblEmployeeFullInformation table we need only name and Employee Name and its city but country name will automatically get via whichCountry () method.

Insert Values in tblEmployeeFullInformation:

INSERT INTO tblEmployeeFullInformation (vName,vCity) VALUES ('Vijay Shukla','Banglore')

INSERT INTO tblEmployeeFullInformation (vName,vCity) VALUES ('Ajay Shukla','New York')

INSERT INTO tblEmployeeFullInformation (vName,vCity) VALUES ('Sanjay Shukla','Beijing')

Inline table Value:

Its returns a table data type and is an extravagant alternative to a view as the user-defined function, it will take parameters into a T-SQL select command and in synopsis provide us with a parameterized, non-updateable view of the underlying tables.

CREATE FUNCTION EmployeeByCountry

(@Country varchar(30))

RETURNS TABLE

RETURN

SELECT dbo.WhichCountry(tblEmployeeCity.vCity) as tblEmployeeFullInformation,

tblEmployeeCity.*

FROM tblEmployeeCity

WHERE dbo.WhichCountry(tblEmployeeCity.vCity) = @Country

Above code will create a table with EmployeeByCountry name. And when this table will execute with T-SQL select command then we also need to pass a parameter with the table name below is example:

select * from EmployeeByCountry('India')

Output:

Multi-statement Table-valued.

CREATE FUNCTION Employee

( @ID varchar(50) )

RETURNS

@EmployeeInfo table (

Emp_name Varchar(50),

Emp_City Varchar(20)

)

BEGIN

INSERT INTO @EmployeeInfo SELECT eName,vCity FROM tblEmployeeCity WHERE vEmpID = @ID

IF @@ROWCOUNT = 0

BEGIN

INSERT INTO @EmployeeInfo VALUES ('','No Enployee Found')

END

RETURN

END

Above code will create a virtual table which is shows the employee information as Employee Name and Employee City.

Execute:

SELECT * FROM Employee('E001')

Restart Computer Failed Error in SQL Server 2008 - DotNet Brother

“Restart Computer Fail” error occurs when we try to install SQL Server 2008 Developer Edition. Trying to install SQL Server 2008 Developer edition setup failed at the Setup Support Rules page on the Rule "Restart Computer" see figure 1. The following error message appear on screen: "Restart computer failed" see figure 2.

Figure 1:

Restart Computer Failed Error in SQL Server 2008

Figure 2:

Error Reason: In general, a previous program installation created pending file operations on the installation machine.

Solution:

Step 1: Open the registry. For open registry, press window key + R and type

Step 2: Type regedit and press enter button

Step 3: Now select HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager

Step 4: Double click "PendingFileRenameOperations"

Step 5: Delete all data from Value data textbox.

Step 6: Press OK and close the registry.

Now try to run SQL Server 2008 setup again. I hope Restart computer failed error not come again.

Cursor in SQL

In this blog I am trying to explain the concept of Cursor in SQL Server.

Definition:

Cursor is an object of database which is used to retrieve data from result set row by row. In other words a cursor is a set of rows together with a pointer that identifies a current row. We use cursor when we need to update records in a table row by row.

Cursor Life Cycle:

1-Declare Cursor

A cursor is declared by DECLARE CURSOR SQL command. We can declare a cursor by following syntax:

DECLARE cur_name CURSOR

[LOCAL | GLOBAL] --define the scope of cursor

[FORWARD_ONLY | SCROLL] --define cursor movements

[STATIC | KEYSET | DYNAMIC | FAST_FORWARD] -- type of cursor

[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] --define locks

FOR select_statement -- SQL Select statement

FOR UPDATE [column1,column2,...columnn] -- columns that to be updated

2-Open Cursor

A cursor can be open in two way either locally or globally. Syntax for open cursor:

OPEN [GLOBAL] cur_name --by default it is local

3-Fetch Cursor

After opening a you can fetch cursor. Syntax for fetch cursor:

FROM [GLOBAL] cur_name

INTO @var1,@var2…..@varn

4-Close Cursor

Close statement close cursor explicitly. Syntax for close cursor:

CLOSE cur_name

5-Deallocate Cursor

To free the resources allocated by cursor we deallocate the cursor.Syntax for deallocate cursor

DEALLCATE cur_name

Example of Cursor

DECLARE @FirstName varchar(50)

DECLARE @LastName varchar(50)

DECLARE cur_name CURSOR

STATIC FOR SELECT FirstName,LastName FROM user1

OPEN cur_name

if @@CURSOR_ROWS>0

BEGIN

FETCH NEXT FROM cur_name INTO @FirstName,@LastName

WHILE @@FETCH_STATUS=0

BEGIN

PRINT 'First Name:'+@FirstName+',Last Name:'+@LastName

FETCH NEXT FROM cur_name INTO @FirstName,@LastName

END

CLOSE cur_name

DEALLOCATE cur_name