20 October 2010

Simulate Report Alerts in Crystal Reports 2008 Server

Working with Crystal Reports can be tricky.  Anyone who publishes the reports on a BOE Server or Crystal Reports 2008 server has to relearn some of the aspects of the reports to create similar functionality to the desktop application.

Recently I was asked to implement an alerting scheme in a crystal report that was published to the server.  Since online reports do not support alerts the same way desktop reports do, this is a tricky problem.

The way we decided to do it was to put the alert in the report header.  We then tested for the condition that would trigger an alert.  If the condition was triggered, we made the report header visible.  Otherwise, we hid the report header.

This is accomplished with three formulas:
  • The first formula declares the test variable and initializes the value to false.
  • The second formula is placed in the details section of the report.  This formula tests for the condition and, if triggered, sets the test variable to true.  This formula uses the Crystal WhileReadingRecords function. 
  • The final formula evaluates the test variable.  If true, the header is made visible (with a warning message), otherwise it is hidden.  This function uses the Crystal EvaluateAfter function.
Here's exactly how it works.

Create a new formula called Declare Globals.  The code in this formula is as follows.  Place this formula in the header and make it invisible.  T is the test variable, and the test is initialized to false.

@Declare Globals code - placed in header

global booleanvar t;  // t is for test
t:= false;

Next, create another formula called Test Records.  In this example, the test is done in SQL, but it could be done in the report.  The only result of the test is to set the test variable to true if the test is triggered.  This code should be placed in the details section of the report and again made invisible.

@Test Records code - placed in details

global booleanvar t; // reference the variable declared in the header

if ({tblData.bad_data} = -1) then t := true;

Finally, go into Section Expert on the report and choose Report Header > Common tab > Suppress (No Drill Down).  Un-check Suppress and click the formula editor button next to Suppress.  Evaluate t and show/hide the report header based on the value of the test variable.  The report header should have the warning in it in big red letters to alert the user.

Code placed in the Report Header

global booleanvar t; // reference the variable declared in the header

evaluateafter({@Test Records});
not t;

06 October 2010

SQL Server Error 15023: 'User already exists in current database'

I received this error when copying a database from one server to another and then tried to bring up a web application.  The system user that came over with the database was orphaned from the existing login.

The solution is relatively easy (for one login).  Use the sp_change_users_login with the 'update_one' option.  This will directly map the user to the SQL login.

exec sp_change_users_login 'update_one', '[your database user]', '[the SQL login]'

Thanks to Pinal Dave's article on this subject.

27 August 2010

Solving the ASP.Net Double Hop problem

This article is in debt to Andrew Hay's excellent writeup on how he solved his double-hop problem.

The double-hop problem occurs when a central application calls a web-service on a second server, but the credentials are not passed to the second server, causing the web-service call to fail.  This is because IIS does not pass on the credentials to the second machine.

When this happens you will see the error:

System.Net.WebException: The request failed with HTTP status 401: Unauthorized at...

The worst part of this error is that the web service will work from your machine, but not when you move it to the server for testing.

The double-hop will most likely happen if you use the CredentialCache.DefaultNetworkCredentials or CredentialCache.DefaultCredentials to authenticate the service call.

Problem Solved: System.Net.NetworkCredential

The fix is relatively simple:

  • Create a new System.Net.NetworkCredential that uses a service account.  Use this to authenticate the service call.
  • Add the service account to the users group on the target server where the web service is located.

The pseudo code looks like this:

Dataset ds = new DataSet("Grid"); 
using(MyService ws = new MyService) {
     char[] delim = ";".ToCharArray(); 
     string[] creds = ConfiigurationManager.AppSettings["ServiceAcct"].Split(delim);
     ws.Credentials = new System.Net.NetworkCredential(creds[0], creds[1], creds[2]); 
     ds = ws.getDataSet(arg1, arg2);
return ds; 

The Web.Config would have an application setting of "ServiceAccount" with the value "User;Password;Domain".

Follow up with adding the service account to the Users Group on the machine that hosts the web service.  This should solve the

17 March 2010

ASP.Net Data meets Ext JS: Ext.net – Store and GroupingStore

Download the source code for Ext.net here.


Ext JS is my front-end platform of choice for all my ASP.Net applications.  The reasons are simple – Ext JS gives the developer more control, supports better coding practices, and takes better advantage of the IIS platform than native ASP development options.  But how could that be?

First, there is the question of control.  ASP controls are not open source.  It’s hard for the developer to understand what is going on under the covers without extensive study.  When will the page refresh?  When will the control talk to the server?  How can I give the control a different look?  Why is that Microsoft object emitting CSS?  Many of these questions are difficult for most people to understand and take valuable time to understand. 

Ext JS controls are open source and have a rich, well documented event model.  You can read the code directly if you need to.  Ext JS controls don’t make unexpected calls to the server or emit unexpected code  The developer controls how data is piped from the sever to the screen, which makes it easier to control events and identify performance bottlenecks. 

Simply put, the developer has complete control over everything that call that happens on the client.  There are not many mysteries. And aspects that annoy you can be fixed at programming time by looking at the source code.

Secondly, I have always been bothered by the mixed metaphor of ASP.Net development.  What’s on the browser?  What’s on the server?  Do I code this control with HTML or C#?  For all the remarkable work Microsoft did to make the process seem like coding a Visual Basic application (it is truly amazing), the actual results can be bizarre and fragmented.  .Net languages appear on the page with HTML, which then might have additional code capturing events somewhere else.  The page object model is complex, and ASP developers have to learn when and how to place each control on the page to

Ext JS controls are coded entirely in JavaScript and the separation between browser and server is clearly defined.  There is no half-HTML/half C# (or VB.Net) control that lives partially in code-behind and partially on the page.  There is no reason to carefully wedge control-code between page events to get everything working correctly.

This complete separation tends to drive business logic off the page and back onto the server where it belongs.  My personal preference is to keep the server code and UI code as separate as possible.  Ext JS enables the developer to keep the server-side code simple and clean.  Simply write your UI in JavaScript and expose the web services for the page.  This approach also simplifies testing because the developer can easily identify and test the logic used in each page.

Finally, Ext JS makes it possible to leverage the absolute strength of IIS, which is delivering web services.  It has always struck me as strange that ASP.Net controls do not explicitly consume services.  In an effort to make the communication seamless, Microsoft actually minimizes the strengths of exposing SOAP services.  This, in turn, leads to code-bloat, because the developer has to write custom code to deliver data to ASP.Net controls and then additional code to deliver data to other applications.

Despite all these advantages, communication between Ext JS and ASP web services can be tedious.  Ext JS was written by people who are clearly oriented towards JSON data delivery.  And why not?  It’s lightweight and native to JavaScript.  There is the XML reader, but the average .Net/Ext JS developer still has to write tedious (and needless) code to parse the data into stores.

But IIS (.Net) is an XML-oriented language.  It does not serve JSON without specialized server-side code and some hacking.  What if you work in a corporate environment that does not allow non-standard software to be installed on the server?  Shouldn’t the goal of the programmer be to stick to the standards and also leverage what each piece of software does best?

Ext.net.Store and Ext.net.GroupingStore

Ext JS.net started with simple questions.  Why should I write JavaScript code to describe my data structures, when an ASP.Net web services already delivers that data to my page?  Would my code be less coupled if I worried more about the UI and less about the structure of the data coming to my page? 
The result is Ext.net.Store and Ext.net.GroupingStore.  The goals of this project are as follows:
  • Reduce boilerplate code – on the server and the page
  • Increase web service reuse/simplify server-side code
  • Simplify data transport and consumption
Table names and data types have already been written and defined on the server – why write them again?

How does Ext.net work? 

The Ext.Net stores intercept the XML returned from a web-service call and build the data set before the store is completely initialized.  This means that the data definition that is defined on the server and delivered in the web service automatically becomes the data definition in the store. 
In order to take advantage of the store, the programmer simply exposes an ADO DataSet as the return data type.  Ext.Net stores consumes the meta-data, builds a table definition and then populates it with the service data.

On the server, create a standard data set.  The names of the tables do not matter.

IIS Server Side Code - C#

  public DataSet TableData()
      DataSet ds = new DataSet("ds"); 
      string cnStr = ConfigurationManager.ConnectionStrings["local"].ToString();
      using (SqlConnection cn = new SqlConnection(cnStr))
          string strSQL = "SELECT * FROM tblDataVals";
          using (SqlDataAdapter da = new SqlDataAdapter(strSQL, cn))
              da.Fill(ds, "data");
          strSQL = "SELECT COUNT(*) FROM tblDataVals";
          using (SqlDataAdapter da = new SqlDataAdapter(strSQL, cn))
              da.Fill(ds, "count");
      return ds;

The client-side JavaScript is just as simple.  In your HTML add a reference to Ext.net.Store.  Make sure the reference appears after the references to ExtJs code.

Declare a store by using Ext.net.Store or Ext.net.GroupingStore wherever you would declare a store.  All of the config parameters are the same, with very few differences. 

Ext.net.Store and Ext.net.GroupingStore will create a reader automatically, so you do not have to declare an XMLReader or any record definitions.  On large grids, this will save considerable code. 

There is one extra parameter to add to the grid – net. The net object has 3 fields:
  • dataTable (string): The name of the table that contains the data.  This will be translated into the store data.
  • dataTableId (string): The name of the field that contains the id of the data table.
  • totalRecords (string): The name of the table that contains the data count.  This parameter is optional.
Below is a sample store declaration.  Note that there is no record definition because it is created at run time. 
A big benefit to this is that the service can change and the grid will keep on working as it did before.  For large grids, you also save re-coding record definitions.

Sample Ext.net.Store declaration in JavaScript

var s = new Ext.net.Store({
net: {
     dataTable: 'data',
     dataTableId: 'id',
     totalRecords: 'count'
  remoteSort: true,
  sortInfo: { sort: 'fname', dir: 'ASC' },
  proxy: new Ext.data.HttpProxy({
     url: 'EmployeeServices.asmx/GetEmployees',
     method: 'GET'
  storeId: 'empStore'

The source code for Ext.net.Store and Ext.net.GroupingStore can be downloaded for free from http://extjsdata.codeplex.com/.  The code has been tested with Firefox 3.5+ and IE7+.  It was designed to be compatible with ExtJs Framework 2.2, but also works with 3.1.

16 March 2010

Bookmarklet: Add URL to Remember the Milk from the iPhone

I use Google Reader on all my mobile devices (currently an iPhone) and frequently find myself wanting to switch to the desktop to finish a story.  Google Reader has great desktop integration with other services to save articles, but is very limited on the iPhone.  Google Reader mobile allows you to email a link to yourself, but the whole process is painful and I get enough email.

I am also a heavy user of Remember the Milk as my task management system.  Wouldn't it be great to be able to add URL's directly from the iPhone?  Enter the RMilk iPhone Bookmarklet.

var a = [
'name=' + document.title,
'url=' + location.href,
location.href = "http://m.rememberthemilk.com/add?" + a.join("&");

The Bookmaklet above adds the URL into the mobile Remember the Milk site.  The following values are populated:

  • name: The name of the page you are viewing will be the task name.
  • url: The URL is the page you are viewing.
  • due: The due date can be any legal value that Remember the Milk can validate as a date.
  • priority: Legal priority values are 1, 2, 3, and 4 (none).
  • tags: Any tag (or tags) you would like to use to populate your task.
Additionally, there are two other options worth noting:
  • estimate: Any valid time estimate can be used 
  • list: The integer list id that applies to your list.  If you want the URL to go to a specific list, go to http://m.rememberthemilk.com/add.  View the page source and find the list of values that contains your list.  Use the value attribute from the option tag as your list value.
To add this bookmarklet to your iPhone, do the following:
  • Copy the JavaScript Bookmarklet above into Notepad.
  • Edit the values in the array as to how you would like to populate Remember The Milk.  Add estimate and list to the array if you would like those values populated.
  • Copy the new JavaScript into an email and email it to your iPhone.  Copy the JavaScript from the email.
  • Bookmark any page in Safari.  Edit the bookmark by giving it a new name and pasting the JavaScript into the URL textbox.

This post is heavily indebted to Steve Ollis, The IT Juggler.

19 February 2010

Google Chrome Is Now My Default Browser

Google Chrome has been updated for the Mac, which means I can use it on all platforms now (Mac / Linux / Windows). It has become my default browser and is now the Swiss Army Knife of my computer.

Following is my list of 'must have' extensions for Chrome:

One side effect of Google awesomeness is that Fluid (on my Mac) just became much less important.  If you haven't tried Google Chrome, or you haven't seen the extensions, I recommend taking a look.

12 February 2010

C#: Add history to an ACT! Group (and contacts)

My current project requires interaction with ACT!, both retrieving and setting data. 
Working with the ACT! framework is an interesting experience.  The code is very verbose and requires some expertise to understand.  And it took me a while to realize that the best way to retrieve data is through the database, while using the framework to perform the updates.
The following method writes an email history to the database.  It is useful for when you need to record automated processes to groups in the database. 
The method takes a GUID as an argument, primarily because there are no unique naming constraints in the group list.

using Act.Framework;
using Act.Framework.Contacts;
using Act.Framework.Groups;
using Act.Framework.Histories;
using Act.Framework.Lookups;
using Act.Shared.Collections;
// simplified connection for multiple methods
privateActFramework actFramework() {
    string cn = ConfigurationManager.ConnectionStrings["actDb"].ConnectionString;
    string login = ConfigurationManager.AppSettings["actLogin"].ToString();
    string pwd = ConfigurationManager.AppSettings["actPwd"].ToString();
ActFramework f = newActFramework();
f.LogOn(cn, login, pwd);
    return f;
// adds email history to the database
public void AddEmailGroupHistory(string GUID, string subject, string body) {
    // create a disposable connection to the database

    using(ActFramework f = actFramework()) {
    // create a new group GUID array with one element
Guid[] gGuid = new Guid[] { new Guid(GUID) };
    // get a group list - the list will contain one group
GroupList gList = f.Groups.GetGroupsByID(null, gGuid);
    // get a contact list from the first group in the list
ContactList cList = gList[0].GetContacts(null);
    // creates an all-zero GUID
Guid activityClearedId = Guid.Empty;
    // creates a system type of Email sent
HistoryType hType = new HistoryType(SystemHistoryType.EmailSent);
    // the history is public
    bool isPrivate = false;
    // start and emd (duration) are equal
DateTime startEnd = DateTime.Now;
    // Create a new history object
History h = f.Histories.CreateHistory(cList, gList, activityClearedId,
hType, isPrivate, startEnd, startEnd, subject, body);
    // commit the history to the database

02 February 2010

ExtJS Helper Functions - SelectionModel Modifications

One of the application patterns I implement in ExtJS is to have ViewPort containing a grid with records for a user to edit.  The user double-clicks a record on the grid, which opens a window to edit the information.  When complete, the user then closes the window and the grid refreshes.

The issue is that after the grid refreshes, the user loses her place on the grid as the selection goes back to the first record.

Additionally, the RowSelectionModel() only implements selection by index, which means that if there are concurrency checks, you cannot automatically go back to the index you were on or guarantee the new index will be correct.

These methods attempt to correct these shortcomings.  The first extension implements a selectById method for the RowSelectionModel object.

The second extension implements a SelectAfterLoad method on the GridPanel (and all of it's descending objects).  When closing the form, take the id from the record you are using and pass it into the selectAfterLoad method.  The method will kick off a single event to find the id in the current loaded record set.  If the id of the record is not found, the default 'selectFirstRow()' method is called.

// extend the RowSelectionModel
Ext.override(Ext.grid.RowSelectionModel, {

// enables the selection model to select by record id
selectById: function (id) {

var sm = this,               // hook to the selection model
      s = sm.grid.store,   // reference to the store
      f = false,                 // found (initialized as false)
      idx = 0;                   // record index

      // if store has no records, return control
      if (s.getCount() === 0) {

     // iterate through each record, looking for the id
     s.each(function (r) {
          // if the record id matches the passed id
          if (r.id.toString() === id.toString()) {
               // found is true
               f = true;
               // select the row
               sm.selectRow(idx, false);
               // return false to exit
               return !f;

          // iterate the index
          idx = idx + 1;
         // return true to continue iteration
         return !f;
           // if not false, exit
            if (!f) {
                // select the first row

// selects by record id after the grid reloads
Ext.override(Ext.grid.GridPanel, {

     // selects row by id after store loads
     selectAfterLoad: function (id) {

     // get the grid selection model
     var sm = this.getSelectionModel();

     // select record by passed id argument after store loads
               'load': {
                     fn: function (t, recs, opts) {
                      single: true

20 January 2010

ExtJS Helper Functions - GetValue / SetValue

This is my first (of several planned) functions to increase the functionality of the ExtJs functionality.

These methods extend the container object and make it easier to set and retrieve values from the controls within the container.  A great benefit is that the search will go into all the nested containers as well.

// Shortcut for adding methods to the prototype
Function.prototype.method = function (name, func) {
this.prototype[name] = func;
return this;

// Works on all containers (windows, formformpanels, etc.) and will
// even search subcontainers. The control must have an identifiable id
// and a setValue method.
// In this method c is the control id and v is the value to be set.
Ext.Container.method('setValue', function (c, v) {

// Works on all containers as above. The control must have an identifiable
// id  and a getValue method.
// In this method, c is the control id of the value to be retrieved.
Ext.Container.method('getValue', function (c) {
return this.findById(c).getValue();

Using these functions on the container saves a tremendous amount of code devoted to looking up and setting values. Just create a variable for the container and then use the getValue retrieve the value or setValue to set the value.

For instance, if you have a window object named 'win' and a textbox with an id of 'txtName', you can merely use:
win.setValue('txtName', 'Michael'); 
Doing so will set the TextBox value to Michael, even if it is deeply nested in a form panel and several columns.


01 January 2010

How to Turn Your iPod Touch into a SIP Phone with Nimbuzz and SIPGate

Many sites on the web that show how to make and receive phone calls on an iPod touch. But to anyone who has attempted it, you will quickly find that implementation is not fast or easy. The following shows how to make your iPod Touch into a SIP enabled phone using SIPGate. Note that these instructions will work with any phone that can load Nimbuzz (such as Nokia, Blackberry and Android) phones.

Get Nimbuzz software and SIPGate account
1. Get Nimbuzz. If you have an iPod touch, get it from the App Store. Otherwise, go to the Nimbuzz site and follow the download and installation and account setup instructions.

2. Get a SIPGate account at the site of your choice.  SIPGate is a German company, so European customers can get European numbers.  US phone numbers are available at http://www.sipgate.us/.  At this time, only California numbers are available.  SIPGate offers free accounts for one person (as of the time of this writing).

Configure Nimbuzz
Nimbuzz offers SIPGate as one of the native SIP integration options.  In my testing, this native integration does not work.  To make matters worse, the required SIP settings are hidden deep within SIPGate.  Here's how to configure Nimbuzz with SIPGate to make it work.  Please note, the instructions as follow are for the iPod Touch.  The UI for other phones may differ slightly or significantly.

1. Open Nimbuzz, choose Call (telephone icon) at the ribbon at the bottom of the screen.

2. Nimbuzz Out is the default provider.  You will see Nimbuzz Out        $0.00    > in the bar at the top of the screen.  Push the > to see the Nimbuzz call settings screen.  You will see NimbuzzOut, SkypeOut and SIP as options.  Push SIP to see a list of SIP/VoIP providers.

3. Go to the bottom of the list and select Other VoIP Account.  You will see Sipgate.DE/Sipgate.US as options - do not select either of these as they do not work.

4. Get your SIP username and password from the SIPGate website.   Note: Your SIPGate VoIP user name and password are not the same user name and password you use to log into the website.
  • Push Settings at the top-right corner of the SIPGate screen.
  • Click the left-most phone and choose SIP Credentials from the menu.
  • The SIP-ID is your user name and SIP-Password your password for connecting Nimbuzz to SIPGate.  The Domain and Proxy are sipgate.com (unless otherwise noted).
Once you have followed these steps, you should be able to make and receive calls from your Nimbuzz application without any problems.