SQLSERVER Stored Procedure Dynamic Query Pivot Table DateTime parameter

 USE [TNG_KMS]  
 GO  
 /****** Object: StoredProcedure [dbo].[SP_S_GetCompletedInjectionReport]  Script Date: 18/7/2016 10:52:36 AM ******/  
 SET ANSI_NULLS ON  
 GO  
 SET QUOTED_IDENTIFIER ON  
 GO  
 DROP PROCEDURE [dbo].[SP_S_GetCompletedInjectionReport]  
 GO  
 CREATE PROCEDURE [dbo].[SP_S_GetCompletedInjectionReport]  
 --(       
 --     -- Add the parameters for the stored procedure here  
      @dt_DateFrom datetime ,   
      @dt_DateTo datetime  
 --)  
 AS  
 BEGIN  
      -- SET NOCOUNT ON added to prevent extra result sets from  
      -- interfering with SELECT statements.  
      SET NOCOUNT ON;  
      SET FMTONLY OFF;  
   -- Insert statements for procedure here  
 DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)  
 DECLARE @ColumnName AS NVARCHAR(MAX)  
 SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(name)  
 FROM (select name from LookupItems where CategoryID=4) AS AppSector  
 ORDER BY name  
 SET @DynamicPivotQuery =  
 'SELECT * FROM (  
   select   
      @@ROWCOUNT No,      FormRefNo,   
      ISNULL(TotalSam,0) BigTotalSam,   
      R.CreatedTime,R.CompletionDate,  
       Datediff(dd,CONVERT(Date,R.CreatedTime),R.CompletionDate) ''DaysCompleted'',  
      LI.Name [AppSector]  
      from   
       [TNG_KMS].[dbo].[Request] R,   
       [TNG_KMS].[dbo].[ProfileGroup] PG,  
       [TNG_KMS].[dbo].[LookupItems] LI  
       where R.ProfileGroupID=PG.ID  
       and PG.AppSectorID=LI.ItemIndex   
       and LI.CategoryID = 4  
       and R.RequestStatusID=5  
       and (R.CompletionDate between @dt_DateFrom and @dt_DateTo)  
 ) as s  
 PIVOT  
 (  
   SUM(BigTotalSam) FOR [AppSector] IN (' +@ColumnName+ ')  
 ) AS pvt'  
 ;  
 --Execute the Dynamic Pivot Query  
 EXEC sp_executesql @DynamicPivotQuery,        
                          N'@dt_DateFrom DATETIME, @dt_DateTo DATETIME',  
                          @dt_DateFrom,   
                          @dt_DateTo;   
 END  
 GO  

Fixed: The selected stored procedure returns no columns

Hi johnjohn123123, The problem maybe caused by your stored procedures may contain: Dynamic queries Temporary tables While EF doesn't support importing stored procedures above. The reason is that to import the procedure EF must execute it. Such operation can be dangerous because it can trigger some changes in the database I suggest you add following code in the start of your stored procedure SET FMTONLY OFF then save the Stored Procedure, Update the model and retry. Hope it can help you. Best Regards, Kevin Shen.

Nota ASP.Net 4

• 3 Different ways to add AjaxControlToolkit in Asp .Net Website.
• Method Error 500 Cascadingdropdown Ajax
• Tip/Trick: How to Register User Controls and Custom Controls in Web.config

AUTOCOMPLETE

• http://www.c-sharpcorner.com/UploadFile/0c1bb2/autocomplete-extender-using-Asp-Net/ 
• ASP.Net AJAX AutoCompleteExtender: Pass Additional Parameter to WebMethod using ContextKey 

$get() & $find()

• [AJAX] $get And $find ASP.NET AJAX Functions

UPDATE PANEL

• UpdatePanel Control Overview
• http://ajax.net-tutorials.com/controls/updatepanel-control/

 Invalid PostBack : Cascading dropdown

• https://johanleino.wordpress.com/2011/04/09/revised-cascadingdropdown-causes-invalid-postback-or-callback-argument-error/ 

DYNAMIC GRIDVIEW

• http://stackoverflow.com/questions/11685470/how-to-add-dynamic-n-checkbox-columns-to-gridview-asp-net 
• http://highoncoding.com/Articles/219_GridView_All_Rows_in_Edit_Mode.aspx

LOGIN

• IMPLEMENTING ASP.NET FORMS AUTHENTICATION WITH ACTIVE DIRECTORY MEMBERSHIP PROVIDER

NLOG

• http://rickyrosario.com/blog/asp-net-logging-using-nlog/

GRIDVIEW

• http://www.codeproject.com/Tips/80964/How-to-get-Hidden-Column-Value-in-GridView

TIPS

• Checklist: What NOT to do in ASP.NET

Quartz

• Scheduled Tasks In ASP.NET With Quartz.Net
• Quartz.NET 2.x Tutorial
• Configure NLog for logging

MEDIA

• http://preloaders.net/

Java: @ModelAttribute

Masalah: properties yang tiada element pada .jsp disetkan sebagai null.

Cara mengatasinya adalah menggunakan @ModelAttribute di mana ada 2 jenis yang boleh disemak pada docs spring-framework.

@ModelAttribute pada function akan dipanggil pabila sesuatu model yang serupa pada parameter ditemui.

UPDATE! walaupun tanpa @ModelAttribute("studyLeaveSupervisor") di hadapan StudyLeaveSupervisor dalam parameter @RequestMapping, function pada @ModelAttribute tetap dipanggil.

Jadi kita boleh gunakan function ini untuk dapatkan data daripada DB contohnya. Kemudian data ini akan di-override oleh value pada page.

@ModelAttribute

      /**  
       * Init data daripada DB ke dalam model sebelum di-override oleh data daripada form apabila jumpa parameter yang declared  
       * sebagai <code>@ModelAttribute("studyLeaveSupervisor") </code>. Ini adalah untuk elakkan field yang tiada dalam form di-set value-nya kepada null.  
       * ModelAttribute akan dipanggil sebelum mana-mana RequestMapping yang berkaitan.  
       * @param p2 nama field dalam page  
       * @param slsStudyleaveRefcode nama field dalam page  
       * @param slsSupervisorId nama field dalam page  
       * @return  
       */  
      @ModelAttribute("studyLeaveSupervisor")  
      public StudyLeaveSupervisor preloadStudyLeaveSupervisor( String p2, String slsStudyleaveRefcode, String slsSupervisorId) {  
         if( !StringUtil.isEmpty(slsStudyleaveRefcode) && !StringUtil.isEmpty(p2) && !StringUtil.isEmpty(slsSupervisorId) )  
         {  
                 StudyLeaveSupervisorKey key = new StudyLeaveSupervisorKey();  
                key.setSlsStartDate( DateUtil.parseYYYYMMDDDate( p2 ) );  
                key.setSlsStudyleaveRefcode(slsStudyleaveRefcode);  
                key.setSlsSupervisorId(slsSupervisorId);  
                System.out.println("\t>> loadStudyLeaveSupervisor = " + key.toString() );  
                return studyLeaveService.getSupervisorById(key);  
         }  
         else{  
              return new StudyLeaveSupervisor();  
         }  
      }  

@RequestMapping

      @RequestMapping(method = RequestMethod.PUT)  
      public String update(@ModelAttribute("studyLeaveSupervisor") @Valid StudyLeaveSupervisor studyLeaveSupervisor,   
                BindingResult bindingResult, Model uiModel, HttpServletRequest request, HttpSession session)   
      {  
           MessageObject msj = new MessageObject();  
           try{  
                if (studyLeaveSupervisor != null){  
                     studyLeaveSupervisor.setSlsUpdateDate( new Date() );  
                     studyLeaveSupervisor.setSlsUpdateBy( StringUtil.getString( session.getAttribute( SystemConstant.VAR_SESSION_USERID ) ) );  
                     logger.debug("\tupdate: \tsupervisor = " + studyLeaveSupervisor.toString());  
                     studyLeaveService.updateStudyLeaveSupervisor(studyLeaveSupervisor);  
                }  
                msj = new MessageObject(false, "Successfully update record.", null, studyLeaveSupervisor.getSlsSupervisorId());  
           }catch(Exception e){  
                msj = new MessageObject(true, "Fail to update record.", e.getMessage(), studyLeaveSupervisor.getSlsSupervisorId());  
                e.printStackTrace();  
                uiModel.addAttribute( ViewVariables.VAR_MSJ_GENERIC, msj);  
                return ADD_EDIT_PAGE_URL;  
           }  
           uiModel.addAttribute( ViewVariables.VAR_MSJ_GENERIC, msj);  
           return "redirect:"+LIST_FULLMAPPING;  
      }  

JS: Toggle Visibility

<!--
  function toggle_visibility(id, div_id) {
     var e1 = document.getElementById(id);
     var e2 = document.getElementById(div_id);
     
     if(e1.value=='OTHERS')
    e2.style.display='block';
   else
    e2.style.display='none';
  }
  //--> 

Edit launch config server pada STS

Double click server

Click "Open launch configuration"

Tukar config secukup rasa.

Simple ANT build.xml run in STS

ANT akan memudahkan proses kompil projek ke dalam pakej yang sesuai untuk deployment.

ANT menggunakan satu fail yang lazimnya dinamakan build.xml yang mengandungi arahan salinan fail daripada source-code kepada arkib.

Spring Tool Suite telah mempunyai plug-in yang memudahkan penggunaan ANT.

Right click build.xml

Pilih target yang sesuai, default telah disetkan kepada war di mana *.war akan dihasilkan.

Mesej setelah semua proses selesai.

Boleh rujuk kepada Help dalam STS untuk bantuan menggunakan ANT Support:

Rujukan:

• http://ant.apache.org/
• http://ant.apache.org/external.html; external tool ~ contohnya plugin GUI untuk Eclipse.
•