Skip to content

Reporting and Data Entering at the same time using Microsoft Reporting Service ReportViewer embedded in an ASP.NET page

April 12, 2013

5S

A Report is for displaying data not for editing. But what if you want to implement both functions using Microsoft SQL Server Reporting Service (SSRS) Report. In a standalone report web page from SSRS, it’s not possible unless you use report parameters for very simple data editing. But if an SSRS report is embedded in a ReportViewer of an ASP.NET page, you could use events that ReportViewer generates and mimicking data editing. I used BookmarkNavigation event.

1. Install SQL Server Data Tools – Business Intelligence if you haven’t yet. Once installed, you will find SQL Server Data Tools under SQL Server 2012 program group in start menu. ReportBuilder does not show all properties.

2. Edit Bookmark property of a report element (typically a textbox) you want to edit using SQL Server Data Tools. Since I needed three parameters to identify which score (o x in the screen shot) user clicked, I composed bookmark name something like “LTRIM(STR(Fields!ID_Audit.Value))+”:”+LTRIM(STR(Fields!ID_Item.Value))+”:2″ (example score=2). Also set the action property of the textbox to “Go to a Bookmark” as described in the capture below.

5s bookmark

3. In the code-behind file of ReportViewer host page, add the following event handler. You have to extract three parameters from the BookmarkNavigationEventArgs and update database.

4. After database updated, refresh the ReportViewer. There will be some flickering between postbacks. (Let them) Deal with it or try some UpdatePanel (I tried without success).

5. If you pop up a modal dialog extender panel for input inside the BookmarkNavigation event handler, you can edit text data fields(comment field in the screen shot, for example) too.

    Private Sub ReportViewer1_BookmarkNavigation(sender As Object, e As Microsoft.Reporting.WebForms.BookmarkNavigationEventArgs) Handles ReportViewer1.BookmarkNavigation
        e.Cancel = True 'We didn't want to navigate actually. We use this event just for communication between ReportViewer & WebServer        

        'This assumes the BookmarkId is a three-word string delimited by ":" (ID_Audit:ID_Item:Score)
        Dim tokens() As String = e.BookmarkId.Split(":")

        If tokens.Length >= 3 Then
            Dim ID_Audit As Integer = Val(tokens(0))
            Dim ID_Item As Integer = Val(tokens(1))
            Dim Score As Integer
            Int32.TryParse(tokens(2), Score)

            'Update database 
            ObjectDataSource_AuditDetail.UpdateParameters("ID_Audit").DefaultValue = ID_Audit
            ObjectDataSource_AuditDetail.UpdateParameters("ID_Item").DefaultValue = ID_Item
            ObjectDataSource_AuditDetail.UpdateParameters("Score").DefaultValue = Score
            ObjectDataSource_AuditDetail.Update()

            'After database is updated, refresh the ReportViewer
        End If
    End Sub

You might ask what’s the point of using SSRS report when you are already developing an ASP.NET web application which can have rich interface for data editing. Well, sometimes you need a very quick one page implementation of data reporting which also needs relatively simple data editing and the Report was already developed (in my case, by other system analyst). Then you may want to take advantage of it.

After completion of this project, I found a similar method described in this link, which was basically using hyperlink action instead of bookmarks. I tried the same method but it bothered me that I had to include web server’s URL in the hyperlinks (with parameters in query string) of the report element because I had to maintain two versions of reports for developing and deploying. Basically it creates one more dependency you have to take care of. Performance seemed to have a slight difference too. The bookmark method seemed just a little bit faster though I am not sure.

How to maintain scroll position of a gridview

April 12, 2013

There are two different approaches depending on whether the gridview is inside an UpdatePanel or not. If it’s not inside an UpdatePanel, then put javascript caller in div definition : “onscroll=SetDivPosition();”

    // Maintain Gridview scroll position. (Partial Postback (UpdatePanel) version)-----
    var yPos;
    var prm = Sys.WebForms.PageRequestManager.getInstance();

    function BeginRequestHandler(sender, args) {
        if ($get('<%= divGridview.ClientID%>') != null) {
            yPos = $get('<%= divGridview.ClientID%>').scrollTop;
        }
    }

    function EndRequestHandler(sender, args) {
        if ($get('<%= divGridview.ClientID%>') != null) {
            $get('<%= divGridview.ClientID%>').scrollTop = yPos;
        }
    }

    prm.add_beginRequest(BeginRequestHandler);
    prm.add_endRequest(EndRequestHandler);
 
    // Maintain Gridview scroll position. (Non-UpdatedPanel version)-----
    window.onload = function () {
        var strCook = document.cookie;
        if (strCook.indexOf("!~") != 0) {
            var intS = strCook.indexOf("!~");
            var intE = strCook.indexOf("~!");
            var strPos = strCook.substring(intS + 2, intE);
            var divGridView = document.getElementById("<%= divGridview.ClientID%>")

            if (divGridView != null) {
                //remembered scroll position might be out of range if the length of GridView was shortened
                if (strPos > divGridView.style.height * 0.92) strPos = divGridView.style.height * 0.92;
                divGridView.scrollTop = strPos;

            }
        }
    }
  
    function SetDivPosition() {
            var intY = document.getElementById("<%= divGridview.ClientID%>").scrollTop;
        //document.title = intY;    //it changes the title of IE tab/window (side effect. disabled.)
        document.cookie = "yPos=!~" + intY + "~!";
    } 

Error “Collection cannot be null. Parameter name: c” in ASP.NET 4.5 WebForm page

January 24, 2013

I was upgrading an ASP.NET 3.5 site to 4.5 and got this error: “Collection cannot be null. Parameter name: c“. It occurs just after page databinding and I couldn’t set any debug point in my code. On top of that Google search for this error didn’t give me much useful information.

I tried to narrow down by removing most databound controls (Griview, Formview, DetailsView) in aspx file and reviving one by one. I finally cornered a DetailsView as the culprit. The error is gone when I added  “AutogenerateRows=”False””  to the DetailsView.

I guess the default behaviour of ASP.NET 4.5 has been changed regarding DetailsView property though I am not sure why this suddenly became an issue when 3.5 site was just working fine.  Anyway, I hope this can help other developers.

Paint Log

March 25, 2012
tags: , ,

 

A manufacturing logging system for our painters.

Snag/Scrap Rejection Management System

March 25, 2012
tags: , ,

“Snag” is a part of our non-conformance record (NCR) system, which deals with a relatively light problem which occurs during manufacturing process. I wrote it as an ASP.NET user control and integrated into the ERP Helper system’s Production Order page.

Redundancy in an Alien Language

March 24, 2012

I am not talking about oxymoron-type redundancy (“Please prepay in  advance“) but the one inherent in the  language structure (grammar) such as “those two dogs” (see there are three places that show pluralism). Irregular  variation of verbs, gender variation, postpositions, etc. are all inherent redundancy, which you cannot avoid in a sentence even with perfect grammar without any unnecessary words or duplication.

We know that redundancy in languages, whether it is inherent or over-emphasizing duplication, is invented to overcome the noisy  environment and to improve the efficiency of transmission. When I say noisy, it does not mean only in sound but more diverse conditions which possibly can prevent communication. For example, when a speaker is in a certain posture,  the pronunciation is harder to understand. Or when the listener’s mind is switching on and off with distractions, he or  she can catch only intermittent words spoken, etc.

I wonder if linguists have done some research comparing the amount of inherent redundancy components (can it even be defined quantitatively at all?)  between different languages.  Would it be similar for all languages? Would it be different significantly? If different, in what way? If they are in  similar level then can we think this as a characteristic of us human being? Then when we made a first contact with an alien civilization can we tell something about their environment just by analyzing their inherent redundancy in their language?   Or would their first language sent to us be similar to computer programming languages which extremely suppress any kind of redundancy? Just fun to think about it.

T4 Templates for ASP.NET Web Form CRUD Application

December 31, 2011

My projects are usually simple ASP.NET web form applications with SQL database basic CRUD operations. That’s what you do everyday when you are an in-house developer for a manufacturing company. I was tired of writing the same pattern of code on and on and finally I decided to learn T4 and this is what I came up. It’s very basic and the outputs are not complete code files but just code snippets so that you can insert into your existing ASP.NET web form framework files .

I created an independent Visual Studio 2010 project for my T4 templates following Oleg Sych’s blogs.  Three template files are shown under ‘Templates’ folder. they should be disabled for code generation by removing ‘TextTemplatingFileGenerator‘ in Properties:Custom Tool field leaving it as blank.

You need three code templates for an aspx web form project: an aspx file, a data access layer(DAL) VB file, a SQL stored procedures file for CRUD operations which will be called from the DAL code. The code behind VB code for aspx.vb was simply attached in the aspx template as a script block.

The three green lined ones are the script files which actually generate the ouput code referencing the previously mentioned templates. You can execute each of them by ‘Run custom tool’ context menu. Then, the actual output files are generated under each script file and will have a proper target extensions such as .aspx, .sql, .vb.

Here is an example of an aspx template and script files. Other templates and script files are attached as docx files at the end of this post.

MakeAspx.tt:

<#@ template language="VB" hostSpecific="True" #>
<#@ output extension="aspx" #>

<#@ include file="T4Toolbox.tt" #>
<#@ include file="Templates\AspxTemplate.tt" #>

<#
    Dim template As AspxTemplate = New AspxTemplate()
    template.ServerName="SQL2008"
    template.DatabaseName = "SupplierTest"
    template.TableName = "BudgetCategory"
    template.Render()
#>

AspxTemplate.tt:

<!--
    This template is for generating Aspx page code for DetailsView and GridView with basic CRUD functions. 
    It assumes the databse table includes columns of [ID],[UpdatedBy],[DateUpdated]. -->
<#@ template language="VB" debug="true" #>
<#@ output extension="aspx" #>
<#@ assembly name="Microsoft.SqlServer.ConnectionInfo" #>
<#@ assembly name="Microsoft.SqlServer.Management.Sdk.Sfc" #>
<#@ assembly name="Microsoft.SqlServer.Smo" #>
<#@ assembly Name="System.Xml" #>
<#@ assembly Name="System.Core.dll" #>
<#@ assembly Name="System.Xml.Linq.dll" #>
<#@ assembly Name="System.Windows.Forms.dll" #>
<#@ import namespace="System" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Diagnostics" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Xml" #>
<#@ import namespace="System.Xml.Linq" #>
<#@ import namespace="System.Collections" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="Microsoft.VisualBasic" #>
<#@ import namespace="Microsoft.SqlServer.Management.Smo" #>
<#@ import namespace="T4Toolbox" #>
<#@ include file="Utility.tt" #>
<#+
Public Class AspxTemplate
 Inherits Template
 
 Public ServerName As String
 Public DatabaseName As String
 Public TableName As String
 
 Private InputParamTypeName As String
  
 Public Overrides Function TransformText() As String
  If TableName is nothing then
   Throw new Exception("TableName is not given.")
   Return ""
  End if
 
  Dim server As Server = New Server(ServerName)
        Dim database As Database = New Database(server, DatabaseName)
        Dim table As Table = New Table(database, TableName)
        table.Refresh()  
#>
<%@ Page Title="<#= table.Name #>" Language="vb" AutoEventWireup="false"
    CodeBehind="MakeVb.vb" Inherits="ProjectName.<#= table.Name #>" %>
<%@ Register TagPrefix="asp" Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit"  %>
xmlns="<a href="http://www.w3.org/1999/xhtml%22%3E%3Cbody%3E%3Cform">http://www.w3.org/1999/xhtml"><body><form</a> id="form1" runat="server">
 
    LinkButton ID="linkAdd" runat="server" Text="Add" />

    <asp:Panel ID="panelAdd" runat="server" Visible="false">
        DetailsView ID="DetailsView_<#= table.Name #>" runat="server"
            AutoGenerateRows="False"  CssClass="auc_formviewdata" DefaultMode="Insert" AllowPaging="true"
            DataSourceID="ObjectDataSource_BudgetCategory" EnableModelValidation="True"
            BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px" ForeColor="#333333" GridLines="None"
            CellPadding="2" >

         <CommandRowStyle BackColor="#E2DED6" Font-Bold="True" Height="20px" />
         <FieldHeaderStyle BackColor="#E9ECF1" Font-Bold="True" Width="80px" HorizontalAlign="Right"/>
         <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
         <EditRowStyle BackColor="#999999" />
         <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
        
         <HeaderStyle BackColor="#897CAF" Font-Bold="True" ForeColor="White" />
         <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /--%>

            <Fields>
<#+
  PushIndent(Vbtab):PushIndent(Vbtab):PushIndent(Vbtab):PushIndent(Vbtab)
        For Each column As Column In table.Columns
   IF column.Name.ToUpper<>"ID" AND column.Name.ToUpper<>"DATEUPDATED" AND column.Name.ToUpper<>"UPDATEDBY" AND NOT column.Computed THEN
    
    WriteLine("")
    PushIndent(Vbtab):
     WriteLine("")
                    PushIndent(Vbtab):
      WriteLine("")
     PopIndent():
                 WriteLine("")
     WriteLine("")
     PushIndent(Vbtab):

    InputParamTypeName=Utility.FromSqlToSystemType(column.DataType.Name)
    IF InputParamTypeName="String" Then
     IF column.Name.ToUpper="NOTE" Then
                     WriteLine("
      PushIndent(Vbtab):
                         WriteLine("Text='' TextMode=""MultiLine"" Rows=""4"" /> ")
      PopIndent()
      
     Elseif column.Name.ToUpper="STATUS" Then
                     WriteLine("
                        PushIndent(Vbtab)
       WriteLine("SelectedValue=''>")
                            WriteLine("")
                            WriteLine("")
      PopIndent()  
                        WriteLine("")
     
     Else 'Normal String
                     WriteLine("
      PushIndent(Vbtab):
                         WriteLine("Text='' /> ")
       Writeline("
       PushIndent(Vbtab):
                             Writeline("ControlToValidate=""txtNew"+column.Name+""" ValidationExpression=""[ ]*[0-9]*[ ]*"" />--%>")
      PopIndent():PopIndent()
     End If
    
    ElseIF InputParamTypeName="Date" Then
                    WriteLine("
     PushIndent(Vbtab):
                        WriteLine("Text='' />")  
                        WriteLine("
                        PushIndent(Vbtab):
       WriteLine("TargetControlID=""txtNew"""+column.Name+""" PopupPosition=""BottomRight"" />")       
                 PopIndent():PopIndent()
     
    ElseIF InputParamTypeName="Boolean" Then
                    WriteLine("
     PushIndent(Vbtab):
                        WriteLine("Checked=''  /> ")
     PopIndent()
    End If
   
    PopIndent()
             WriteLine("")
    PopIndent()
             WriteLine("")
   End If
        Next
  PopIndent():PopIndent():PopIndent():PopIndent()  
#>
                CommandField ShowInsertButton="true" ShowCancelButton="true" />                            
            </Fields>
        </asp:DetailsView>
        <br /><br />
    </asp:Panel>   

    GridViewID="GridView_<#= table.Name #>" runat="server" CssClass="auc_gridviewdata"
        AllowSorting="True" AutoGenerateColumns="False" CellPadding="2" EnableModelValidation="True"
        DataSourceID="ObjectDataSource_BudgetCategory" DataKeyNames="ID" >

        <HeaderStyle HorizontalAlign="Center" VerticalAlign="Middle" CssClass="auc_gridviewheader"
            Wrap="True" BackColor="#726891" ForeColor="White" />
        <PagerStyle CssClass="textbox" Font-Size="X-Small"
            BorderStyle="Solid" BorderWidth="1px" BorderColor="Gray" />

        <Columns>
           CommandField ShowEditButton="true" ShowDeleteButton="true" ItemStyle-Width="60px" />

<#+
  PushIndent(Vbtab):PushIndent(Vbtab):PushIndent(Vbtab):PushIndent(Vbtab)
        For Each column As Column In table.Columns
   IF column.Name.ToUpper<>"ID" AND column.Name.ToUpper<>"DATEUPDATED" AND column.Name.ToUpper<>"UPDATEDBY" AND NOT column.Computed THEN
    
    WriteLine("")
    PushIndent(Vbtab):
     WriteLine("")
                    PushIndent(Vbtab):
      WriteLine("")
     PopIndent():
                 WriteLine("")
     WriteLine("")
     PushIndent(Vbtab):

    InputParamTypeName=Utility.FromSqlToSystemType(column.DataType.Name)
    IF InputParamTypeName="String" Then
     IF column.Name.ToUpper="NOTE" Then
                     WriteLine("
      PushIndent(Vbtab):
                         WriteLine("Text='' TextMode=""MultiLine"" Rows=""4"" /> ")
      PopIndent()
      
     Elseif column.Name.ToUpper="STATUS" Then
                     WriteLine("
                        PushIndent(Vbtab)
       WriteLine("SelectedValue=''>")
                            WriteLine("")
                            WriteLine("")
      PopIndent()  
                        WriteLine("")
     
     Else 'Normal String
                     WriteLine("
      PushIndent(Vbtab):
                         WriteLine("Text='' /> ")
       Writeline("
       PushIndent(Vbtab):
                             Writeline("ControlToValidate=""txt"+column.Name+""" ValidationExpression=""[ ]*[0-9]*[ ]*"" />--%>")
      PopIndent():PopIndent()
     End If
    
    ElseIF InputParamTypeName="Date" Then
                    WriteLine("
     PushIndent(Vbtab):
                        WriteLine("Text='' />")  
                        WriteLine("
                        PushIndent(Vbtab):
       WriteLine("TargetControlID=""txtNew"""+column.Name+""" PopupPosition=""BottomRight"" />")       
                 PopIndent():PopIndent()
     
    ElseIF InputParamTypeName="Boolean" Then
                    WriteLine("
     PushIndent(Vbtab):
                        WriteLine("Checked=''  /> ")
     PopIndent()
    End If
   
             PopIndent():
             WriteLine("")
    PopIndent():
             WriteLine("")
   End If
        Next
  PopIndent():PopIndent():PopIndent():PopIndent()  
#>                 

            BoundFieldDataField="UpdatedBy" HeaderText="Updated By" ReadOnly="true" />
            BoundField DataField="DateUpdated" HeaderText="Date Updated" ReadOnly="true" DataFormatString="{0:dd-MMM-yyyy}" />
            BoundField DataField="ID" ReadOnly="true" Visible="false" />
        </Columns>
    GridView>                  
   
ObjectDataSource ID="ObjectDataSource_<#= table.Name #>" runat="server"
    TypeName="MyCompanyERPService.ObjectDataSource<#= table.Name #>" SelectMethod="Get<#= table.Name #>List"
    UpdateMethod="Update<#= table.Name #>" InsertMethod="Insert<#= table.Name #>" DeleteMethod="Delete<#= table.Name #>">
    <SelectParameters>
        <!--asp:Parameter Name="bOpenOnly" Type="Boolean" DefaultValue="False" /-->
    </SelectParameters>
    <UpdateParameters>
        Int32" />
<#+     PushIndent(Vbtab):PushIndent(Vbtab)
  For Each column As Column In table.Columns
   IF column.Name.ToUpper<>"ID" AND column.Name.ToUpper<>"DATEUPDATED" AND column.Name.ToUpper<>"UPDATEDBY" AND NOT column.Computed THEN
    Write("<asp:Parameter Name="""+column.Name+""" Type=""")
    
    InputParamTypeName=Utility.FromSqlToSystemType(column.DataType.Name)
    Select Case InputParamTypeName.ToUpper
     Case "STRING"
      Write("String"" />")
     Case "INTEGER"
      Write("Int32"" />")
     Case "DOUBLE"
      Write("Double"" />")
     Case "DATE"
      Write("DateTime"" />")
     Case "BOOLEAN"
      Write("Boolean"" />")
     Case Else
      Write("Object"" />")
    End Select
   
    WriteLine("")
   END IF
        Next
  PopIndent():PopIndent()
  #>
  SessionParameter SessionField="UserID" Name="By" Type="String" />
    </UpdateParameters>
    <InsertParameters>
  <#+    
  PushIndent(Vbtab):PushIndent(Vbtab)
  For Each column As Column In table.Columns
   IF column.Name.ToUpper<>"ID" AND column.Name.ToUpper<>"DATEUPDATED" AND column.Name.ToUpper<>"UPDATEDBY" AND NOT column.Computed THEN
    Write("<asp:Parameter Name="""+column.Name+""" Type=""")
    
    InputParamTypeName=Utility.FromSqlToSystemType(column.DataType.Name)
    Select Case InputParamTypeName.ToUpper
     Case "STRING"
      Write("String"" />")
     Case "INTEGER"
      Write("Int32"" />")
     Case "DOUBLE"
      Write("Double"" />")
     Case "DATE"
      Write("DateTime"" />")
     Case "BOOLEAN"
      Write("Boolean"" />")
     Case Else
      Write("Object"" />")
    End Select
   
    WriteLine("")
   END IF
        Next
  PopIndent():PopIndent()
  #>
        SessionParameter SessionField="UserID" Name="By" Type="String" />
        <asp:Parameter Name="ID" Type="Int32" Direction="Output" />
    </InsertParameters>
    <DeleteParameters>
        <asp:Parameter Name="ID" Type="Int32" />
        SessionParameter SessionField="UserID" Name="By" Type="String" />
    </DeleteParameters>
</asp:ObjectDataSource>

<script type="text/VB">
 'This is the code-behind class of the aspx file

    Public Property EditorRoleSum As Integer
        Get
            Return ViewState("<#= table.Name.ToLower #>editorrolesum")
        End Get
        Set(value As Integer)
            ViewState("<#= table.Name.ToLower #>editorrolesum") = value
        End Set
    End Property

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            If (Session("rolesum") And EditorRoleSum) = 0 Then
                linkAdd.Visible = False
                panelAdd.Visible = False
            End If
        End If
    End Sub

    Private Sub GridView_<#= table.Name #>_RowDataBound(sender As Object, e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView_<#= table.Name

#>.RowDataBound
        Dim gv As GridView = CType(sender, GridView)

        If e.Row.RowType = DataControlRowType.DataRow Then
            Utility.GridVewTool.AddDeleteConfirmScript(gv, e.Row, 0, True)

            'Role check
            If (Session("rolesum") And EditorRoleSum) = 0 Then
                e.Row.Cells(0).Visible = False
            Else
                e.Row.Cells(0).Visible = True
            End If
        End If
    End Sub

    Private Sub linkAdd_Click(sender As Object, e As System.EventArgs) Handles linkAdd.Click
        panelAdd.Visible = True
    End Sub

    Private Sub DetailsView_<#= table.Name #>_ItemCommand(sender As Object, e As System.Web.UI.WebControls.DetailsViewCommandEventArgs) Handles DetailsView_<#=

table.Name #>.ItemCommand
        panelAdd.Visible = False
    End Sub

    Private Sub DetailsView_<#= table.Name #>_ItemInserted(sender As Object, e As System.Web.UI.WebControls.DetailsViewInsertedEventArgs) Handles DetailsView_<#=

table.Name #>.ItemInserted
        GridView_<#= table.Name #>.DataBind()
    End Sub 
</script>
</form></body></html>
<#+
        Return Me.GenerationEnvironment.ToString()
 End Function
End Class
#>

Attachments:  AspxTemplate, VbTemplate, CrudTemplate, Scripts