Custom Start and Finish Time Business Logic using ID Fields

Tips submitted by ASP.NET Maker users

Custom Start and Finish Time Business Logic using ID Fields

Postby xgis » Sat Dec 14, 2013 10:50 am

This example demonstrates the use of ID fields to integrate time business logic to avoid incorrect data entry.
The critical element of this example is the order of the unique IDENTITY fields that start the day at 06:00 AM.
The table is used for DJ bookings that are generally for night based activities, although can extend into the morning hours.
The proposed method uses both the Row_Inserting and Row_Updating events to prevent incorrect data update and alert the user if an incorrect selection is being made.

Time in this example is in 15 minute intervals over a period of 24 hours.
Time in this example is stored as text in an nvarchar(12) field type to overcome formatting issues.
In the ANM10 'Div Tag Attributes' the 'Format' is 'Date/Time' and the 'Date/Time named format' is "Long Time" to properly display AM/PM.

The selections for time start and finish are stored as type INT in 2 separate fields in the data table.
The display field [DJSTime] is the nvarchar time field eg 5:15 PM
There is an extra field added called [DJSTimeOrder] which is used to ORDER the display for the end user.
The reason for this is to show the most commonly selected times first, which in this case are afternoon times.

Please note this example uses both Row_Inserting and Row_Updating events and ONLY deals with new records (ie rsnew).
The Business Logic event codes;

// Row Inserting event
public bool Row_Inserting(OrderedDictionary rsold, ref OrderedDictionary rsnew) {
if (Convert.ToInt32(rsnew["DJSTimeIDFinish"]) < Convert.ToInt32(rsnew["DJSTimeIDStart"])) {
CancelMessage = "The finish time must be greater than the start time.";
return false;
}
return true;
}


// Row Updating event
public bool Row_Updating(OrderedDictionary rsold, ref OrderedDictionary rsnew) {
//ew_End(rsold, rsnew); // Print the old and new record end the script
if (Convert.ToInt32(rsnew["DJSTimeIDFinish"]) < Convert.ToInt32(rsnew["DJSTimeIDStart"])) {
CancelMessage = "The finish time must be greater than the start time.";
return false;
}
return true;
}


Below are the MSSQL Table Generation Codes;
To customise use a text editor and Find/Replace database, table or field names.
Another consideration would be to add an extra field eg of Type INT to store a value of 1 on each hour to colour HOURLY values using Row_Rendered events.

The SQL Code

USE [DATABASENAME]
GO
/****** Object: Table [dbo].[DJSTime] Script Date: 14/12/2013 11:50:09 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DJSTime](
[DJSTimeID] [int] IDENTITY(1,1) NOT NULL,
[DJSTime] [nvarchar](12) NOT NULL,
[DJSTimeOrder] [int] NOT NULL
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[DJSTime] OFF

GO
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'6:00:00 AM',53)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'6:15:00 AM',54)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'6:30:00 AM',55)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'6:45:00 AM',56)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'7:00:00 AM',57)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'7:15:00 AM',58)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'7:30:00 AM',59)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'7:45:00 AM',60)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'8:00:00 AM',61)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'8:15:00 AM',62)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'8:30:00 AM',63)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'8:45:00 AM',64)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'9:00:00 AM',65)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'9:15:00 AM',66)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'9:30:00 AM',67)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'9:45:00 AM',68)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'10:00:00 AM',69)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'10:15:00 AM',70)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'10:30:00 AM',71)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'10:45:00 AM',72)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'11:00:00 AM',73)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'11:15:00 AM',74)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'11:30:00 AM',75)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'11:45:00 AM',76)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'12:00:00 PM',77)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'12:15:00 PM',78)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'12:30:00 PM',79)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'12:45:00 PM',80)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'1:00:00 PM',81)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'1:15:00 PM',82)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'1:30:00 PM',83)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'1:45:00 PM',84)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'2:00:00 PM',85)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'2:15:00 PM',86)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'2:30:00 PM',87)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'2:45:00 PM',88)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'3:00:00 PM',89)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'3:15:00 PM',90)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'3:30:00 PM',91)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'3:45:00 PM',92)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'4:00:00 PM',93)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'4:15:00 PM',94)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'4:30:00 PM',95)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'4:45:00 PM',96)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'5:00:00 PM',1)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'5:15:00 PM',2)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'5:30:00 PM',3)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'5:45:00 PM',4)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'6:00:00 PM',5)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'6:15:00 PM',6)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'6:30:00 PM',7)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'6:45:00 PM',8)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'7:00:00 PM',9)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'7:15:00 PM',10)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'7:30:00 PM',11)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'7:45:00 PM',12)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'8:00:00 PM',13)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'8:15:00 PM',14)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'8:30:00 PM',15)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'8:45:00 PM',16)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'9:00:00 PM',17)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'9:15:00 PM',18)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'9:30:00 PM',19)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'9:45:00 PM',20)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'10:00:00 PM',21)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'10:15:00 PM',22)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'10:30:00 PM',23)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'10:45:00 PM',24)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'11:00:00 PM',25)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'11:15:00 PM',26)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'11:30:00 PM',27)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'11:45:00 PM',28)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'12:00:00 AM',29)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'12:15:00 AM',30)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'12:30:00 AM',31)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'12:45:00 AM',32)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'1:00:00 AM',33)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'1:15:00 AM',34)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'1:30:00 AM',35)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'1:45:00 AM',36)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'2:00:00 AM',37)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'2:15:00 AM',38)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'2:30:00 AM',39)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'2:45:00 AM',40)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'3:00:00 AM',41)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'3:15:00 AM',42)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'3:30:00 AM',43)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'3:45:00 AM',44)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'4:00:00 AM',45)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'4:15:00 AM',46)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'4:30:00 AM',47)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'4:45:00 AM',48)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'5:00:00 AM',49)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'5:15:00 AM',50)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'5:30:00 AM',51)
INSERT [dbo].[DJSTime] ([DJSTime],[DJSTimeOrder]) VALUES (N'5:45:00 AM',52)

GO
SET IDENTITY_INSERT [dbo].[DJSTime] OFF
GO
xgis
 
Posts: 87
Joined: Thu Jan 17, 2013 10:40 pm

Return to User Submited Tips (ASP.NET Maker)