Hello All, We are going to start new batch from next week. message/call or mail us for more details.

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:

<head runat="server">

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

    <script language="javascript" type="text/javascript">

        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>

    <form id="form1" runat="server">

    <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 create watermark Text for Textbox by using JavaScript

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.

How to Drop Primary Key Constraint

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


DELETE, TRUNCATE with RESEED Identity in SQL Server

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


DELETE, TRUNCATE with RESEED Identity in SQL Server

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)
AS
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:


Create a user define function using SQL Server 2008 R2


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')

Create a user define function using SQL Server 2008 R2

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
AS
RETURN
  SELECT dbo.WhichCountry(tblEmployeeCity.vCity) as tblEmployeeFullInformation,

         tblEmployeeCity.*
  FROM tblEmployeeCity
  WHERE dbo.WhichCountry(tblEmployeeCity.vCity) = @Country
GO


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:

Create a user define function using SQL Server 2008 R2

Multi-statement Table-valued.


CREATE FUNCTION Employee
( @ID varchar(50) )
RETURNS
@EmployeeInfo table (
Emp_name Varchar(50),
Emp_City Varchar(20)
)
AS
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
GO


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

Execute:

SELECT * FROM Employee('E001')

Create a user define function using SQL Server 2008 R2

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:


Restart Computer Failed Error in SQL Server 2008

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:

FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n]
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
END
CLOSE cur_name
DEALLOCATE cur_name