Saturday, July 7, 2012

One Simple Procedure for Cursor.


Procedure That make insertion into table according weekdays which weekday data I have in my one table.

ALTER PROC [dbo].[CRS_VehicleAdultChildPrice_Flat]        
@pk_VehiclePriceID int,      
@WEEKDAYID NVARCHAR (255),
@SeasonEndDate NVARCHAR(25),
@SeasonStartDate NVARCHAR(25)    
AS      
DECLARE @fkVehiclePriceID int      
DECLARE @SessionDate  NVARCHAR(25)        
DECLARE @ChildNo int
DECLARE @ChildMinAge int
DECLARE @ChildMaxAge int    
DECLARE @AdultPrice decimal(18,2)
DECLARE @ChildPrice decimal(18,2)        
DECLARE @TourDate datetime    
DECLARE @Culture nvarchar(10)      
DECLARE @CreatedBy nvarchar(255)      
DECLARE @ModifiedBy nvarchar(255)    
DECLARE @timings nvarchar(1000)  

DECLARE @CUR CURSOR      
DECLARE @CURDATE CURSOR        

SET @CUR = CURSOR FOR SELECT        
pk_VehiclePriceID    
,AdultPrice
,VMS_TBL_VehiclePrice.Culture      
,VMS_TBL_VehiclePrice.CreatedBy      
,VMS_TBL_VehiclePrice.ModifiedBy
,VMS_TBL_VehiclePrice.timing


 FROM VMS_TBL_VehiclePrice        
INNER JOIN VMS_VehicleAdultPrice_Detail ON VMS_VehicleAdultPrice_Detail.fkVehiclePriceId=VMS_TBL_VehiclePrice.pk_VehiclePriceID      
WHERE pk_VehiclePriceID=@pk_VehiclePriceID ORDER BY pk_VehiclePriceID    
OPEN @CUR      
FETCH NEXT      
FROM @CUR INTO        
@fkVehiclePriceID,    
@AdultPrice,      
@Culture,      
@CreatedBy,      
@ModifiedBy,
@timings      
 
WHILE @@FETCH_STATUS = 0      
BEGIN      
     
   SET @CURDATE = CURSOR FOR SELECT        
   DATE_NAME FROM TBL_DATE_WEEKDAY WHERE WEEKDAYID IN (SELECT * FROM DBO.SPLIT(@WEEKDAYID,',')) AND ( CONVERT(DATETIME,DATE_NAME)>=Convert(DATETIME,@SeasonStartDate) AND CONVERT(DATETIME,DATE_NAME) <=Convert(DATETIME,@SeasonEndDate))      
   OPEN @CURDATE      
   FETCH NEXT      
   FROM @CURDATE INTO @SessionDate      
   print @@FETCH_STATUS      
   WHILE @@FETCH_STATUS = 0      
   BEGIN      
   print @SessionDate      
         
     
         INSERT INTO VMS_VehicleAdultPrice_Flat(fkVehiclePriceID, AdultPrice,TourDate, Culture, CreatedBy, CreatedOnDate, ModifiedBy, ModifiedOnDate, Row_Guid, Row_Status,timings)      
          VALUES(@fkVehiclePriceID,@AdultPrice,@SessionDate ,@Culture,@CreatedBy,GETDATE(),@ModifiedBy,GETDATE(),NEWID(),1,@timings)          
     
           
     
   FETCH NEXT      
   FROM @CURDATE INTO @SessionDate    
   END      
   CLOSE @CURDATE      
   DEALLOCATE @CURDATE      
   
FETCH NEXT      
FROM @CUR INTO @fkVehiclePriceID,  
   
@AdultPrice,  

@Culture,      
@CreatedBy,      
@ModifiedBy,
@timings      
 
 
END    
CLOSE @CUR      
DEALLOCATE @CUR



SET @CUR = CURSOR FOR SELECT        
pk_VehiclePriceID    
,ChildNo
,ChildMinAge
,ChildMaxAge
,ChildPrice

,VMS_TBL_VehiclePrice.Culture      
,VMS_TBL_VehiclePrice.CreatedBy      
,VMS_TBL_VehiclePrice.ModifiedBy

 FROM VMS_TBL_VehiclePrice        
INNER JOIN VMS_TBL_ChildPrice_Detail ON VMS_TBL_ChildPrice_Detail.fkVehiclePriceId=VMS_TBL_VehiclePrice.pk_VehiclePriceID        
WHERE pk_VehiclePriceID=@pk_VehiclePriceID ORDER BY pk_VehiclePriceID    
OPEN @CUR      
FETCH NEXT      
FROM @CUR INTO        
@fkVehiclePriceID,
@ChildNo,      
@ChildMinAge,      
@ChildMaxAge,      
@ChildPrice,      
     
@Culture,      
@CreatedBy,      
@ModifiedBy      
 
WHILE @@FETCH_STATUS = 0      
BEGIN      
     
   SET @CURDATE = CURSOR FOR SELECT        
   DATE_NAME FROM TBL_DATE_WEEKDAY WHERE WEEKDAYID IN (SELECT * FROM DBO.SPLIT(@WEEKDAYID,',')) AND ( CONVERT(DATETIME,DATE_NAME)>=Convert(DATETIME,@SeasonStartDate) AND CONVERT(DATETIME,DATE_NAME) <=Convert(DATETIME,@SeasonEndDate))      
   OPEN @CURDATE      
   FETCH NEXT      
   FROM @CURDATE INTO @SessionDate      
   print @@FETCH_STATUS      
   WHILE @@FETCH_STATUS = 0      
   BEGIN      
   print @SessionDate      
         
       
       
     
        INSERT INTO VMS_VehicleChildPrice_Flat(fkVehiclePriceID, ChildNo, ChildMinAge, ChildMaxAge, ChildPrice, TourDate, Culture, CreatedBy, CreatedOnDate, ModifiedBy, ModifiedOnDate, Row_Guid, Row_Status)      
          VALUES(@fkVehiclePriceID, @ChildNo, @ChildMinAge, @ChildMaxAge,@ChildPrice,@SessionDate ,@Culture,@CreatedBy,GETDATE(),@ModifiedBy,GETDATE(),NEWID(),1)          
     
     
     
     
   FETCH NEXT      
   FROM @CURDATE INTO @SessionDate    
   END      
   CLOSE @CURDATE      
   DEALLOCATE @CURDATE      
   
FETCH NEXT      
FROM @CUR INTO @fkVehiclePriceID,  
@ChildNo,      
@ChildMinAge,      
@ChildMaxAge,  
 
@ChildPrice,
@Culture,      
@CreatedBy,      
@ModifiedBy      
 
 
END    
CLOSE @CUR      
DEALLOCATE @CUR

Time Drop Down Filling.


 public void GetTiming()
        {
            ArrayList aTime = new ArrayList();
            int interval = Convert.ToInt32(txtTimeInterval.Text);
            DateTime EndTime = Convert.ToDateTime(txtEndTime.Text);
            DateTime StartTime = Convert.ToDateTime(txtStartTime.Text);
           
            for (int i = interval; i <= 1440+1; i++ )
            {
                if (StartTime != EndTime)
                {
                  aTime.Add(StartTime.ToString("HH:mm"));
                  StartTime = StartTime.AddMinutes(interval);
               
                }
                else if (StartTime == EndTime)
                {
                    aTime.Add(StartTime.ToString("HH:mm"));
                    break;
                }
            }
            chkTiming.DDList.DataSource = aTime;
            chkTiming.DataBind();
            chktime.Visible = true;
           
        }

Regular Expression For Specific Time Format("HH:MM")


asp:TextBox ID="txtStartTime" runat="server" Width="100" asp:TextBox
                                                                                    asp:RequiredFieldValidator ID="RequiredFieldValidator11" runat="server" ControlToValidate="txtStartTime"
                                                                                            Display="None" ErrorMessage="Start Time missing" SetFocusOnError="True"
                                                                                            ValidationGroup="validComboAddGrp" asp:RequiredFieldValidator
                                                                                            asp:RegularExpressionValidator ID="regextxtSessionTime" runat="server"
    ControlToValidate="txtStartTime"
    ValidationExpression="^([0-1][0-9]|[2][0-3]):([0-5][0-9])$"
    ErrorMessage="You must enter a valid time. Format: HH:MM"
    Display="none"
    SetFocusOnError="true" ValidationGroup="validComboAddGrp"
  asp:RegularExpressionValidator