Thursday, November 24, 2011

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



Wednesday, November 23, 2011

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



Saturday, September 24, 2011

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;


            }


        }


Saturday, August 27, 2011

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

Saturday, August 6, 2011

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.



Tuesday, February 15, 2011

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>