Pivot in Sql

CREATE TABLE #tblitems(
  class    nVARCHAR(100),
  Subject  nVARCHAR(100),
  Total    INT
)

INSERT INTO #tblitems
SELECT 'Standard -12', 'Chemistry', 20 UNION ALL
SELECT 'Standard -11', 'Maths', 45 UNION ALL
SELECT 'Standard -12', 'Maths', 0 UNION ALL
SELECT 'Standard -11', 'Chemistry', 5 UNION ALL
SELECT 'Standard -12', 'Physics', 30 UNION ALL
SELECT 'Standard -11', 'Physics', 45 UNION ALL
SELECT 'Standard -11', 'Biology', 50 UNION ALL
SELECT 'Standard -12', 'Biology', 94

-- Creating a list of all distinct row values
-- that would be going to become columns
DECLARE @paramList VARCHAR(MAX)
SET @paramList = STUFF((
                    SELECT DISTINCT ',[' + Subject + ']'
                     FROM #tblitems FOR XML PATH('')
                    )
                 ,1,1,'')
PRINT @paramList
-- OUTPUT : [param1],[param2],[param3]

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT class, ' + @paramList
+ ' FROM( SELECT * FROM #tblitems )src
PIVOT(SUM(Total) FOR Subject IN (' + @paramList + ')) pvt order by class desc'

EXEC sp_executesql @query
DROP TABLE #tblitems

Find Label inside Templete column of GridView Javascript

Hi, An Easy way to find Label inside Gridview through Javascript.
Label control actually renfered like Span the Html  Control. you can see by Using Firebug

In GridView

   <asp:TemplateField HeaderText="Employee Name" HeaderStyle-HorizontalAlign="Left" HeaderStyle-Width="110px">
                   
                        <ItemTemplate>
                            <asp:Label ID="lblDisplayEmp" runat="server" Text='<%# Eval("DisplayEmpName") %>'></asp:Label>       
                            <asp:Label ID="hidEmpname" style="display:none;"     runat="server" Text='<%# Eval("EmpName") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>


  var grd = document.getElementById("<%= GridView1.ClientID %>");
  for (i = 1; i < grd.rows.length; i++) {
var empName = grd.rows[i].cells[17].getElementsByTagName("span");
                alert(empName[1].innerHTML);
}

Note :  empName[1].innerHTML- it will give you first label
             empName[0].innerHTML  it will give you Second label

get distinct values from string

An easy way to remove duplicate from string and get distinct values


 var input = '1,2,3,1,4,6,7,2,3';
                var splitted = input.split(',');
                var collector = {};
                for (i = 0; i < splitted.length; i++) {
                    key = splitted[i].replace(/^\s*/, "").replace(/\s*$/, "");
                    collector[key] = true;
                }
                var out = [];
                for (var key in collector) {
                    out.push(key);
                }
                var output = out.join(',');

OutPut will be 1,2,3,4,6,7

Set Fix Header to Ajax AutoCompleteExtender

Ajax Auto complete onclient showing ,Yuo can set a fix header


AutoComplete Extender Properties to be Set
OnClientShowing="AutoCompleteHeader" BehaviorID="Behav1"



Javascript:-

function AutoCompleteHeader()
        {    
     
        var extender = $find("Behav1");  
        var message = "Name|Class|RollNo";
    jQuery(extender.get_completionList()).prepend("<li rel='header' style='background-color:#0263B1 !important;font-weight: bold;color:white;cursor : Not-allow !important;'>" + message + "</li>");
 
        }

Note EmployeeAutoCompleteEx7- is BehaviourId of AutoComplete

Get and Set WaterMark Text of AJAX

Hii  ,I will tell You how can we get Watermark text of Ajax Tool through Javascript
This Can be done by setting Behaviour Id of Watermark Extneder


<asp:TextBox runat="server" ID="txtproId" CssClass="invisible"  />
                                                <cc1:TextBoxWatermarkExtender ID="wtmProjectName" runat="server" BehaviorID="projCatbehav" TargetControlID="txtProjectCategory"
                                                    WatermarkCssClass="watermarked" WatermarkText="Goldy" />

function validate() {

}

To get the Text 

var procat11= $find("projCatbehav").get_Text();

Note:-projCatbehav is an Behaviour Id

To Set the Text

$find("projCatbehav").set_Text("Please Type Here");

Find Particular Column in a Database

Hi guys Now you can find the table name if you know the column name in a Database

Here we go

SELECT t.name AS table_name,

SCHEMA_NAME(schema_id) AS schema_name,

c.name AS column_name

FROM sys.tables AS t

INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

WHERE c.name LIKE '%Fee%'

ORDER BY schema_name, table_name;

Note:-Fee is my column name

TOO Many Arguments Specified Resolved

Heloo to all, Now guys i am going to tell you that how you can check your Sql datasource parameters.
when you pass parameters from .aspx page,mostly time you get an error

TOO MANY ARGUMENTS SPECIFIED

To check it you have to call updating method

protected void SqlDataSource2_Updating(object sender, SqlDataSourceCommandEventArgs e)
    {
     
        for (int i = 0; i < e.Command.Parameters.Count; i++)
        {
            Trace.Write(e.Command.Parameters[i].ParameterName);
            if (e.Command.Parameters[i].Value != null)
            {
                Trace.Write(e.Command.Parameters[i].Value.ToString());
            }
        }
    }


with this you can check each value by parameter wise

Retrieve only Time in sql

Hello to all Now i will tell you that how to fetch only time from GetDate() function

SELECT LTRIM(RIGHT(CONVERT(VARCHAR(20),  GetDate() , 100), 7))

Make checkbox checked,Unchecked according to status 0&1 at .aspx page

Hello to all Now I am going to tell you that How can You bind checkbox in datalist and make it Checked and unchecked in .aspx page itself. No Need to do code at .cs page


 <asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource2">
                    <ItemTemplate>
                      
                                <asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Eval("status").ToString() == "1" ? true : false %>' />
                           
                    </ItemTemplate>
                </asp:DataList>

Sql Date ISDATE

ISDATE  function is used to check whether the date is valid or not.

Suppose you have a column nvarchar in which you are storing dates

select ISDATE(yourdate) from  YourTable

If Valid then it return 1  and if not then 0

Return two parameter from stored procedure

Suppose in stored procedure we have like this

set @lastinserted=(select ident_current('user_referencerecord'))
 set @exist=0
Now to return two variables
select @exist as exist,@lastinserted as lastinserted

To recieve at .cs page

using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                while (rdr.Read())
                {
                    returnval1=rdr.GetInt32(rdr.GetOrdinal("exist"));
                    int returnval2 = rdr.GetInt32(rdr.GetOrdinal("lastinserted"));
                }


            }

Count multilevel data

Helo to all I have a table in sql server 2005
Suppose Goldy join kshama   kshama join Harsh  Harsh join  Gaurav and Gaurav join Moni
Now i want to calculate total joinning of Goldy
according to me it should come 4 of Goldy   3 of Kshama  2 of Harsh and 1 Of Gaurav
Now what will be the query
My table is like

Now To achieve this goal I will use With Clause

with emct(id,parentid,name1,usercode,lvel) as

(

select id,parentid,name1,usercode,0  from multilevel 

where id =1(change your id from here)

union all

select main.id,main.parentid,main.name1,main.usercode,lvel +1

from multilevel as main join emct as temp on main.parentid=temp.id

)

select id,parentid,name1,usercode,lvel from emct;

go

Create stored procedure and get value return from it

Now i am going to tell You that how to make return from stored Procedure

Suppose this is my Stored Procedure

create Proc AddNewscrip


@unique as nvarchar(50),
as

if(@unique ='insert')
begin
if exists(select scripname from scripmaster where scripname=@scripname)
return 1
else
return 0
end

Now at .cs page You can retrive it like

SqlParameter returnValue = new SqlParameter("returnVal", SqlDbType.Int);
            returnValue.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(returnValue);
Now Open the Connection
conn.open();

cmd.ExecuteNonQuery();
            conn.Close();
            return Convert.ToInt32(returnValue.Value);

In this way you can get return value 0 or may be 1 depending on Your condition

count selected items of checkboxlist and also separate it with comma

A simple way to count only selected items of checkboxlist

 var totalcount = CheckBoxList1.Items.Cast<ListItem>().Where(item => item.Selected).Count();


now If you want separte it with comma then



 foreach (ListItem selectedItem in CheckBoxList1.Items)
        {
            if (selectedItem.Selected)
            {


                if (string.IsNullOrEmpty(selectedExch))


                    selectedExch = selectedItem.Value;


                else


                    selectedExch = selectedExch + "," + selectedItem.Value;


            }


        }

insert data from one table to another in sql

Helo guys.. A simple way to insert data from one table to another



Insert into Table1(distcentre, distcode,disttname) select distributorcenter, UserCode,Name
from Table2

Trigger Tutorial

                                                                 TRigger :--

Trigger is like stored Procedure.It is used after Insert update delete or Before it.The difference between both is that trigger has relation with tables while stored procedure not.

Suppose we have two tables ‘test’ and ‘test22’. Now I wants that when a new record is inserting  in the table test then after insert a trigger automatically  and pick the last inserted record  from test and insert the same into test22 table.. Here we go

Step I --  Create Trigger

Create trigger   myfirst <-----(Trigger Name)

on test  <-----------(Table name on which we are applying)

after insert

as

begin

declare @idss as int, @nameb as nvarchar(50),@cls as nvarchar(50)

select @idss=id,@nameb=name1,@cls=class from test where id= (select isnull(max(id),0) from test)

insert into test22 values(@idss,@nameb,@cls)

end

Step II --  Now when you insert a record in test then automatically the same record will insert in ‘test22’

Any problem plz mail me at  goldyabk1@gmail.com.. 

I am trying to do best to update my blog.

how to convert bytes in kb,mb and gb in fileupload

take a button,fileupload and a label in designingtime

protected void Button1_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile)
        {
            string format = System.DateTime.Now.ToLongDateString() +","+ System.DateTime.Now.ToLongTimeString();

            int size = FileUpload1.FileBytes.Length;
            Label1.Text = FormatBytes(size);

       
        }

      
    }

public string FormatBytes(int Bytes)
    {
        string filesize;
        if (Bytes >= 1073741824)
        {
            decimal size = decimal.Divide(Bytes, 1073741824);
            filesize = string.Format("{0:##.##} GB", size);
        }
        else if (Bytes >= 1048576)
        {
            decimal size = decimal.Divide(Bytes, 1048576);
            filesize = string.Format("{0:##.##} MB", size);
        }
        else if (Bytes >= 1024)
        {
            decimal size = decimal.Divide(Bytes, 1024);
            filesize = string.Format("{0:##.##} KB", size);
        }
        else if (Bytes > 0 & Bytes < 1024)
        {
            decimal size = Bytes;
            filesize = string.Format("{0:##.##} Bytes", size);
        }
        else
        {
            filesize = "0 Bytes";
        }
        return filesize;
    }

You have to add in web.configuration also--

<system.web>
        <httpRuntime  maxRequestLength="2097151" executionTimeout="360"/>

</system.web>

payment through paypal in Asp.Net

A simple Way to payment through Paypal..

For this it is compulsory to login in your pay pal account.

Now come to the coding part ..  Take one text-box and a button

 protected void Button1_Click(object sender, EventArgs e)

    {

       protected decimal dec;(take this globally)

        try

        {

            dec = Convert.ToDecimal(TextBox1.Text);

                       

        }

        catch(Exception ex)

        {

            Label1.Visible = true;

        }

        paypalquery();

    }

 protected void paypalquery()

    {

//Make a class named it as "paypal"

        paypal pay = new paypal();

        pay.AccountEmail = paypal.Email;

        pay.PayPalBaseUrl = paypal.PayPalUrl;

        pay.Amount = dec;

        pay.ItemName = "New Item Added";

        pay.SuccessUrl = Request.Url + "?paypal=success";

        pay.CancelUrl = Request.Url + "?paypal=cancel";

        Response.Redirect(pay.submiturl());

        return;

    }

this is cs class

public class paypal

{

    public string LogoUrl = "";

    public string AccountEmail = "";

    public string BuyerEmail = "";

    public string SuccessUrl = "";

    public string CancelUrl = "";

    public string ItemName = "";

    public decimal Amount = 0.00M;

    public string InvoiceNo = "";

    public static string Email = "PayPalEmailAccount@YourCompany.com";

    public static string PayPalUrl = "https://www.sandbox.paypal.com/us/cgi-bin/webscr?";

    public string PayPalBaseUrl = "https://www.paypal.com/cgi-bin/webscr?";

public paypal()

{

//

// TODO: Add constructor logic here

//

}

    public string submiturl()

    {

        StringBuilder buil = new StringBuilder();

        buil.Append(PayPalBaseUrl + "cmd=_xclick&business=" + HttpUtility.UrlEncode(AccountEmail));

       if (Amount != null)

           buil.AppendFormat("&amount={0:f2}", Amount);

       if (ItemName != null && ItemName != "")

           buil.AppendFormat("&item_name={0}", HttpUtility.UrlEncode(ItemName));

       if (SuccessUrl != null && SuccessUrl != "")

           buil.AppendFormat("&return={0}", HttpUtility.UrlEncode(SuccessUrl));

       if (CancelUrl != null && CancelUrl != "")

           buil.AppendFormat("&cancel_return={0}", HttpUtility.UrlEncode(CancelUrl));

        return buil.ToString();

    }

    public class configuration

    {

        public static string email = "PayPalEmailAccount@YourCompany.com";

        public static string PayPalUrl = "https://www.sandbox.paypal.com/us/cgi-bin/webscr?";

    }

}

Difference b/w String and String Builder

Usually, String is immutable which means once you have the content there you cannot change it. While StringBuilder is mutable because it allows you to change the content you have already placed.
Notice that a string allows you to change the content but offcourse it creates a new string reference. For example, all string methods return a new string and don't update the same string variable.
When you have huge amount of concatenation, best recommendation to go for StringBuilder!
Example:
String and StringBuilder class stores strings. But when you cannot change a String object after creating one.
eg: String name = "Prasad";
By saying you cannot change the name object means you cannot change the value in name object internally. When you change the name object value to something else, a new String object is creating in memory and assign the new value.

eg: name = "Prasad Reddy";

A new name object is creating in memory and the value "Prasad Reddy" is assinging to the newly created space.

But StringBuilder class occupies the same space even if you change the value.

If you are doing string concatenation StringBuilder class is far better in performance than String class.

You can use StringBuilder's Append() method to use concatenation.


for example

String strSen = "hello";
strSen+= "Hello";


StringBuilder sbb = new StringBuilder();
sbb = sbb.Append("goldy");
sbb = sbb.Append("gupta");



Both are doing same thing but the process is faster in stringbuilder..

display number into words with use of sql function

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO



Create FUNCTION [dbo].[udf_Num_ToWords] (

@Number Numeric (38, 0)
) RETURNS VARCHAR(8000)

AS BEGIN

DECLARE @inputNumber VARCHAR(38)
DECLARE @NumbersTable TABLE (number CHAR(2), word VARCHAR(10))
DECLARE @outputString VARCHAR(8000)
DECLARE @length INT
DECLARE @counter INT
DECLARE @loops INT
DECLARE @position INT
DECLARE @chunk CHAR(3) -- for chunks of 3 numbers
DECLARE @tensones CHAR(2)
DECLARE @hundreds CHAR(1)
DECLARE @tens CHAR(1)
DECLARE @ones CHAR(1)

IF @Number = 0 Return 'Zero'

SELECT @inputNumber = CONVERT(varchar(38), @Number)
     , @outputString = ''
     , @counter = 1
SELECT @length   = LEN(@inputNumber)
     , @position = LEN(@inputNumber) - 2
     , @loops    = LEN(@inputNumber)/3

IF LEN(@inputNumber) % 3 <> 0 SET @loops = @loops + 1

INSERT INTO @NumbersTable   SELECT '00', ''
    UNION ALL SELECT '01', 'one'      UNION ALL SELECT '02', 'two'
    UNION ALL SELECT '03', 'three'    UNION ALL SELECT '04', 'four'
    UNION ALL SELECT '05', 'five'     UNION ALL SELECT '06', 'six'
    UNION ALL SELECT '07', 'seven'    UNION ALL SELECT '08', 'eight'
    UNION ALL SELECT '09', 'nine'     UNION ALL SELECT '10', 'ten'
    UNION ALL SELECT '11', 'eleven'   UNION ALL SELECT '12', 'twelve'
    UNION ALL SELECT '13', 'thirteen' UNION ALL SELECT '14', 'fourteen'
    UNION ALL SELECT '15', 'fifteen'  UNION ALL SELECT '16', 'sixteen'
    UNION ALL SELECT '17', 'seventeen' UNION ALL SELECT '18', 'eighteen'
    UNION ALL SELECT '19', 'nineteen' UNION ALL SELECT '20', 'twenty'
    UNION ALL SELECT '30', 'thirty'   UNION ALL SELECT '40', 'forty'
    UNION ALL SELECT '50', 'fifty'    UNION ALL SELECT '60', 'sixty'
    UNION ALL SELECT '70', 'seventy'  UNION ALL SELECT '80', 'eighty'
    UNION ALL SELECT '90', 'ninety'  

WHILE @counter <= @loops BEGIN

SET @chunk = RIGHT('000' + SUBSTRING(@inputNumber, @position, 3), 3)

IF @chunk <> '000' BEGIN
SELECT @tensones = SUBSTRING(@chunk, 2, 2)
    , @hundreds = SUBSTRING(@chunk, 1, 1)
    , @tens = SUBSTRING(@chunk, 2, 1)
    , @ones = SUBSTRING(@chunk, 3, 1)

IF CONVERT(INT, @tensones) <= 20 OR @Ones='0' BEGIN
SET @outputString = (SELECT word
                                      FROM @NumbersTable
                                      WHERE @tensones = number)
                   + CASE @counter WHEN 1 THEN '' -- No name
                       WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
                       WHEN 4 THEN ' billion '  WHEN 5 THEN ' trillion '
                       WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
                       WHEN 8 THEN ' sextillion '  WHEN 9 THEN ' septillion '
                       WHEN 10 THEN ' octillion '  WHEN 11 THEN ' nonillion '
                       WHEN 12 THEN ' decillion '  WHEN 13 THEN ' undecillion '
                       ELSE '' END
                               + @outputString
   END
ELSE BEGIN
             SET @outputString = ' '
                            + (SELECT word
                                    FROM @NumbersTable
                                    WHERE @tens + '0' = number)
        + '-'
                             + (SELECT word
                                    FROM @NumbersTable
                                    WHERE '0'+ @ones = number)
                   + CASE @counter WHEN 1 THEN '' -- No name
                       WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
                       WHEN 4 THEN ' billion '  WHEN 5 THEN ' trillion '
                       WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
                       WHEN 8 THEN ' sextillion '  WHEN 9 THEN ' septillion '
                       WHEN 10 THEN ' octillion '  WHEN 11 THEN ' nonillion '
                       WHEN 12 THEN ' decillion '   WHEN 13 THEN ' undecillion '
                       ELSE '' END
                            + @outputString
END

IF @hundreds <> '0' BEGIN
SET @outputString  = (SELECT word
                                      FROM @NumbersTable
                                      WHERE '0' + @hundreds = number)
           + ' hundred '
                                + @outputString
END
END

SELECT @counter = @counter + 1
    , @position = @position - 3

END

SET @outputString = LTRIM(RTRIM(REPLACE(@outputString, '  ', ' ')))
SET @outputstring = UPPER(LEFT(@outputstring, 1)) + SUBSTRING(@outputstring, 2, 8000)


RETURN @outputString END



Insert query

insert into table_3 values(1,100,dbo.udf_Num_ToWords(100) )


how to call

select dbo.udf_Num_ToWords(@Number)