SQL Paging

This stored procedure provides a simple method of SQL paging

 

CREATE PROCEDURE [dbo].[Up_Guestbook_List]
@PageNumber int,
@PageSize int
as
begin
set nocount on
declare @TotalRows int
declare @FirstRow int
declare @FirstRowId int
declare @PageTotal float
select @TotalRows = count(id) from Guestbook where status = 1
set @PageTotal = ceiling(cast(@TotalRows as float) / cast(@PageSize as float))
if (@PageNumber < 1) set @PageNumber = 1
if (@PageNumber > @PageTotal) set @PageNumber = @PageTotal
select @FirstRow = (@PageNumber - 1) * @PageSize + 1
if (@FirstRow <= @TotalRows)
begin
set rowcount @FirstRow
select @FirstRowId = id
from Guestbook where status = 1
order by 1c
set rowcount @PageSize
select
@PageNumber as PageNumber,
@PageSize as PageSize,
@PageTotal as PageTotal,
*
from Guestbook
where id >= @FirstRowId and status = 1
order by 1
end
set nocount off
end

Advertisements

Silverlight 1.0 Drag and Drop

See the toucan sample: http://www.hoffmancentral.org/sample/silverlight.htm

JavaScript Numeric Only Input

Use the following JavaScript function to only allow numeric (0-9) input and backspace input in a web form text field.

JavaScript:

function numbersonly(e) {
var unicode=e.charCode? e.charCode : e.keyCode;
if (unicode!=8){ //if the key isn't the backspace key (which we should allow)
if (unicode<48||unicode>57) //if not a number return false //disable key press
}}

Usage:

<input onkeypress=”return numbersonly(event);” name=”VAT_Number”>

To allow for tabbing change (unicode!=8) to (unicode!=8 && unicode!=9)

Removing embedded HTML tags with SQL

This is an interesting piece of sql, the function accepts a string and removes HTML elements that is inside the string. This is genereally useful when you have data that has embedded HTML content on it. via

Create Function dbo.UTILfn_StripTags
(@Dirty varchar(4000))
Returns varchar(4000)
As
Begin
Declare @Start int,
@End int,
@Length int
While CharIndex('<', @Dirty) > 0 And CharIndex('>', @Dirty, CharIndex('<', @Dirty)) > 0
Begin
Select @Start = CharIndex('<', @Dirty),
@End = CharIndex('>', @Dirty, CharIndex('<', @Dirty))
Select @Length = (@End - @Start) + 1
If @Length > 0
Begin
Select @Dirty = Stuff(@Dirty, @Start, @Length, '')
End
End
return @Dirty
End

SQL Split Function

This SQL function is similar to the VB split function. It takes a nvarchar delimeted list and delimeter and returns a table with the values split on delimeter.

if exists
(select * from dbo.sysobjects where id = object_id(N'[dbo].[UTILfn_Split]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[UTILfn_Split]
GO
create function dbo.UTILfn_Split(
@String nvarchar (4000),
@Delimiter nvarchar (10)
)
returns @ValueTable table ([Value] nvarchar(4000))
begin
declare @NextString nvarchar(4000)
declare @Pos int
declare @NextPos int
declare @CommaCheck nvarchar(1)
--Initialize
set @NextString = ''
set @CommaCheck = right(@String,1)
--Check for trailing Comma, if not exists, INSERT
--if (@CommaCheck <> @Delimiter )
set @String = @String + @Delimiter
--Get position of first Comma
set @Pos = charindex(@Delimiter,@String)
set @NextPos = 1
--Loop while there is still a comma in the String of levels
while (@pos <> 0)
begin
set @NextString = substring(@String,1,@Pos - 1)
insert into @ValueTable ( [Value]) Values (@NextString)
set @String = substring(@String,@pos +1,len(@String))
set @NextPos = @Pos
set @pos = charindex(@Delimiter,@String)
end
return
end

via

Simple Javascript CAPTCHA

Simple CAPTCHA using Javascript. The page collecting the form information should collect from forms only. eg. in ASP: request.forms(“name”)

Click here to download the code: BotBoot1.html

Get month name based in int

for (int i = 1; i <= 12; i++)

{

DateTime date = new DateTime(1,i,1);

DropDownDOBMonth.Items.Add(date.ToString(“MMM”));

}

 

More date formats.