sql query for Generate serial no

SELECT C.EmployeeId, C.FirstName,C.LastName,

(SELECT COUNT(*) FROM Employee C2 WHERE C2.EmployeeId <= C.EmployeeId

) as SrNoFROM Employee C ORDER BY EmployeeId

restrict delete record which is used in other table in mssql

1. Create Scalar value Function in sql
ex:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



create FUNCTION [dbo].[GetApplicationDelete]
(
@ApplicationId numeric(18,0)
)
RETURNS bit
AS
BEGIN
-- Declare the return variable here
DECLARE @flg as bit;
declare @count as numeric(18,0);

-- Add the T-SQL statements to compute the return value here
select @count = SUM(b) from
(
SELECT count(*) b from dbo.ApplicationApprover
where ApplicationId = @ApplicationId
union
select count(*) b from .dbo.Cases
where ApplicationId = @ApplicationId



) tablename

if @count = 0
set @flg =1;
else
set @flg =0;


-- Return the result of the function
RETURN @flg

END

2 .Use this function in Sql Query

Ex:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go




create PROCEDURE [dbo].[APPLICATION_FindAll]
AS
SET NOCOUNT ON

SELECT
ApplicationId,
AppCode,
AppName,
Description,
Remarks,
AppType,
IsActive,
IsDelete,
AppStartdate,
AppEnddate,
CreateBy,
CreateDate,
UpdateBy,
UpdateDate,
[dbo].GetApplicationDelete(ApplicationId) delflg
FROM [APPLICATION]

GO

3. Apply in Form in Grid View Delete Button
<asp:TemplateField HeaderText="Delete">

<ItemTemplate>

<asp:ImageButton ID="imgbtnDelete" runat="server" AlternateText="Delete" CausesValidation="False"

CommandName="Delete" ImageUrl="~/App_Themes/Image/Delete.gif" OnClientClick="Are You Sure?"

Visible='<%# Eval("delflg") %>' />

</ItemTemplate>

<ItemStyle HorizontalAlign="Center" />

</asp:TemplateField>

confirmation message on delete in a gridview

protected void gridOverview_OnRowDataBound(object sender, GridViewRowEventArgs e)
//Add delete msgbox to each row containing field
//information to identify the row
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
// reference the Delete ImageButton
ImageButton ib = (ImageButton)e.Row.FindControl(“ibDelete”);
ib.Attributes.Add(“onclick”,“javascript:return ” +

confirm(’Are you sure about deleting ” +
DataBinder.Eval(e.Row.DataItem, ColomnName) + “?’);”);
}
}

OR


Add New Client Click property of button
return confirm(’Are you sure about deleting ”);

OR
Use Ajax Extender for confirmation box
<asp:TemplateField HeaderText=”Delete” >
<ItemStyle CssClass=”grid-column-buttons” />
<ItemTemplate>
<asp:ImageButton ID=”ibDelete” runat=”server”
CausesValidation=”False” CommandName=”Delete”
ImageUrl=”~/Images/icon-delete.gif” />
<ajaxToolkit:ConfirmButtonExtender ID=”ConfirmBtExt1″
runat=”server” TargetControlID=”ibDelete”
ConfirmText=’Are you sure about deleting this record?’ />
ItemTemplate>
asp:TemplateField>