Tuesday, 11 February 2014

SQL Server 2005 Reporting Services: The type 'System.Web.UI.ScriptManager' is ambiguous.

The type 'System.Web.UI.ScriptManager' is ambiguous

If you are trying to run SSRS2005 (or any version really) and you get the following error...

The type 'System.Web.UI.ScriptManager' is ambiguous: it could come from assembly 'C:\WINDOWS\assembly\GAC_MSIL\System.Web.Extensions\1.0.61025.0__31bf3856ad364e35\System.Web.Extensions.dll' or from assembly 'C:\WINDOWS\assembly\GAC_MSIL\System.Web.Extensions\\System.Web.Extensions.dll'. Please specify the assembly explicitly in the type name.

It could be because your web.config file is referring to at least two versions of the same DLL and one of them need to be removed.

Most suggested solutions suggest to delete everything in your /bin folder and rebuild your application. That is not necessary and could cause more problems than it's worth. Besides you could have other applications that actually require the older version of the DLL, which was the case with me.

Your application needs to determine which of the two DLL versions you're going to use and if for some reason you've added both versions into your Assembly, you'll need to remove one of them to cater for that one application. The other application(s) that use the older version - leave them alone.

So in web.config find the following lines (assuming you only need .Net 3.5 and not the .Net 1.1 version) ...

<add assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
<add assembly="System.Web.Extensions.Design, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>

Just comment out or delete those two lines of configuration.


Thursday, 12 December 2013

SQL Server Reporting Services 2005 - How to report BIT data types in plain English

Many of you know that in SQL Server, if you want to record True or False data (the boolean), you have to define a column/field called the Bit Column (http://technet.microsoft.com/en-us/library/ms177603%28v=sql.90%29.aspx).

But how would you report such a little beast in SSRS? This is where the IFF() function comes in handy, because you need to convert the status of these columns into a choice between "something" and "nothing". The "something" in this case is the plain English I was referring to in the title. The "Nothing" gets more interesting because it can be NULL or something yet again.

However, the question really is how to do I treat Bit columns in SSRS to make sense to my readers of my reports?

The answer depends.

Lets say we have multiple Bit data types and each of them mean different things to the context of the database. For example a survey or multiple-choice question form. Such forms require a simple Yes or No answer to individual questions.


"What safety documentation did you fill in today? Tick the ones used."

[ ] JSEA?
[ ] Risk Assessment?
[ ] Work Method Statement?
[ ] Safety Procedures Form?
[ ] Other [details:____________________________]

Of course with the above question, you could tick one or multiples. The "Other" requires details of what 'other' means.

How do we report this in SSRS when the database has 5 BIT data types to record the True or False of each point, and when the report has to report it in a single TextBox control?

What we want to report in the SSRS TextBox is one or more of the above tick boxes. Yes at least ONE tick box must be selected. I also want to separate the selections made by commas.

Now, the problem becomes aparant when you cannot predict what selections were made and in what order (not that it matters). So you need to write a smart expression to handle all possible scenarios and handle which ones were chosen to be able to place the comma on the right one, if at all!

Introducing the power of the IFF() command in SSRS.

=IIF(Fields!JSEA.Value,"JSEA","") + IIF(Fields!JSEA.Value And (Fields!RiskAssessment.Value Or Fields!WorkMethodStatement.Value Or Fields!SafeOpProcedures.Value Or Fields!OtherFlag.Value),", ","") +
 IIF(Fields!RiskAssessment.Value,"Risk Assessment","") + IIF(Fields!RiskAssessment.Value And (Fields!WorkMethodStatement.Value Or Fields!SafeOpProcedures.Value Or Fields!OtherFlag.Value),", ","") +
 IIF(Fields!WorkMethodStatement.Value,"Work Method Statement","") + IIF(Fields!WorkMethodStatement.Value And (Fields!SafeOpProcedures.Value Or Fields!OtherFlag.Value),", ","") + IIF(Fields!SafeOpProcedures.Value,"Safe Operating Procedures","") + IIF(Fields!SafeOpProcedures.Value And Fields!OtherFlag.Value,", ","") +
 IIF(Fields!OtherFlag.Value,"Other: " + Fields!txtOtherFlag.Value,"") +
 IIF(Not Fields!JSEA.Value And Not Fields!RiskAssessment.Value And Not Fields!WorkMethodStatement.Value And Not Fields!SafeOpProcedures.Value And Not Fields!OtherFlag.Value,"","")

This perplexing looking expression string actually does the lot. Starting from the "top", it decides what to display based on what was chosen, but the cool thing is the IIF() with the And and Or to control if the comma should be placed at the end of each selection.

Basically saying, if JSEA was chosen and one of the other selections chosen as well, then place a comma. If none of the other selections were made, then do not show the comma. Note the space after the comma in the string literal (ie: ", "). That's simply to keep things apart and looking neat!

The last IIF() being rather large is actually there to handle the ELSE case, where if none of the selections were made then show a NULL string. And even if all are Selected, show a NULL string. In fact that last bit never happens, which is superflous, but it's necessary here to allow the IFF() function to have the proper syntax and for it to work. Without this last IFF() the SSRS engine will return "#error" as the ELSE response.

SSRS has a small set of powerful but very useful functions, and one of the best is the IFF() function. By simply deciding what we want from top to bottom (old school) and catering for the situation where none of the above has been met, we can handle BIT like data types and report them in meaningful English. We can also do this within a single textbox control and not have to worry about concatenating or multiple textboxes to handle one response. It's also a useful way to create CASE WHEN THEN ELSE in one line. I hope this has been helpful.

Wednesday, 23 October 2013

Temporary Gridviews to mimic a "pop up" window in ASP.net

Lets say for example you have some data you want to show your users that is small by nature, like a list of items or a list of names. And lets say the user should control as to how they view the data (like sorting by column headings) and be able to dismiss (hide it) the data when no longer required.

Sounds like a pop-up screen doesn't it? Well it could be, but in this case it is not necessary because programming pop-up screens in ASP.net is fraught with hours of frustration unless you use AjaxControls or jQuery or some other fancy plugin.

By nature an ASP.net website is "flat" and so things like pop-ups are actually another flat page overlaid over the previous page.

This is an example of where you can quickly create a GridView control with data from your database, to show some information, and allow users to hide that information when no longer needed. It's simple, to the point and it works!

One of the biggest problems with Gridviews that rely on database binding and parameters, is how to pass the true and correct values into the parameters for the SQL to return some data. Many times I've seen developers design their Gridviews, with nice formatting and layouts, but the whole thing comes stuck when linking it to a datasource (<asp:SqlDataSource> to be exact). But that's another story!

We'll explore a simple way to place a gridview on your website, write some fancy code-behind (C#) to bind the datasource the way *we want* and how to hide and display it using a simple link (<asp:linkbutton> actually).

First lets drop a gridview (you can ignore the formating if you wish) ...

<asp:GridView ID="myGV" runat="server" AllowPaging="False" AllowSorting="False" AutoGenerateColumns="False"
    CellPadding="2" ForeColor="#333333" GridLines="None">
    <RowStyle BackColor="#EFF3FB" />
    <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
    <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
    <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
    <EditRowStyle BackColor="#2461BF" />
    <AlternatingRowStyle BackColor="White" />
        <asp:BoundField DataField="StudentPreferredName" HeaderText="Preferred Name" />
        <asp:BoundField DataField="StudentFamilyName" HeaderText="Surname" />
Now lets drop a couple of link buttons. One to fire up the gridview and the other to hide it ...
To show it ...

<asp:LinkButton ID="labShowList" runat="server" Text="Show List" OnClick="labShowList_Click" Visible="true"></asp:LinkButton>
To hide it ... 
<asp:LinkButton ID="labHideList" runat="server" Text="Hide List" OnClick="labHideList_Click" Visible="false"></asp:LinkButton> 
Note the predefined visible properties. One is set to true and the other is set to false. The first link will always be visible to allow users to show the gridview in the first place
and the other is set to false, because we only want to hide the gridview after it's been displayed. 
Note also the gridview does not have a direct link to any datasource. This is deliberate because we want to control this at the back end only when the user clicks the link button
to show the gridview. There is no point showing the link AND the gridview at the same time (unless that's what you really want to achieve).

Now lets look at some of the code behind the scenes ...
protected void labShowList_Click(object sender, EventArgs e)
    //You may not need this line of code, but it's usefil to find the 
    //gridview control we created above, so it can be used in code below.
    GridView dvStudentsExpected = (GridView)dvUnit.FindControl("dvStudentsExpected"); 
    //test if the gridview exists! Meh.. I like to test everything 
    if (dvStudentsExpected != null)
        String strSQL = "SELECT <put your SQL command here>";
        SqlConnection cnn = new SqlConnection("<<put your connection strin to the database here>>");
        SqlCommand cmd = new SqlCommand(strSQL, cnn);
        cmd.CommandTimeout = 1500; //for debugging more than anything
        cmd.Parameters.Add("@myParm1", SqlDbType.VarChar).Value = <<what ever value you need to search by>>;
        cmd.Parameters.Add("@myParm2", ... etc

        //Note: Cannot use SqlReader with a gridview as readers are forward only.
        //You have to use a dataset and dataadapter to load the data to the gridview as a whole. 
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();

        //test if anything was returned from the query. Otherwise do nothing
        if (ds.Tables[0].Rows.Count > 0)
            //Find the Hide List link and if found, show it or hide it if not found
            LinkButton lb = (LinkButton)dvUnit.FindControl("labHideList");
            if (lb != null)
                lb.Visible = true;
                lb.Visible = false;

            //bind the data to the gridview and "pop up" the gridview
            dvStudentsExpected.DataSource = ds;
            dvStudentsExpected.Visible = true;

        //clean up
So you can see from the above code that we are controlling the datasource ourselves, we are controlling what parameters to feed into the SQL command
and we are controlling when to display the gridview. 

Now we need code to manage hiding the gridview from the users when they no longer need it.

protected void labHideList_Click(object sender, EventArgs e)
    //find the Students Expected gridview
    GridView dvStudentsExpected = (GridView)<<whatever control you need to find it in>>.FindControl("dvStudentsExpected");
    if (dvStudentsExpected != null)
        //hide the gridview of students
        dvStudentsExpected.Visible = false;

        //hide the link itself
        LinkButton lb = (LinkButton)dvUnit.FindControl("labHideList");
        if (lb != null)
            lb.Visible = false;
And that's really all to it.


You saw how to define a gridview, how to control the data source required for it to display something, how to create a link to hide it and one to show it.

Thursday, 17 October 2013

How to pass a list of parameters from ASP.net into a SQL Server Report using SSRS2005, using a single parameter

I thought I would share this with you all in case you may have had issues (like I did) with passing multiple values in a single parameter from your web page to a report.
NOTE: This is different from passing multiple parameters, each with its own value into a report. The later, there are plenty of examples on the web.
This is very usefull when you need to basically pass into your report's SQL command a list of values for your report's SQL command to use using a "special" function, and where you do not know the number of times the values may be required, as the user can choose anything from one value to 'n' values (but we will hit a limit, as I'll explain later). It's also useful for generating Excel row-by-row extracts from your website - say for Pivot table handling or charting later on.
Unfortunately using IN() on its own tricks a lot of people and they cannot figure out why it does not work. That's because if you define your report in SSRS to expect a parameter straight into the IN() function, the system literally places the value as a parameter in the function and tries to compare what is essentialy a parameter "data type" with your column's data type and you will get errors.
If you report has SQL similar to this ...
SELECT t.Col1, t.Col2, etc
  FROM myTable t
WHERE t.myColumn IN (@myListOfValues)

where @myListOfValues is something like "'value1','value2','value3',..." it "may" work but I found passing such a string from ASP.net into SSRS did not work and there are technical issues with string handling from the ASP.net side plus a limit depending your system and browser.
To get around possible issues, create a function in your SQL Server database to receive a string of values delimited by a comma and allow the function to turn your list into a table. That way the table can easily be linked using SQL and passed as a sort of "parameter feeder" into your report's SQL or dataset.
So without babbling on too much lets start with code and an example:
Firstly lets create a special utility function that converts a list of values into a table, and by the way this function can be used within your projects to do exactly that - split strings delimited by something into a table for anything else.
Open SQL Server and create a new function using your normal right-click NEW function command. Something like this ...
CREATE FUNCTION [dbo].[fnMakeTableFromList](@List VARCHAR(MAX),@Delimiter VARCHAR(255))
          FROM (SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')   FROM (SELECT [XML] = CONVERT(XML, '<i>' + REPLACE(@List, @Delimiter,'</i><i>')+ '</i>').query('.')) AS a
          CROSS APPLY [XML].nodes('i') AS x(i)) AS y

NOTE: the delimiter does not have to be a single character! Again useful for delimiting using keywords, etc.
Note the XML logic and conversion in the function? That is because ASP.Net is going to literally pass some HTML into SQL Server and we're going to use it to strip off the data we need into a table.
Run the function with some values to test:
SELECT * FROM dbo.fnMakeTableFromList ('a,b,c,d', ',');
You should see 4 rows of data returned ...
That is the results in a table.
Now use this function in your SQL Reporting Services report:
Here is my report as an example:

FROM tblStudents s
LEFT OUTER JOIN dbo.fnMakeTableFromList(@StudentList,',') AS list
ON list.Item = s.StudentID
WHERE (@StudentList IS NULL
    OR @StudentList='')
  AND (l.Item IS NULL
    OR l.Item = s.StudentID)

Note my example also caters for reporting every student ID if there was no value passed at all. So report every student found in tblStudents or report those based on the list of student IDs given, delimited by a comma. When you run this directly in SSRS, you'll be asked for a parameter @StudentList. In there type what ever values you need separated by a comma, and you should only get those student IDs. If not, make sure the report works "stand alone" first before going over to the ASP side.
Once you are happy your report works, and the function in SQL Server works, then we are ready to code the ASP.net side of things:
In your ASPX code behind page (C#) we need to control what the list is and how to pass it over to SSRS. Because we are dealing with a LIST<> here, I am only going to illustrate the way to do using a LIST<> to mimic an array. As you know C# does not have array terminology like you have with VB. So in your ASP.net page paste this code in your PageLoad event ...
    protected void Page_Load(object sender, EventArgs e)
        //get parameters from the URL list
        string strStudentList = Request.QueryString["StudentList"];
        //create a List of parameters and pass it over to the report
        List<ReportParameter> paramList = new List<ReportParameter>();
        paramList.Add(new ReportParameter("StudentList", strStudentList));
        //run the report

Of course some objects in here have to be defined in your ASPx page.
For example I use a master page and as you can see, I did all of this to create a mailing list for printing on special sticky label paper.

<%@ Page Language="C#" MasterPageFile="~/rptsStudentAdministration/StudentAdminReports.master" AutoEventWireup="true" CodeFile="rptStudentLabels.aspx.cs" Inherits="rptsStudentAdministration_rptStudentLabels" Title="Student Mailing Labels" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
    <rsweb:reportviewer id="ReportViewer1" runat="server" font-names="Verdana" font-size="8pt"
        height="800px" processingmode="Remote" width="900px">
<ServerReport ReportServerUrl="<%$ AppSettings:ReportServerURL %>" ReportPath="/rptsStudentAdministration/rptStudentLabels"></ServerReport>

Make sure you are using these as well in your .cs file:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Collections.Generic;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.Reporting.WebForms;

And that's it folks!

If you need to generate a report in SQL Server Reporting services that relies on users selecting none, one or many values to control the logic in the report, then think of passing them all as a single parameter and using a function to turn your values into a table for ease of SQL management. Once you have the SQL working, you should be able to generate the report easily in design mode and using the above ASPx logic, be able to pass all the values delimited by a comma into into your report. An added bonus is to HIDE the parameter in SSRS and that way the user does not have to see what values they chose, and you control the entire report being generated programmatically.

How to reset textbox and checkbox controls in an ASP.net document

One of the most frustrating things about ASP.net is, being a client-server based technology, the "default" design of any ASP.net web form is to assume all control is done at the server side (using code behind like C# or VB.net) and little if any control is done on the client-side (jQuery, javascript, etc). Unfortunately in many situations when developing a form, you need to be able to control the web form on the client side and not the server side. This is especially true when using validation controls such as <asp:RequiredFieldValidator> and <asp:CustomValidator>.

Those controls "kick in" even before your program starts going into the server, and if you need to call methods to reset the form for example (I think you know where this is going!), ASP.net won't let you and hold you "accountable" for the fact some fields in your form could be invalid and they need to be valid before code-behind kicks in.

In these cases, you must code your logic into the ASPX page itself using a scripting language such as JavaScript.

So without further adue, here is the script I use to reset all my textbox and checkbox controls in my web form, and it's neat because you don't get that "flash" effect when the system has gone into your code behind. Obviously if you have other controls, you'll need to code them into this script somehow (Google is your friend!), and I am sure there is more neater or effective ways of doing this in jQuery, but I am not very familiary with it at this stage. Many ways to skin a cat!

         function ResetForm() {
          //get the all the Input type elements in the document
          var AllInputsElements = document.getElementsByTagName('input');
          var TotalInputs = AllInputsElements.length;
          //we have to find the checkboxes and uncheck them
          //note: <asp:checkbox renders to <input type="checkbox" after compiling, which is why we use 'input' above
          for(var i=0;i< TotalInputs ; i++ )
            if(AllInputsElements[i].type =='checkbox')
                AllInputsElements[i].checked = false;
          //reset all textbox controls
          $('input[type=text], textarea').val('');
          return false;

      //This function resets all the validation controls so that they don't "fire" up
      //during a post-back.
      function Page_ClientValidateReset() {
          if (typeof (Page_Validators) != "undefined") {
              for (var i = 0; i < Page_Validators.length; i++) {
                  var validator = Page_Validators[i];
                  validator.isvalid = true;

To call the code, I simply use an <asp:button> control:

My apologies for the code snippett and lack of copy to clipboard and scrollbars. I am still trying to get to terms with SyntaxHighlighter from Alex Gorbatchev. An excellent tool by the way!