Short solution for short problems

Wait cursor for Windows forms

I think that every good developer has to be aware not to invent the wheel again and again. I found a great opensource project that helps window forms applications build in a wait cursor every time the system is doing some work.

You will need to reference the dll in your project and put these 2 lines somewhere in the startup of your application:

ApplicationWaitCursor.Cursor = Cursors.WaitCursor;

That’s it!

ApplicationWaitCursor.Delay = new TimeSpan(0, 0, 0, 1, 0);  // Delay of 1 second
ApplicationWaitCursor.Delay = new TimeSpan(0, 0, 0, 0, 250);  // Delay of 250 mili seconds

You can find a demo project and the source code here: http://www.codeproject.com/cs/miscctrl/WaitCursor.asp

I used it myself and must admit that this code works really fine. You can see that the developer has put some effort in it! 

I only notice one small problem during testing. When I clicked on a scroll bar in a listbox, and hold the mouse button down for a while to scroll, the wait cursor appeared.

You can filter events, below I adapted the ApplicationWaitCursor.cs and added the WM_LBUTTONDOWN message. I declared the varible : private const int WM_LBUTTONDOWN = 0x0201; on top and added this code:||
m.Msg == WM_LBUTTONDOWN to the if in the MessageFilter.PreFilterMessage method. Now when I left click on the mouse and keep it down, the wait cursor is not shown. But now every time whe do a left mouse click, the wait cursor will not be shown. So I decided to leave it this way and set the delay at 500 ms. Apart from this small problem, the application is now much user friendly because every time the system is doing some work, the wait cursor informs this to the user.

Here are some pre filtered messages I pickup up from comments on the post:

bool IMessageFilter.PreFilterMessage(ref Message m)
if (
m.Msg == WM_RBUTTONUP || 
m.Msg == WM_SYSKEYUP ||
_cursor.Enabled = false;
...Message defitions are:
private const int WM_LBUTTONDOWN = 0x0201;
private const int WM_RBUTTONUP = 0x0205;
private const int WM_CONTEXTMENU = 0x007B;
private const int WM_SYSKEYUP = 0x0105;
private const int WM_SYSKEYDOWN = 0x0104
The list of all mouse events: http://msdn2.microsoft.com/en-us/library/ms674824.aspx


Google fan

As a huge Google fan, I try to be updated about the latest new features Google presents.

An agenda with free sms notification makes my life a lot more easy. You know when use try to use an agenda, but still you forget to look in it at the most important times. You forget sometimes an appointment with the dentist. Not anymore. Google offers free sms notification.

Also Picasa has a lot to offer. You can store 2 Gig photo's on the web, if you make the quality web-based you can put you're whole life in there. You can map your photo's, so you can see with a google map where the picture was taken. You can also use an embedded flash tool to show your pictures everywhere:

And don't get me started on iGoogle or google analytics, whow, this is really crazy.

Google also lets you keep your web history. A few days ago I was looking in my mail for a aknowledgement about something I ordered, but I could't find it. I forgot which url. I was a little desperate, because I paid with credit card, but didn't recieved a mail to track the order. I ended up in my google web history and after a while I founded the search text I used to find the website. And I got my order back.

SQL CE, minimize the database size!

Before you start working on a SQL CE application, you should realize that there is more and thorough analysing needed. Because size is almost always a problem, we have to understand the importance of correctly settings up the database.What you should keep in mind is the following:

* bigint Integer (whole number) data from -2^63 (- 9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807). The storage size is 8 bytes.

* integer Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). The storage size is 4 bytes. 

* smallint Integer data from –32,768 to 32,767. The storage size is 2 bytes.

* tinyint Integer data from 0 to 255. The storage size is 1 byte.

* bit Integer data with either a 1 or 0 value.

* numeric (p, s) Fixed-precision and scale-numeric data from -10^38 +1 through 10^38 ?1. The p specifies precision and can vary between 1 and 38. The s specifies scale and can vary between 0 and p. Numeric always uses 19 bytes, regardless of its precision and scale. 

* money Monetary data values from -2^63 (- 922,337,203,685,477.5808) through 2^63 - 1 (922,337,203,685,477.5807), with an accuracy to a ten-thousandth of a monetary unit. The storage size is 8 bytes.

* float Floating point number data from -1.79E + 308 through 1.79E + 308 The storage size is 8 bytes.

* real Floating precision number data from -3.40E + 38 through 3.40E + 38.

* datetime Date and time data from January 1, 1753 to December 31, 9999 with an accuracy of one three-hundredth of one second or 3.33 milliseconds. Values are rounded to increments of .000, .003 or .007 milliseconds. Stored as two 4-byte integers. The first 4 bytes store the number of days before or after the base date (January 1, 1900). The base date is the system's reference date. Values for datetime that are earlier than January 1, 1753 are not permitted. The other 4 bytes store the time of day as the number of milliseconds after midnight. Seconds have a valid range from 0 through 59.  

* national character(n) Synonym: nchar(n) Fixed-length Unicode data with a maximum length of 255 characters. The default length is 1. The storage size, in bytes, is two times the number of characters entered.

* national character varying(n) Synonym: nvarchar(n) Variable-length Unicode data with a length of 1 to 255 characters. The default length is 1. The storage size, in bytes, is two times the number of characters entered.

* ntext Variable-length Unicode data with a maximum length of (2^30 - 2) / 2 (536,870,911) characters. The storage size, in bytes, is two times the number of characters entered.

* binary(n) Fixed-length binary data with a maximum length of 510 bytes. The default length is 1.

* varbinary(n) Variable-length binary data with a maximum length of 510 bytes. The default length is 1.

* image Variable-length binary data with a maximum length of 2^30 – 1 (1,073,741,823) bytes.

* uniqueidentifier A GUID. Storage size is 16 bytes.

* IDENTITY [(s, i)] This is a property of a data column, not a distinct data type. Only data columns of the integer data types can be used for identity columns. A table can have only one identity column. A seed and increment can be specified, and the column cannot be updated. s (seed) = starting value i (increment) = increment value. 

* ROWGUIDCOL This is a property of a data column, not a distinct data type. This is a column in a table that is defined by using the uniqueidentifier data type. A table can only have one ROWGUIDCOL column.  

So if you add a column creationdate, but it is not needed by the system nor for reporting. Then you should consider deleting it. Keep in mind that for one record it is 4 bytes, but for 1000000 records it needs 4000000bytes or 3.8 mb.

In an application we used Netpad from psion teklogix with a fixed memory of 40 mb. Only 5 Mb is calculated for the DB. We don’t store million of records, but we do store ten thousands of records and a leaving out a datetime in a table could spare us 0.38 mb.

In case of synchronizing with an other DB, be sure to write all data to the mother DB and do not keep unnecessary data on the netpad.

When you estimate the size of a table or of a database, consider the following information:


Each row in the table has an overhead of 6 bytes.


Each column in the table has an overhead of 1 byte, plus 1 byte for every 256 bytes of row storage.


Fixed-length data types have an overhead of 1 byte for each column, and the overhead is rounded to the next higher byte.


Zero-length string columns occupy 1 byte in the row.

I understand that with the uprizing flash memory, capacities will grow larger, but so will systems become more complex and in need of more storage, therefore it always will be a good practise to try to minimize the size of the databases.

I’ll write more on this subject in a coming post. I’m currently optimizing a SQL Ce database.

source: http://support.microsoft.com/kb/827968

Find strings in Excel sheet

How can we look up a string in an Excel sheet? With normal Excel code it is easy using the FIND function, but when you want to use a VBA function on all results of the find operation, you'll need a custom function:

Function FindAll(SearchRange As Range, FindWhat As Variant, _
    Optional LookIn As XlFindLookIn = xlValues, Optional LookAt As XlLookAt = xlWhole, _
    Optional SearchOrder As XlSearchOrder = xlByRows, _
    Optional MatchCase As Boolean = False) As Range
' FindAll
' This returns a Range object that contains all the cells in SearchRange in which FindWhat
' was found. The parameters to the function have the same meaning as they do for the
' Find method of the Range object. If no cells were found, the result of this function
' is Nothing.
Dim FoundCell As Range
Dim FoundCells As Range
Dim LastCell As Range
Dim FirstAddr As String
With SearchRange
    ' In order to have Find search for the FindWhat value
    ' starting at the first cell in the SearchRange, we
    ' have to find the last cell in SearchRange and use
    ' that as the cell after which the Find will search.
    Set LastCell = .Cells(.Cells.Count)
End With
' Do the initial Find. If we don't find FindWhat in the first Find,
' we won't even go into the code which searches for subsequent
' occurances.
Set FoundCell = SearchRange.Find(what:=FindWhat, after:=LastCell, _
    LookIn:=LookIn, LookAt:=LookAt, SearchOrder:=SearchOrder, MatchCase:=MatchCase)
If Not FoundCell Is Nothing Then
    ' Set the FoundCells range
    ' to the first FoundCell.
    Set FoundCells = FoundCell
    ' FirstAddr will contain the
    ' address of the first found
    ' cell. We test each FoundCell
    ' to this address to prevent
    ' the Find from looping back
    ' through the range it has
    ' already searched.
    FirstAddr = FoundCell.Address
        ' Loop calling FindNext until
        ' FoundCell is nothing or
        ' we wrap around the first
        ' found cell (address is in
        ' FirstAddr).
        Set FoundCells = Application.Union(FoundCells, FoundCell)
        Set FoundCell = SearchRange.FindNext(after:=FoundCell)
    Loop Until (FoundCell Is Nothing) Or (FoundCell.Address = FirstAddr)
End If
' Return the result.
If FoundCells Is Nothing Then
    Set FindAll = Nothing
    Set FindAll = FoundCells
End If
End Function

This function returns an array of ranges containing the ranges where the searched string was in. After you can perform operations on the result values like this:

Private Sub FindAndDeleteCells()
    Dim SearchRange As Range
    Dim FoundCells As Range
    Dim FoundCell As Range
    Dim FindWhat As Variant
    Dim MatchCase As Boolean
    Dim LookIn As XlFindLookIn
    Dim LookAt As XlLookAt
    Dim SearchOrder As XlSearchOrder
    ' Set the variables to the
    ' appropriate values.
    Set SearchRange = ThisWorkbook.Worksheets(1).Range("A1:D20")
    FindWhat = "Large"
    LookIn = xlValues
    LookAt = xlPart
    SearchOrder = xlByRows
    MatchCase = False
    ' Search the range.
    Set FoundCells = FindAll(SearchRange:=SearchRange, FindWhat:=FindWhat, _
        LookIn:=LookIn, LookAt:=LookAt, SearchOrder:=SearchOrder, MatchCase:=MatchCase)
    ' Display the results.
    If FoundCells Is Nothing Then
        Debug.Print "No cells found."
        For Each FoundCell In FoundCells.Cells
            ' Now we delete the cell and the 3 cells on the right
            FoundCell.Offset(0, 1).Activate
            FoundCell.Offset(0, 1).Clear
            FoundCell.Offset(0, 2).Clear
            FoundCell.Offset(0, 3).Clear
            'Delete the Cells
            ' If you only want ot delete the cells, then use this code:
            'Delete the entire row
            'If you want to delete the entire row then use this code:
            Dim i As Long
            For i = FoundCell.Rows.Count To 1 Step -1
            Next i
        Next FoundCell
    End If
End Sub

Export data from Lotus Notes to Excel

The following code lets you choose a view and all the data from the view is exported to Excel automatically.
Very useful and completely free!

Place this code as an Agent in Lotus Notes Designer, and run the code from the Actions-menu.

Sub Initialize
      'Create an Excel Spreadsheet from any view
     '11/3/2000 Art Yates
 Dim Session As New NotesSession ,db As NotesDatabase
 Dim sourceview As NotesView,sourcedoc As NotesDocument
 Dim dataview As NotesView, dc As NotesDocumentCollection
 Dim datadoc As NotesDocument, maxcols As Integer
 Dim WS As New Notesuiworkspace
 Dim ViewString As String, Scope As String, GetField As Variant
 Dim C As NotesViewColumn, FieldName As String, K As Integer,N As Integer
 Dim xlApp As Variant, xlsheet As Variant, rows As Integer, cols As Integer
 Dim nitem As NotesItem , entry  As NotesViewEntry, vwNav As NotesViewNavigator
 Dim ShowView()  As Variant, i As Integer, VList As Variant, ColVals As Variant
 Set db = session.CurrentDatabase   'link to current database
 'fetch then display a list of views in the database
 Vlist= db.views
 K=Ubound(Vlist)  'get size of list
 Redim Preserve ShowView(K)
 For i = 0 To K
  If Len(Vlist(i).Name) >0 Then 
   If Mid(Fieldname,1,1) <>"(" Then  'do not show hidden views
    ShowView(N) = FieldName
   End If
  End If 
 Next i 
 Redim Preserve ShowView(N)
     'now sort the list - by default views are listing in the order that they were created
 For i=0 To N
  For K=i To N
   If  ShowView(i) > ShowView(k) Then
    ShowView(i) = ShowView(k)
   End If
  Next k
 Next i 
 viewstring= ws.Prompt(PROMPT_OKCANCELLIST,"List of Views","Choose a View","",ShowView )
 If Len(viewstring)=0 Then Exit Sub
  'ViewString ="Dan's View"
 Set dataview = db.getview(ViewString)  'get selected view
 Set vwnav= dataview.createViewnav()
 rows = 1
 cols = 1
 maxcols=dataview.ColumnCount  'how many columns?
 Set xlApp = CreateObject("Excel.Application")  
'start Excel with OLE Automation
 xlApp.StatusBar = "Creating WorkSheet. Please be patient..."
 xlApp.Visible = True
 xlApp.ReferenceStyle = 2
 Set xlsheet = xlApp.Workbooks(1).Worksheets(1)  
 'select first worksheet
 'worksheet title
 xlsheet.Cells(rows,cols).Value ="View: " + ViewString + ", from Database: " 
+  db.title +",  Extract created on: " +  Format(Now,"mm/dd/yyyy HH:MM")
 xlApp.StatusBar = "Creating Column Heading. Please be patient..."
 rows=2  'column headings starts in row 2
 For K=1 To maxcols
  Set c=dataview.columns(K-1)
  xlsheet.Cells(rows,cols).Value = c.title
  cols = cols + 1
 Next K
 Set entry=vwnav.GetFirstDocument
 rows=3   'data starts in third row
 Do While Not (entry Is Nothing)
  For cols=1 To maxcols 
   colvals=entry.ColumnValues(cols-1) 'subscript =0
   Select Case scope
   Case "STRING"
    xlsheet.Cells(rows,cols).Value ="'" +  colvals
   Case Else 
    xlsheet.Cells(rows,cols).Value = colvals
   End Select   
  Next cols  
  xlApp.StatusBar = "Importing Notes Data   -    Document " & (rows-1) 
  Set entry = vwnav.getnextdocument(entry)  
 xlApp.Selection.Font.Bold = True
 xlApp.Selection.Font.Underline = True
 xlApp.Range(xlsheet.Cells(2,1), xlsheet.Cells(rows,maxcols)).Select
 xlApp.Selection.Font.Name = "Arial"
 xlApp.Selection.Font.Size = 9
 With xlApp.Worksheets(1)
  .PageSetup.Orientation = 2
  .PageSetup.centerheader = "Report - Confidential"
  .Pagesetup.RightFooter = "Page &P" & Chr$(13) & "Date: &D"
  .Pagesetup.CenterFooter = ""
 End With
 xlApp.ReferenceStyle = 1
 xlApp.StatusBar = "Importing Data from Lotus Notes Application was Completed."
 'xlapp.ActiveWorkbook.saveas "c:VX" + Trim(Format(Now,"yyy"))   'save with generated name
 Set xlapp=Nothing   'stop OLE
 Set db=Nothing
End Sub 

Reference calculated fields

 When we use advanced functions, sometimes we need to reference one calculated field to an other. In Sharepoint this is possible.
And when we accidently make circular references we get this wonderful error:

The formula contains a circular reference (the calculated column refers to itself).  You must remove or change the circular reference.


Here is an example of a formula based on an other calculated field:



This fomula checks if the field contains a "\", and if it does, it takes the substring on the right of the "\",
else it displays and empty string: "".

You can find everything on functions that you can use in calculated fields on: http://office.microsoft.com/en-us/sharepointtechnology/HA011609471033.aspx

Using advanced functions in calculated fields

Recently I had a nice challange in Sharepiont 2007.
I had to group a view by a field, but not just group it on the text but on part of the value.
Eg. The field contained a value like this: A chapter\a subchapter.
The view had to be grouped by chapter and secondly by subchapter.This is the simple solution to the problem: 

I created 2 new calculated fields in the sharepoint list.
The first field called chapter, the second subchapter.
The chapter field had to contain the part of the title field before the "\". And if there was no "\" in the text,
then it had to be the complete string. This is the code I used to create this field: 



First we check if the "\" is present in the string, if not we just use title as it is. If "\" is present, then we take the left of the "\".

Here are some examples:

A testing code\b subtest 2     --> becomes: A testing code

A testing code                      --> becomes: A testing code


The second field had to contain the part of the string after the "\" and in case there was no "\" it had to be empty. Here the code I used:



First we check if the "\" is present in the string, if not we return "". If "\" is present, then we take the right of the "\".

Here are some examples:

A testing code\b subtest 2     --> becomes: b subtest 2

A testing code                      --> becomes:


Then I grouped the view on these 2 fields. 

Since I'm a programmer myself, I know that examples are the best way to learn a code. So here's one more.

This time I had to get some values out of a text field called Soort with this format: text1\text2\text3 .
I had to show text2 and text3 in different columns. The first calculated field called Project contains the string text2.
The code to get text2 out of the string text1\text2\text3 is the following:



So this code return: text2. The next calculated field called Subproject had to contain the string that is in place of text3.
Since Sharepoint code does not have the possibility to split or use loops we had to come up with the following code:

=RIGHT(RIGHT(Soort,LEN(Soort) - FIND("\",Soort)),LEN(RIGHT(Soort,LEN(Soort)-FIND("\",Soort))) - 
FIND("\",(RIGHT(Soort,LEN(Soort) - FIND("\",Soort))))) 


I have not reviewed these functions, I just know that they work.
Maybe it is possible to get the results in an easier way. Feel free to share it with us.


Using substring in a column in Sharepoint

A client asked me if we could group items in a list on a part of a textcolumn.

The text in the column looked like this:

categories per pc/ 106.032
categories per pc/ 106.033

I was supposed to make a view that showed that was grouped by pc name, only the pc name was not to be found seperate in any column. Instead it was a piece of the text in an existing field.It was the last 7 characters of the column.

So I googled and quickly and ended up on this website: http://office.microsoft.com/en-us/sharepointtechnology/HA011609471033.aspx

There are all functions and operators on fomulas documented. I created a calculated field called PC and inserted this funcion:


In named this new field PC and grouped my view on this field. The possibilities in calculated fields have more possibilities then I thaught.  

Redirecting pages with html

If you are looking for code to redirect your site to another site or subfolder, here it is: 

This script leads the old index page to the new web site.

	<script language="JavaScript">window.location="http://www.newwebsite.be/"; </script>


It is also possible to reload the page after some time as e new page.

Use this script:

	<meta http-equiv="REFRESH" content="10;URL=newpage.html"> 

Workflow in Moss 2007

Workflow possibilities with Microsoft



Microsoft offers default 3 possibilities:

VS 2005, Sharepoint designer and out of the box solutions. 

v     Out of the box solutions: By default there are 5 workflows present after installation of MOSS: Approval, Collect Feedback, Collect Signatures, Disposition Approval, Translation Management.

v     Sharepoint Designer: This graphical design allows you to create different actions. This can be used by web designers, there's no need to posses any .Net knowledge.

v     VS 2005: The possibilities in VS 2005 are huge. You can accomplish almost everything, but it has to be developed. These workflows are usable throughout the entire portal. When it is not possible with Sharepoint designer, we have to use VS .net 2005. You'll need in dept .Net and VS2005 knowledge to create workflows.

Comparing Workflow Development Processes

The figure below illustrates the various steps that need to be performed to create, deploy, associate, and run a workflow using each of the authoring tools. In general, the largest difference between the two tools is this:

v   Workflow authoring in the Visual Studio 2005 Designer for Windows Workflow Foundation is performed by a professional developer, who is creating a workflow template that can be deployed across multiple sites, and contains custom code and activities. The developer then turns the workflow template over to a server administrator for actual deployment and association.  

v   Workflow authoring in Office SharePoint Designer 2007 is done by someone other than a professional developer, such as a web designer or knowledge worker, who wants to create a workflow for a specific list or document library. In this case, the designer is limited to the workflow activities on their ‘safe list’, and the workflow cannot include custom code. The workflow author deploys the workflow template directly to the list or document library as part of the workflow authoring process.


For a more detailed comparison of the capabilities and advantages of each tool, see Workflow Development Tools Comparison.

Extra info: