Parsing JSON w/ @ symbol in it

To read the json response like bellow @ concatenated with attribute 
                           '{ "@id": 1001, "@name": "Sony", "edu": "MCA" }'
Usually we do use alert(data.edu); to get the values but when @ is with attribute then use the bellow code

 var attName = data['@id'];
 alert(attName);

      or

 var att = '@name';
 var attID = data[att];

 alert(data.edu);



Tags: @ in json, @ symbol in json response, Parsing JSON with @ symbol in it.

Using of BULK INSERT in Sql Server

Using of BULK INSERT 

STEP 1: Create a text file the name STUDENTS_LIST.txt and add the bellow data

1,RAJIV,M.S
2,VINAY KUMAR,B.A
3,HANU,M.B.A
4,ARUN,M.Sc

STEP 2: Create a table using bellow statment

CREATE TABLE #STUDENT(ID INT, [NAME] NVARCHAR(50), EDUCATION NVARCHAR(20))

STEP 3: Now use the Bulk Insert Statement

BULK INSERT #STUDENT

FROM 'D:\STUDENTS_LIST.txt'

WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )

STEP 4: Check the table using bellow query

SELECT * FROM #STUDENT

DROP TABLE #STUDENT

Tags: BULK INSERT, Insert, Sql Server, SQL, Query

How to bind the selected value of a DropDownList

// Select * from Countries
// Select CustID,CountryID from Customers where CustID=1
DataSet ds = new DataSet();
da.Fill(ds, "Countries");
ddl.DataSource = ds.Tables[0];
ddl.DataTextField = "CountryName";
ddl.DataValueField = "CountryID";
ddl.SelectedValue = ds.Tables[1].Rows[0]["CountryID"].ToString();
ddl.DataBind();

O/p:     Angola Antigua and Barbuda Argentina Australia Austria India Indonesia Malaysia

Tags: asp.net, binding, selected value, data base

Sql Server: CTAS Create table as select

Creating table from another data copy only the snytax but not the data

SELECT * FROM Customers

SELECT * INTO ImpCustomers FROM Customers WHERE 1=2

SELECT * FROM ImpCustomers

O/p:

DROP TABLE ImpCustomers 

Tags: Sql Server: CTAS Create table as select,Create, Copy

Getting the Rows from DB in page wise using Row_Number in SQL Server

Create table #States(StateID INT,StateName NVARCHAR(20))
INSERT INTO #States
SELECT 1,'California' UNION
SELECT 2,'Albama' UNION
SELECT 3,'Alaska' UNION
SELECT 4,'Arizona' UNION
SELECT 5,'Florida' UNION
SELECT 6,'Hawaii' UNION
SELECT 7,'Montana' UNION
SELECT 8,'Indiana' UNION
SELECT 9,'Lowa' UNION
SELECT 10,'New York' UNION
SELECT 11,'New Mexico' UNION
SELECT 12,'Texas' UNION
SELECT 13,'Virginia' UNION
SELECT 14,'South Carolina' UNION
SELECT 15,'Washington'


SELECT * FROM #States
  

Create the Procedure like

Create Proc GetRecords(@pageNo int=1,@pageSize int)
as begin
Select StateID,StateName from
(Select Row_Number() over (Order By StateID) SNO,StateID,StateName from #States)S
Where S.SNO Between ((@pageNo-1)*@pageSize)+1 and @pageNo*@pageSize
end

Execution:-

exec GetRecords 1,4        --1 is page number & 4 is page size
exec GetRecords 2,4
exec GetRecords 3,4
exec GetRecords 4,4

o/p:

DROP TABLE #States

Tag: Using Row_Number in SQL Server finding records

HTML Color codes

HTML Color codes

#000	#300	#600	#900	#C00	#F00
#003	#303	#603	#903	#C03	#F03
#006	#306	#606	#906	#C06	#F06
#009	#309	#609	#909	#C09	#F09
#00C	#30C	#60C	#90C	#C0C	#F0C
#00F	#30F	#60F	#90F	#C0F	#F0F
#030	#330	#630	#930	#C30	#F30
#033	#333	#633	#933	#C33	#F33
#036	#336	#636	#936	#C36	#F36
#039	#339	#639	#939	#C39	#F39
#03C	#33C	#63C	#93C	#C3C	#F3C
#03F	#33F	#63F	#93F	#C3F	#F3F
#060	#360	#660	#960	#C60	#F60
#063	#363	#663	#963	#C63	#F63
#066	#366	#666	#966	#C66	#F66
#069	#369	#669	#969	#C69	#F69
#06C	#36C	#66C	#96C	#C6C	#F6C
#06F	#36F	#66F	#96F	#C6F	#F6F
#090	#390	#690	#990	#C90	#F90
#093	#393	#693	#993	#C93	#F93
#096	#396	#696	#996	#C96	#F96
#099	#399	#699	#999	#C99	#F99
#09C	#39C	#69C	#99C	#C9C	#F9C
#09F	#39F	#69F	#99F	#C9F	#F9F
#0C0	#3C0	#6C0	#9C0	#CC0	#FC0
#0C3	#3C3	#6C3	#9C3	#CC3	#FC3
#0C6	#3C6	#6C6	#9C6	#CC6	#FC6
#0C9	#3C9	#6C9	#9C9	#CC9	#FC9
#0CC	#3CC	#6CC	#9CC	#CCC	#FCC
#0CF	#3CF	#6CF	#9CF	#CCF	#FCF
#0F0	#3F0	#6F0	#9F0	#CF0	#FF0
#0F3	#3F3	#6F3	#9F3	#CF3	#FF3
#0F6	#3F6	#6F6	#9F6	#CF6	#FF6
#0F9	#3F9	#6F9	#9F9	#CF9	#FF9
#0FC	#3FC	#6FC	#9FC	#CFC	#FFC
#0FF	#3FF	#6FF	#9FF	#CFF	#FFF

Tags: HTML Color codes

Switching Rows as columns in SQL Server

CREATE A TABLE CALLED CAR 

CREATE TABLE CAR(ID INT,NAME NVARCHAR(20),COLOR NVARCHAR(20));

INSERT INTO CAR
    SELECT 1,'TATA SUMO','RED' UNION
    SELECT 2,'TATA SUMO','BLUE' UNION
    SELECT 3,'TATA SUMO','WHITE' UNION
    SELECT 4,'TATA SUMO','SILVER' UNION
    SELECT 6,'MARUTHI','BLUE' UNION
    SELECT 7,'MARUTHI','WHITE' UNION
    SELECT 5,'MARUTHI','RED' UNION
    SELECT 8,'MARUTHI','SILVER' UNION
    SELECT 9,'SAFARI','WHITE' UNION
    SELECT 10,'SAFARI','SILVER' UNION
    SELECT 11,'TATA SUMO','RED' UNION
    SELECT 12,'TATA SUMO','SILVER'

SELECT * FROM CAR

SELECT * FROM (SELECT * FROM CAR) p
    PIVOT (count(ID) FOR COLOR IN (RED,BLUE,WHITE,SILVER)) AS pvt
Order by NAME DESC

SELECT * FROM (SELECT * FROM CAR) p
    PIVOT (count(ID) FOR NAME IN ("TATA SUMO",SAFARI,MARUTHI)) AS pvt

O/P:

TAGS: Switching rows as columns in sqlserver, using pivot in sql server, sql pivot