ErrorCode: ‘-2147467259′ Description: ‘Cannot create new connection because in manual or distributed transaction mode.’  Source: ‘Microsoft OLE DB Provider for SQL Server’  SQL State: ”  NativeErrorCode: ‘0,

Sometimes the simple problems slow you down somewhat and it’s the time it takes searching for the information (that you know is out there) that takes the most of it.  Today was one of those days!

After searching on MSDN and other SQL related web sites I went back to the tried and tested Metastorm forum hosted by ProcessMapping and searched for the following string “distributed transaction mode”.

Lo and behold - one result - and the right one to boot!. 

The solution - run the script eWorkProcedures.sql found at:
C:\Program Files\Metastorm BPM\Engine\Database\SQL scripts\

It looks like I upgraded a test VM a while back but never quite completed the job.  I guess the moral of the story is check the Metastorm forums first, then look at other non-metastorm related sites.  It’s far easier that way!

After making changes to a client side script and and trying to debug I was getting puzzled why changes I’d made in the designer were not being delivered to the client. 

At first I though this was just a IE “feature” so I cleared out the temporary files and cookies but this did not solve the problem, however it did solve another issue where the web client filters were not working correctly. Out of chance I ran the iisreset command and this allowed the correct version to be delivered, however after the next code change the client again received the older script.

Next under the microscope was IIS, this I assumed must be caching the files on initial delivery. After tweaking a few things it appeared this was in the clear to.  Back to the drawing board and running iisreset at every change.

After leaving the issue for a while I was inspecting the DOM of a delivered page and noticed the client script delivery mechanism:

<script type="text/JScript" 

        language="JScript" 

        src="eScript.ashx?AttachmentFile=ClientScript.js&

             AttachmentOwner=map1&

             AttachmentType=Procedure&

             Service=Metastorm BPM Server&

             Ver=29"></script>

Notice the “Ver=29″ tag?  Now this means that the web client is looking for a specific version of the client side script however if you had turned off versioning in the designer after initially publishing the procedure without really knowing what it does it means that no matter how many times you subsequently publish any newer scripts will not be delivered to the client.

I’m not sure if this is a bug or what Metastorm would call a feature but it’s certainly not obvious and surely the web client should then not attempt to retrieve versioned client scripts?  Earlier in the day I’d unchecked many of the check boxes under the Metastorm Designer’s publisher options section to remove the annoying pop ups that you get when developing.  Looks like it was one checkbox too many.  Roll on version 7.6…

Hope this saves you some time if you also get caught by this “feature”.

Standard methods that can be overridden to allow full control of what happens when a user clicks an individual row

Full control of cell content - i.e. let us embed HTML objects such as links & images etc…

A few years back we developed standard routines for expanding and shrinking the size of a grid which also managed the layout alterations that were then necessary. Although the users liked this additional control it was only really useful when designing forms that are constrained by size and contained many fields - it didn’t really make the actual operation of filling in the form any easier.

During the end of 2007 we have been developing for a client where extra control of the grid would have made the user experience far simpler so it was time to delve deeper into what goes on inside.  A few hours of investigation and we’ve produced some client-side JScript extensions that covers our needs.  I’m sure they won’t be suitable for all situations due to the additional complexity but when you really need to do something special hopefully these routines may be able to help.

The example that follows is only suitable for read-only grids and although there are a few bits to follow we’re hopefully going to incorporate most of the hard work into a future release of the Oykr Framework - A Scripting framework that will be supported and will make some of the tips and tricks documented on this site more accessible.

The code here is based upon MBPM version 7.5 but I’m sure they could be adapted for most of not all of the web clients. We would never claim these are bullet proof but during extended testing everything is looking rosy!

The concept works by creating enhanced focus event handler and overriding the default onFocusEvent method through an initialisation call to the function Init() when the form has loaded.  We override this function as any enhancements made to a grid need to be re-applied whenever the grid is redrawn

To override the FocusCurrentRow() method of our grid, grdUserList we use: 

eworkData.FieldByName("grdUserList").FocusCurrentRow = myFocusCurrentRow;

Now that the focus event has been redirected to our new handler we need to re-create the functionality of the original handler and then add the additional steps we would like ran when a grid is re-drawn.

function myFocusCurrentRow()

{

  //STANDARD METASTORM FocusCurrentRow() CODE

  if ( (this.LastSelectedRow != -1) &&

       (this.LastSelectedPage == this.CurrentPageNo) &&

       (!UpdateInProgress))

  {

    var LCurrentRow = this.HTMLfield.rows(this.LastSelectedRow);

    this.ChangeRowColors(LCurrentRow,

                         SelectedRowBackgroundColor,

                         SelectedRowFontColor)

    if(!RowClicked)

    {

      if(LCurrentRow != null)

      {

        if( (LCurrentRow.offsetTop + LCurrentRow.clientHeight)<

                                  this.MainTable.clientHeight )

          this.MainTable.scrollTop = 0;

        else

          this.MainTable.scrollTop = LCurrentRow.offsetTop;

      }

    }

  }

  //STANDARD METASTORM FocusCurrentRow() CODE END

  //OUR CUSTOM PROCESSING START

  SetHtmlCellContent();

  //OUR CUSTOM PROCESSING END

}

SetHtmlCellContent() gives us a hook from the focus event to add in our enhancements.  In this example we’ll show how to add a hyperlink to a column.

In the function below we introduce 3 new function calls, disableGridControl, enableGridControl and eworkSetCellInnerHTML.  The first 2 functions allow us to block and re-enable further events from being processed as the standard functions eworkGetCell and eworkSetCell cause further focus events to be fired resulting in recursive events being raised.

SetHtmlCellContent() blocks events from being fired for the grid, and then for each record within the visible grid it will create a hyperlink element and insert it into the 3rd column (index 2). Finally it re-enables event processing.

function SetHtmlCellContent()

{

  //Set references

  var gridName  = "grdUserList";

  var COL_LINK  = 2;

  var tbl       = document.getElementById(gridName);

  //disable event handlers

  disableGridControl("grdUserList");

  //iterate over records

  for (var i=0; i<(tbl.rows.length-1); i++)

  {

    var a       = document.createElement('a');

    a.href      = "http://oykr.com/blog";

    a.target    = "_blank";

    a.innerText = "The Oykr Blog";

    eworkSetCellInnerHTML (gridName, COL_LINK, i, a.outerHTML);

  }

  //restore event handlers

  enableGridControl("grdUserList");

}

The function eworkSetCellInnerHTML is an slightly tweaked version of the Metastorm function eworkSetCell. It is exactly the same as the standard Metastorm version except where the normal function sets the InnerText value this function sets the InnerHTML allowing you extra control over the cells actual content. It then adds the cell index to the parent cell to allow standard functions to still be able to reference it correctly.

function eworkSetCellInnerHTML(AGridName, AColIndex, ARowIndex, AValue)

{

  var LGrid = eworkData.FieldByName(AGridName);

  AColIndex = parseInt(AColIndex);

  if(LGrid.AccessibilityOn)

      AColIndex++;

  ARowIndex = parseInt(ARowIndex);

  var LRow  = LGrid.HTMLfield.rows(ARowIndex + 1);

  if ( LRow != null )

  {

    var LCell = LRow.cells(AColIndex);

    if (LCell != null)

    {

      var LCurrentCell = LCell.children[0];

      var LCurColumn   = LGrid.ColumnByName(LCurrentCell.dataFld);

      if ( null != AValue )

      {

        //Our code to set the HTML value and set the cellindex value

        //as this isued by some of the standard functions

        LCurrentCell.innerHTML = AValue;

        LCurrentCell.cellIndex = AColIndex;

      }

    }

  }

}

And the final functions to enable and disable the grids events…

function disableGridControl(AGridName)

{

  eworkData.FieldByName(AGridName).XMLData.onrowenter   = null;

  eworkData.FieldByName(AGridName).XMLData.onrowexit    = null;

  eworkData.FieldByName(AGridName).HTMLfield.onkeydown  = null;

}

function enableGridControl(AGridName)

{

  eworkData.FieldByName(AGridName).XMLData.onrowenter   = ReadOnlyGridRowEnter;

  eworkData.FieldByName(AGridName).XMLData.onrowexit    = ReadOnlyGridRowExit;

  eworkData.FieldByName(AGridName).HTMLfield.onkeydown  = checkReadOnlyGridKey;

}

It’s quite a bit to get down on paper but most of the functions are standardized and in this example only SetHtmlCellContent() needs to be adapted to fit your exact needs.  As a secondary side effect - the function myFocusCurrentRow() also exposes the exact cell and value that was clicked so you can make scripted calls based upon exactly what the user has clicked in the grid.

Feel free to download the working example below complete with the complete and commented JavaScript.

Dowload zipped example : OGridExtensionDemo.zip

Normally this type of validation is carried out when a form is submitted and we can easily trap these conditions. Return false from a validation script and it will stop the submission and therefore the server side code, however if you try to do something similar from a command button it’s not so simple.

The routine method is to simply not allow the user the option of pressing the command button prior to all conditions being met - these conditions being calculated by a hidden check box (or series) and having the button’s visibility set dependent on these.  i.e. When the conditions are okay the button is visible and  when the conditions are not okay the button is not visible.  Personally, I don’t really like this as it doesn’t give any opportunity for feeding back to the user to show why things may be wrong. From a design perspective it also creates a more cluttered view in the MBPM designer which can then lead to maintenance issues as a system grows.  Also, one last minor and avoidable niggle, it can leave a “hole” in the form’s design.

I think it would be more beneficial to allow the the client-side script to perform processing and validation before deciding if it should hand control on to the server for server-side processing. Luckily this is quite simple to achieve.

To do this follow these 2 steps:

1. When the form loads associate a new event handler with the command button
2. Create the new event handler

When the form loads ensure it calls the initialisation function:

   1: OnLoad=Init ()&Language=JScript

Create the initialisation function:

   1: function Init()

   2: {

   3:     //hook the command button to it's own event handler to allow

   4:     //validation before the server side operation is triggered

   5:     eworkData.FieldByName("btnCmd1").OnClick = btnCmd1Validate;

   6: }

Now create the event handler:

   1: function btnCmd1Validate()

   2: {

   3:     try

   4:     {

   5:         var RESULT = doComplexValidation();

   6: 

   7:         if (RESULT==true)

   8:         {

   9:             ButtonClick(document.getElementById("btnCmd1"));

  10:             alert("Server-side functionality processed");

  11:         }

  12:         else

  13:         {

  14:             alert("Server-side functionality NOT processed");

  15:         }

  16:     }

  17:     catch (ex)

  18:     {

  19:         alert (ex.message);

  20:     }

  21: }

Now when the the command button (btnCmd1) is clicked the new event handler is used.  This allows the function doComplexValidation() to be called which determines if we carry out further processing. If control should be passed back to the server then we call the Metastorm function ButtonClick() passing the command button element itself as the argument to invoke the server-side.

This gives the opportunity for full validation, additional opportunities for user feedback and the potential for taking pre-emptive corrective action should you need to.

If this works thanks are due to Dean Lee and his WordPress syntax highlighter.

Some of the first things we want to cover in the coming months include grid extensions, using rich text editors, building tag cloud functionality and more…

If nothing else it will be our bucket for tried and tested techniques that can be re-used where required…

We hope it’s useful to others too.

Cheers,

Paul.