Search This Blog

Friday 30 October 2015

Generate date wise user complaint reference number in sql or Generate date wise order number in sql server

Step 1:- Create table

CREATE TABLE tblUserComplaint
(
       RowId int identity,
       ReferenceId varchar(50),
       UserName varchar(50),
       InsDtTime datetime constraint default_InsDtTime default(getdate())
)

Step 2:- Create funtion

CREATE FUNCTION fnGenerateUserComplaintRefId()
RETURNS VARCHAR(100)
AS
BEGIN
       DECLARE @Alias VARCHAR(10) = 'REF'
       DECLARE @CurrentDate VARCHAR(10)
       DECLARE @SrNo VARCHAR(10)
       DECLARE @ReferenceId VARCHAR(100)


       SET @CurrentDate = (SELECT FORMAT( GETDATE(), 'yyyyMMdd', 'en-US' ))

       SET @SrNo = (
                        SELECT MAX(RIGHT(ReferenceId,4))
                        FROM tblUserComplaint
                        WHERE CONVERT(VARCHAR(10), InsDtTime, 112)=@CurrentDate
                   )

       IF @CurrentDate NOT IN (SELECT DISTINCT CONVERT(VARCHAR(10), InsDtTime, 112) FROM tblUserComplaint)
       BEGIN
              SET @ReferenceId = (SELECT CONCAT(@Alias,@CurrentDate,RIGHT(10000+1,4)))
       END
       ELSE
       BEGIN
              SET @SrNo = @SrNo+1

        IF @SrNo<>''
        BEGIN
                SET @ReferenceId = (SELECT CONCAT(@Alias,@CurrentDate,RIGHT(10000+@SrNo,4)))
        END

        IF @SrNo = ''
        BEGIN
                SET @ReferenceId = (SELECT CONCAT(@Alias,@CurrentDate,RIGHT(10000+1,4)))
        END
       END

       RETURN @ReferenceId
END

Step 3:- Create insert procedure

CREATE PROC spUserComplaintIns
@UserName varchar(50)
AS
BEGIN
       INSERT INTO tblUserComplaint
       (
              ReferenceId,
              UserName
       )
       VALUES
       (
              (SELECT dbo.fnGenerateUserComplaintRefId()),
              @UserName
       )
END

Step 4:- Insert record in tblUserComplaint table

EXEC spUserComplaintIns 'Aadarsh Shrivastav'
SELECT * FROM tblUserComplaint





EXEC spUserComplaintIns 'Aadarshini Goel'
SELECT * FROM tblUserComplaint





EXEC spUserComplaintIns 'Bajrang Goswami'
SELECT * FROM tblUserComplaint





EXEC spUserComplaintIns 'Bulbul Desai'
SELECT * FROM tblUserComplaint





EXEC spUserComplaintIns 'Chetan Bhagat'
SELECT * FROM tblUserComplaint





EXEC spUserComplaintIns 'Chaya Deol'
SELECT * FROM tblUserComplaint





Note:-

You can insert record in table using query.
So, your query look like below

INSERT INTO tblUserComplaint
       (ReferenceId,UserName)
VALUES
       ((SELECT dbo.fnGenerateUserComplaintRefId()),'Aadarsh Shrivastav')

Demo is created using Microsoft Sql Server 2014


Wednesday 28 October 2015

RowDataBound Event in GridView in ASP.Net


Step 1:- GridviewRowDataBoundEvent.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="GridviewRowDataBoundEvent.aspx.cs" Inherits="Demo.GridviewRowDataBoundEvent" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>GridView RowDataBound Event</title>
    <script>
        function Message(param1, param2) {
            alert(param1 + " salary is less i.e. " + param2);
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <strong>Employee Details</strong>
            <br />
            <br />
            <asp:GridView ID="GridView1" DataKeyNames="ID" runat="server" OnRowDataBound="GridView1_RowDataBound">
            </asp:GridView>
        </div>
    </form>
</body>
</html>

Step 2:- GridviewRowDataBoundEvent.aspx.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Demo
{
    public partial class GridviewRowDataBoundEvent : System.Web.UI.Page
    {
        DataTable dt;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                BindData();
            }
        }

        protected void BindData()
        {
            dt = new DataTable();

            dt.Columns.Add("ID"typeof(int));
            dt.Columns.Add("FirstName"typeof(string));
            dt.Columns.Add("MiddleName"typeof(string));
            dt.Columns.Add("LastName"typeof(string));
            dt.Columns.Add("Salary"typeof(float));

            DataRow dr;

            dr = dt.NewRow();
            dr[0] = 1;
            dr[1] = "Ravi";
            dr[2] = "Ramprakash";
            dr[3] = "Goel";
            dr[4] = "10000";
            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr[0] = 2;
            dr[1] = "Gopal";
            dr[2] = "Krishnan";
            dr[3] = "Iyer";
            dr[4] = "20000";
            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr[0] = 3;
            dr[1] = "Ramesh";
            dr[2] = "Dwarika";
            dr[3] = "Prasad";
            dr[4] = "30000";
            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr[0] = 4;
            dr[1] = "Albert";
            dr[2] = "Janny";
            dr[3] = "D'Costa";
            dr[4] = "40000";
            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr[0] = 5;
            dr[1] = "Sidharth";
            dr[2] = "Sunil";
            dr[3] = "Patel";
            dr[4] = "50000";
            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr[0] = 6;
            dr[1] = "Sunil";
            dr[2] = "Devendram";
            dr[3] = "Varma";
            dr[4] = "15000";
            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr[0] = 7;
            dr[1] = "Prakash";
            dr[2] = "Mohan";
            dr[3] = "Jha";
            dr[4] = "25000";
            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr[0] = 8;
            dr[1] = "Ram";
            dr[2] = "Gopal";
            dr[3] = "Varma";
            dr[4] = "55000";
            dt.Rows.Add(dr);

            GridView1.DataSource = dt;
            GridView1.DataBind();
        }

        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            // Check for that the row is a data row & not header or footer 
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                if (Convert.ToInt32(e.Row.Cells[4].Text) < 30000)
                {
                    e.Row.BackColor = Color.Yellow;
                }
                else
                {
                    e.Row.BackColor = Color.Gold;
                }
            }
        }
    }
}
________________________________________________________________________________
________________________________________________________________________________

Demo 1:- 

1.  Highlight entire row
2.  If Salary is less than 30000 than set the row Background Colour to Yellow and If greater than Gold

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            // Check for that the row is a data row & not header or footer 
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                if (Convert.ToInt32(e.Row.Cells[4].Text) < 30000)
                {
                    e.Row.BackColor = Color.Yellow;
                }
                else
                {
                    e.Row.BackColor = Color.Gold;
                }
            }
        }
Result 
________________________________________________________________________________
________________________________________________________________________________

Demo 2:-

1.  Highlight only cell
2.   If Salary is less than 30000 than set the cell Background Colour to Yellow and If greater than Gold

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            // Check for that the row is a data row & not header or footer 
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                if (Convert.ToInt32(e.Row.Cells[4].Text) < 30000)
                {
                    e.Row.Cells[4].BackColor = Color.Yellow;
                }
                else
                {
                    e.Row.Cells[4].BackColor = Color.Gold;
                }           
     }
        }
Result


________________________________________________________________________________
________________________________________________________________________________

Demo 3:- 

1.    Highlight cell with bold
2.  If Salary is less than 30000 than set the Salary cell bold

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            // Check for that the row is a data row & not header or footer 
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                if (Convert.ToInt32(e.Row.Cells[4].Text) < 30000)
                {
                    e.Row.Cells[4].Text = "<b>" + e.Row.Cells[4].Text + "</b>";
                }         
      }
        }
Result
 
________________________________________________________________________________
________________________________________________________________________________

Demo 4:- 

1.  Add JavaScript in cell
2.  If Salary is less than 30000 than add JavaScript alert message to the Salary cell

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            // Check for that the row is a data row & not header or footer 
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                if (Convert.ToInt32(e.Row.Cells[4].Text) < 30000)
                {
                    string name = e.Row.Cells[1].Text + " " + e.Row.Cells[2].Text + " " + e.Row.Cells[3].Text;
                    e.Row.Cells[4].Text = "<a href=\"#\" onclick=\"javascript:Message('" + name + "','" + e.Row.Cells[4].Text + "')\">" + e.Row.Cells[4].Text + "</a>";
                }
      }
        }
Result



________________________________________________________________________________
________________________________________________________________________________

Demo 5:- 

1.   Get DataKey value

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            // Check for that the row is a data row & not header or footer 
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                var dataKey = GridView1.DataKeys[e.Row.RowIndex];
                if (dataKey != null)
                {
                    string dataKeyValue = dataKey.Values[0].ToString();
         }     
     }
        }

Note:-

1.  You can use the DataKey Name also instead of DataKey Index to get value
2.  If You are using the DataKey Name So your code look like this

string dataKeyValue = dataKey.Values["ID"].ToString();

Result