Change color statuswise in GridView using JQuery

Sometimes we need to change color into Gridview statuswise. we can use RowDataBound event to do this but this can be easily done using JQuery.

Here is the code to do so...

<script language="javascript" type="text/javascript">
$(document).ready(function () {
            $('.Users).find("td:nth-child(5)").each(function () {
                if ($(this).children("span").html() == Active) {
                    $(this).css("color", "Green");
                    $(this).css("border-color", "Black");
                }
                else if ($(this).children("span").html() == InActive) {
                    $(this).css("color", "Red");
                    $(this).css("border-color", "Black");
                }
            });
        });
    </script>

Here in $('.Users') is a CssClass name of Gridview.

Give the CssClass attribute to your Gridview control like this. And use TemplateField and use Label element for that column.

<asp:GridView ID="gvUserList" runat="server" CssClass="Users"></asp:GridView>

If you are using BoundField in a Gridview control then you can try the following.

<script language="javascript" type="text/javascript">
$(document).ready(function () {
            $('.Users).find("td:nth-child(5)").each(function () {
                if ($(this).text() == Active) {
                    $(this).css("color", "Green");
                    $(this).css("border-color", "Black");
                }
                else if ($(this).text() == InActive) {
                    $(this).css("color", "Red");
                    $(this).css("border-color", "Black");
                }
            });
        });
    </script>

Note : Here nth-child(5) is column number and it starts from 1 not 0.

Find Dynamic Generated Controls from Code Behind C#

We can find the dynamic controls with this code.
Here instead of txtFirstName put your controls ID which you have created dynamically.

string FirstName;
foreach (string key in Request.Params.AllKeys)
       {
                   if (key.Contains("txtFirstName"))
                    {
                        FirstName= Request.Params[key].ToString();
                    }
      }

If any doubt put comment.

Fill Year DropdownList upto Current Year

Bind year in Dropdown List is very easy but if you bind it statically it will create a problem for the next year so it should be dynamic upto the current year.

Here I am giving the function to bind the dropdownlist upto current year.

public void FillYearDropDown()
    {
        int Year;
        int StartYear = 2010;
        int EndYear = DateTime.Now.Year;
        drpYear.Items.Clear();
        for (Year = StartYear; Year <= EndYear; Year++)
        {
            drpYear.Items.Add(Year.ToString());
        }
        drpYear.Items.Insert(0, "--Select Year--");
    }

Here you can change the StartYear variable as per your requirement.

Generate Random Password combination of Alphabates, Numbers and Special Charactors

Here I am giving a common function to generate a random string combination of Alphabates, Numbers and Special Charactors.

public string GenerateString(int size)
        {
            Random rand = new Random();
            string Alpha = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
            string Number = "0123456789";
            string SpecialChar = "!@#$&";
            char[] chars = new char[size];
            int count = 0;
            int j = Number[rand.Next(10)];
            for (int i = j; i < (size*100); i++)
            {
                if (i % 2 == 0)
                {
                    chars[count] = Alpha[rand.Next(26)];
                    count++;
                    if (count == size)
                        break;                   
                }
                if (i % 3 == 0)
                {
                    chars[count] = Number[rand.Next(10)];
                    count++;
                    if (count == size)
                        break;                   
                }
                if (i % 5 == 0)
                {
                    chars[count] = SpecialChar[rand.Next(5)];
                    count++;
                    if (count == size)
                        break;                   
                }                               
            }
            string FinalCode = new string(chars);
            return FinalCode;
       }

Call this function where ever you want with the size of the string that you want to generate. like this

GenerateString(8);

Fill Dropdown list with Enum with key and value

I will explain how to fill Dropdown list with Enum.

I created a class called CommonFunctions in it I created an Enum called Sports.

public class CommonFunctions
{
public enum Sports
    {
        Cricket = 0,
        Football = 1,
        Hockey = 2
    }
}

I am using this Enum to bind the Dropdown on my webpage using the following method.

public void FillDrpSport()
    {       
        foreach (CommonFunctions.Sports r in Enum.GetValues(typeof(CommonFunctions.Sports)))
        {
            ListItem item = new ListItem(Enum.GetName(typeof(CommonFunctions.Sports), r), 
                                    r.GetHashCode().ToString());
            drpSports.Items.Add(item);
        }
    }

Now call this method to your pageload event.

SQL Query for From Date and To Date in Database and Parameter

In this post I am giving example of sql query.

sometimes we need From Date and To Date filter in our application and there is already From Date and To Date fields exist in our Database so how to query it I will show in belowe stored procedure.

Here check the where condition in the code.

CREATE PROCEDURE [dbo].[SPDailyDeviceLog]

@FDate date = NULL,
 @TDate date = NULL
AS
BEGIN

 SET NOCOUNT ON;

 select * from DailyLogs as l
 join Devices as d on d.DeviceID=l.DeviceID
 join Users as u on u.UserID = l.UserID
where ((@FDate BETWEEN convert(Date,l.CheckedInTime) AND convert(Date,l.CheckedOutTime)) OR (@FDate < convert(Date,l.CheckedInTime)) OR (@FDate IS NULL) or (l.CheckedInTime is NULL))
  and ((@TDate BETWEEN convert(Date,l.CheckedInTime) AND convert(Date,l.CheckedOutTime)) OR (@TDate > convert(Date,l.CheckedOutTime)) OR (@TDate IS NULL) or (l.CheckedOutTime is NULL))
   order by l.DailyLogID desc
END

SQL Scripts

To Create Table

CREATE TABLE Users
(
 UserID int primary key identity(1,1),
 UserName varchar(100) NOT NULL,
 Password varchar(100) NOT NULL,
 CreatedDate datetime default getdate(),
 IsActive bit default 1
)

To Add a Column to a Table

ALTER TABLE Users Add Role int Default 2

To Modify a Column to a Table

ALTER TABLE Users ALTER COLUMN Role varchar(50) NULL

To Concatenate and set into variable with airthmatic operation

DECLARE @id varchar(50)
set @id = 'result'+cast(((select COUNT(id) from cash)+1) as varchar(50));
print @id;

Output
result34

Syntex of Temp Table and Cursor in Stored Procedure.

CREATE PROCEDURE SPGetApprovedJobs
 @Customer varchar(100)
AS
BEGIN
 DECLARE @UserName varchar(100)
 CREATE TABLE #TempTable (Job varchar(100))
 DECLARE @MyCursor CURSOR
 SET @MyCursor = CURSOR FAST_FORWARD
 FOR
 select cm.Username from Contact_management as cm join Contact as c on  c.Contact_id = cm.Contact_id
 join Customer_management as cust on cust.Customer_id = c.Customer_id
 where cust.Description = @Customer
 OPEN @MyCursor
 FETCH NEXT FROM @MyCursor
 INTO @UserName
 WHILE @@FETCH_STATUS = 0
 BEGIN
 INSERT INTO #TempTable select Job_sub as Job from Project_management
 where Project_id like '%'+@UserName+'/%'
 FETCH NEXT FROM @MyCursor
 INTO @UserName
 END
 CLOSE @MyCursor
 DEALLOCATE @MyCursor
 select * from #TempTable
 DROP TABLE #TempTable
END

Stored Procedure for the parameter of coma seprated values

-- EXEC SPUploadMultipleJobs 'clco1/2,clco1/6','test.pdf'
-- =============================================
CREATE PROCEDURE [dbo].[SPUploadMultipleJobs]
 @JobIds varchar(max) = '',
 @Attachment varchar(500) = ''
AS
BEGIN
 if(@JobIds <> '' AND @Attachment <> '')
 BEGIN
  DECLARE
   @ProjectId varchar(500),
   @position INT,
   @RevisionCount varchar(500);
  SET @position=0;
  if (right(@JobIds,1)<>',') SET @JobIds=@JobIds+',';
  WHILE charindex(',',@JobIds)>0
  BEGIN
      SET @ProjectId = cast(substring(@JobIds,0, charindex(',',@JobIds)) as varchar(500))       
      --print @ProjectId;
      set @RevisionCount = 'Revision/'+cast(((select COUNT(Revision_num) from Revision_table where Project_id = @ProjectId)+1) as       varchar(500));
      --print 'JobId : '+@JobIds+' Attachment : '+@Attachment+' ProjectId : '+@ProjectId +' RevisionCount : '+ @RevisionCount;
      update Project_management set Status = 'For Approval' where Project_id = @ProjectId;
      insert into Revision_table (Project_id, Attachments, Revision_num) Values (@ProjectId,@Attachment,@RevisionCount);
      SET @JobIds = substring(@JobIds, charindex(',',@JobIds)+1, LEN(@JobIds) - @position);
  END
 END
END

Common Table Expression in Stored Procedure

CREATE PROCEDURE SPUpdateVoucherStatus
 @VoucherId nvarchar(50) = ''
AS
BEGIN
 SET NOCOUNT ON;
 DECLARE @TotalAmount int,@PaidAmount int;
 WITH Account(Total,Paid) as
 (
    select vd.Amount as TotalAmount,SUM(CONVERT(int,pd.TotalAmount)) as AmountPaid
 from Voucher_details as vd
 left join Payment_details as pd on pd.VoucherId = vd.Voucher_id
 group by pd.VoucherId,vd.Vendor_id,vd.Customer_id,vd.Amount,vd.Date,vd.Status
 having vd.Status = 'Unbilled' and pd.VoucherId=@VoucherId
 )
 select @PaidAmount = Paid,@TotalAmount = Total from Account;
 if(@PaidAmount >= @TotalAmount)
 begin
  update Voucher_details set Status = 'Billed' where Voucher_id = @VoucherId
 end
END

Example of Transactions in SQL in Stored Procedure

CREATE PROCEDURE SPPartPaymentEntry
 @VoucherId nvarchar(50) = '',
 @PaymentDate nvarchar(50) = '',
 @TotalAmount nvarchar(50) = '',
 @PaymentMode nvarchar(50) = '',
 @CashAmount nvarchar(50) = '',
 @ChequeAmount nvarchar(50) = '',
 @ChequeNo nvarchar(50) = '',
 @BankName nvarchar(50) = '',
 @ChequeDate nvarchar(50) = '',
 @BankBranch nvarchar(50) = ''
AS
BEGIN
 SET NOCOUNT ON;
 BEGIN TRANSACTION PaymentEntry

 insert into Payment_details(VoucherId,Payment_date,TotalAmount,Payment_mode,Amount_cash,Amount_cheque,Cheque_num,Bank_name,Cheque_date,Bank_branch)
 values(@VoucherId,@PaymentDate,@TotalAmount,@PaymentMode,@CashAmount,@ChequeAmount,@ChequeNo,@BankName,@ChequeDate,@BankBranch)

 declare @Peti_id nvarchar(50);
 set @Peti_id = 'pid'+cast(((select COUNT(Peti_id) from Peti_cash_id)+1) as nvarchar(50));

 insert into Peti_cash_deposit (Peti_id, Description, Date, Deposit, Withdraw)
 Values (@Peti_id, 'Paid for Voucher No. ' + @VoucherId, @PaymentDate, '0', @CashAmount);
 update Peti_cash_balance Set Balance = (CAST(((select Balance from Peti_cash_balance)- CAST(@CashAmount as int)) as nvarchar(50)));

 insert into Peti_cash_id (Peti_id) Values (@Peti_id)

 IF @@ERROR = 0
  COMMIT TRANSACTION PaymentEntry
 ELSE
  ROLLBACK TRANSACTION PaymentEntry
END

Select First and Last Date of Month using Date

DECLARE @mydate DATETIME
SELECT @mydate = '04/10/2013'
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value /*First Date*/
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,'04/10/2013'))), DATEADD(mm,1,'04/10/2013')),101) /*Last Date*/

Select First and Last Date of Month using Month and Year

DECLARE @Month int;
DECLARE @Year int;
set @Month = 05
set @Year = 2013
select DATEADD(month,@Month-1,DATEADD(year,@Year-1900,0)) /*First Date*/
select DATEADD(day,-1,DATEADD(month,@Month,DATEADD(year,@Year-1900,0))) /*Last Date*/

Insert into Table from another Table

insert into Leave_detail(Company,Emp_name,Emp_id,AL,LeaveYear)
select e.Company_id,em.F_name,em.Employee_id,'11','2013' from Employee_management as em
join Employee as e on em.Employee_id=e.Employee_id

Update Table values from another Table

update Salary set Salary.Company_id = e.Company_id from Employee e where e.Employee_id = Salary.Employee_id

Get the Last inserted id in table

SqlCommand cmd = new SqlCommand("insert into Role (Role) OUTPUT INSERTED.RoleID Values ('Admin')",Conn);
conn.Open();
Int32 RoleID = (Int32)cmd.ExecuteScalar();
conn.Close();