Short solution for short problems

Daily WSS: Web services

Web services

WSS 3.0 exposes the whole functionality of the site through web services making it very easy for 3rd party application to connect to the platform. You can manage documents, list, etc, via these web services. This can come is very useful to publish automatic generated reports. You can use the SharePoint search query to find certain documents or list items. The web services are better explained in the Microsoft SDK: http://msdn2.microsoft.com/en-us/library/ms441339.aspx Here an overview of the available web services with a short description.

Microsoft Windows SharePoint Services Web services provide methods that you can use to work remotely with a deployment of Windows SharePoint Services. The following table lists and describes the available Web services.




Provides methods for managing a deployment of Windows SharePoint Services, such as for creating or deleting sites.


Provides methods for working with alerts for list items in a SharePoint site


Provides classes for logging on to a SharePoint site that is using forms-based authentication.


Provides methods for copying items between locations in Windows SharePoint Services.

Document Workspace

Provides methods for managing Document Workspace sites and the data they contain.


Provides methods for returning forms used in the user interface when working with the contents of a list.


Provides methods that enable you to create and manage picture libraries.

List Data Retrieval

Provides a method for performing queries against lists in Windows SharePoint Services.


Provides methods for working with lists and list data.


Provides methods that enable you to create and manage Meeting Workspace sites.


Provides methods for working with security groups.


Provides methods for working with the permissions for a site or list.

SharePoint Directory Management Service

Provides methods for remotely managing distribution groups.

Site Data

Provides methods that return metadata or list data from sites or lists in Windows SharePoint Services.


Provides a method for returning information about the site templates for a site collection.


Provides methods for remotely performing searches within a Windows SharePoint Services deployment.

Users and Groups

Provides methods for working with users, site groups, and cross-site groups.


Provides methods for working with file versions.


Provides methods for working with views of lists.

Web Part Pages

Provides methods to send and retrieve Web Part information to and from Web services.


Provides methods for working with sites and subsites.

Daily WSS: Sites


An administrator or a user with sufficient rights has the possibility to create new sub-sites. He can choose a template and colour theme provided by the organization. You can create for example sub sites for each project. These sites are created from a template and contain automatically the correct layout and content. The sites can have different security then their mother site or children.
There is a possibility to save the site as template, with or without content.
It is possible to create these sites with web services, hence an external application can create these sites. From your site, you could create a new sub site when a new project is launched. Each site has a search functionality that can search the complete content of the site, including the content of the documents. Site content is created with web parts. You can have different web parts, HTML content, pictures list, document library web part (a view on the content of the library), custom web parts, etc.

Daily WSS: Content types

Content Types

Content types are types created by your organization. They can describe a document type or list item. A content type has its own workflow and specific metadata. These types can be reused throughout the entire site and are managed at site level.

For example: a financial form, it has a workflow that sends an email to the financial assistant, and has extra fields like department, total cost … When somebody creates a document of this type; it inherits these properties and activates the workflow.
You can still add extra metadata or specific workflows on items.

Daily WSS: Lists


Lists in SharePoint can contain almost all types of data. You can add multiple attachments to list items; you can add metadata and calculated fields to the list. Metadata can be one of the following types: Single line of text, multiple lines of text, choice, number, currency, date and time, lookup (values in a list or library on the site), yes/no, person or group, hyperlink or picture, calculated (you can perform many functions on fields and show the result in this column).
It is possible to add different content types to the list, multiple workflows and in dept security… A list can be a very powerful source of information.
You can look at a list like a database table with a web interface and much more functionality. You can create these lists through a web interface and manage them. You can add columns at any time to the list, change views and order. The content is also completely searchable.
It’s possible to receive list content or notifications on your mobile.

Daily WSS: Document libraries

Document libraries

These libraries are specially created to store documents in a central place and provide lots of default functionality on these documents.
You can upload or create new documents to a library using the web interface or your windows explorer. That’s right; SharePoint enables you to control documents from your windows explorer, office application or the World Wide Web. You can even add documents to your library by email! There is a possibility to work with different content types in the same library, content types and their strength is explained later on this blog.

You can secure documents, folders and libraries on a different level. You can add workflows to libraries or content types.
WSS 3.0 offers publishing on documents. You can use version history with major and minor versions. The history of documents is kept by the system if we enable versions; therefore it is really easy to rollback previous versions. When multiple users are working on the same documents you can use the check in, check out functionality.

You can create and edit documents using your appropriate office application. When you open the document, the system will choose the correct office application and lets you chose whether to check out or not. You can even change the metadata of the document within the office application of you are using Office 2007.
Content of the documents is crawled by the system. This enables you to perform a search based on the contents of the documents.
It’s possible to receive content or notifications on your mobile.

Google AdSense

As you can see on the right section of this site, I'm being sponsored by Google AdSense. This will not make me rich, in fact it's only for testing purpouse. But if you're site is a popular site or is going to be, it is a very good idea to use this functionality. It is really easy to use, and the only requirement is that you have a gmail account. There are 3 main possibilities of add's:

  1. 1 Content add's : adds that are shown are based upon the content of your site, you can use banners horizontally of vertically.
  2. 2 Search add's: a search box that saerches your site and adds add's to the search results
  3. 3 Pointers: to send users to specific products or services.


You can start using AdSense here!

Feel free to click on the add's on this site and make some money, I recently clicked on one add and got some valuable information, so don't think it's all spam or rip off, it are good products that has to sell themselves!

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