Creating Microsoft Outlook 2007 Add-Ins with Visual Studio 2008

From the 2008 version of Visual Studio, VSTO (Visual Studio Tools For Office) was included as started in all versions from Professional upwards. Previously it had been a separate add-in, with limited functionality in the pro version of Visual Studio.

To create an add in for Outlook 2007;
1) Oen visual studio 2008, go to File\New Project
2) From the project tree Choose Visual Basic\Office 2007\Outlook Add In

3)

4) To add an event handler, select Application from the drop down on the left, which lists elements of the class. Then select the event from the event list on the right.

5) Take as an example, ItemSend event

6) Add some code to the item event

7) Run the project in the usual Visual Studio way, Outlook will start, and the add in will be installed, so if you try to send an email message, your event will fire.

8) One great thing here is that you can set breakpoints within the code in Visual Studio, and do the usual debugging, while events are being fired in Outlook. This is an advance over the VBA methods of older versions of Outlook.

9) To remove the add-in from Outlook during the development stage: in Visual Studio Debug: Clean

Janus GridEx - Adding a button Column

When using a Janus GridEx control it can be useful to have a column with a button in it.


This is an example of how to add a button column to a grid:


            Dim ButtonCol As New Janus.Windows.GridEX.GridEXColumn
            ButtonCol.Key = "Add" 'This is the way to identify the column when it is clicked
            ButtonCol.ButtonText = "Add" 'the caption on the button
            ButtonCol.ButtonDisplayMode = CellButtonDisplayMode.Always 'The button is visible even if the row is not selected
            ButtonCol.ButtonStyle = ButtonStyle.ButtonCell
            ButtonCol.Width = 40
            GridEX1.RootTable.Columns.Add(ButtonCol)




And, how to respond to the button click event:



    Private Sub GridEX1_ColumnButtonClick(ByVal sender As Object, ByVal e As Janus.Windows.GridEX.ColumnActionEventArgs) Handles GridEX1.ColumnButtonClick

        If e.Column.Key = "Add" Then 'Add should be replaced with the key to the button column


'To pick up the value of a specific cell in the current row, for example to get the record ID


 CInt(GridEX1.GetRow(GridEX1.Row).Cells("Id").Text)

'put handling code here
        End If

    End Sub

Error Handling in SQL Server 2005, obtaining Error Message

One of the features that came into SQL 2005 was the use of Try..Catch blocks.
It is good to be able to elegantly deal with errors, but usually as a developer, you still want to know that an error has occurred, and what it is. In .Net there are exception objects you can analyse. SQL Server is different, but it is still possible to access information about the error condition.
This syntax can be used in stored procedures, but sadly not in functions, which don't support try..catch blocks in SQL Server 2005.


BEGIN TRY
          STATEMENT BLOCK
END TRY
BEGIN CATCH
           READ THE ERROR, IF RUNNING THE SPROC IN A QUERY WINDOW, YOU WILL BE 
           ABLE TO SEE THE ERROR IN THE MESSAGES WINDOW


SELECT ERROR_NUMBER() ERNumber,
         ERROR_SEVERITY() Error_Severity,
         ERROR_STATE() Error_State,
         ERROR_PROCEDURE() Error_Procedure,
         ERROR_LINE() Error_Line,
         ERROR_MESSAGE() Error_Message

PUT ERROR HANDLING CODE HERE




END CATCH

Check if Global Temp Table Already Exists in SQL Server 2005

One problem with using Global temp tables in SQL Server, (those indicated by a double hash symbol at the beginning of their name), is that a procedure fails to remove them, the next time they are created you will get an error because they already exists.

To test for their existence before creating them in SQL Server 2005 I used this code, replace ##temp with your table name of course


IF NOT EXISTS(select * from tempdb.sys.sysobjects where name = '##Temp')
BEGIN
                       code to create ##temp
        END
        ELSE
        BEGIN
                      truncate table ##temp
        END

Virtual PC Network Settings With Wireless Adapter

I created a Windows XP virtual PC in Microsoft Windows Virtual PC 2007 and needed to get it to connect to the internet for updates and authentication.

The host PC was connected to the internet via a wireless connection.

This are the steps I took to get this working successfully.
In the virtual machine settings, in the Networking section, I selected the wireless adapter from the list.
See screenshot

Then on the virtual machine I went to Control Panel, networking, and configured the local connection settings to match those in the host machine. Since I was using manually assigned IP addresses I made sure the ip address itself was the same as the host, but everything else, matched the host's configuration.

The virtual machine could now communicate over the internet.

To connect to a Windows Domain, make sure the DNS address in the network adapter properties is the IP address of the Domain Controller. I have found that having an alternate DNS address here, even as the number 2 can prevent the VM joining the domain.

"" is not a valid login error message when installing SQL Server 2008 Express

When trying to install MS SQL Server Express 2008 onto a Windows XP machine we received the following error message during the later stages of installation.

The message appeared late during the installation, at the stage where it asked which account to run the various SQL services under. The solution was odd, the server name and the user name were the same. Changing either caused the problem to disappear.

Decompiling VB.Net Applications

I recently had two situations where I needed to decompile vb.net applications. One was for a client who had an application that could not be supported any longer, and need a fix, there other was for a version of something I had written and I no longer had the original code.

I tried a number of tools, including one from Red Gate.
Red Gates was not the easiest to use, and does not recover form design, but it was good value at $59.

To decompile an executable using Red Gate Net Reflector Pro
Once installed open Visual Studio
There is a new main menu item .Net Reflector
1) Choose Debug an Executable
2) Browse to the Exectuable you wish to decombile
3) The executable will start in a sepecial debug mode
4) Choose .Net Reflector\Choose Assemblies to Debug (a screen like the one below will appear)

5) A list will appear in Visual Studio of available decompiled assemblies

6) Right click on a item and choose Go to Decompiled Definition

Others I tried included
http://www.decompiler-vb.net/register.aspx

SSIS Packages - SQL Server Agent Jobs, Credentials and Proxies

SSIS Packages and Credentials


One issue with SSIS packages is that they run fine when executed manually, but when scheduled as a SQL Agent job, they do not run, or have problems.


This can be because the SQL Agent runs jobs under its own account by default and may not have the same permissions as the account used when they are created and executed manually.


The answer is to use credentials and proxies to impersonate an account with the correct permissions.


So having tested that the SSIS package works correctly when run manually under a certain account do the following: 

1. SQL server Management Studio. 

Security - Credentials- Create New Credential.

Give it a name. 

Fill identity column with domain\account used to test package successfully.  

Password of that account

2. SQLServerAgent-Proxies. 

Create a new proxy. 

Give it a name. 

Credential should be the one which you created previously in step 1

In subsystems. Check Sql Server Integration Servive Package.

3. Now when you create a job step, in the Run As box, change it from 'SQL Agent Service account' to the proxy created in Step 2.

Example:

SSIS Package Checkpoints

Sometimes a package will fail part way through, and it can be a problem to execute the whole package again. It is possible to edit the package and execute the individual components, but this may not be desirable if there are many tasks within the package.

SSIS has a feature which allows the package to resume from the point at which it failed, called checkpoints.

To get check points to work there are four steps to take:

The first 3 involve the package properties.

1) Set a checkpoint file name

2) Set checkpoint usage to IfExists

3) Set savecheckpoints to True

Then, for each of the tasks that you would wish to be able to resume from, edit their properties and set Fail Package on Error to true.

The next time the package is run, a checkpoint file is created. If the package completes successfully, the checkpoint file is deleted. However, if there is a failure, the package can be executed again, and it will resume  from the checkpoint.

While the package is being executed, you can see the checkpoint file appear, it is a text file, so it is human readable.

If, one time, you do not wish the package to resume from the checkpoint next time it is run, simply delete the checkpoint file. The next time the SSIS package is run, it will start from the begining.

SSIS Looping Through Files In Folder

A task I have had to do a number of times in SQL Server Integration Services (SSIS) is to cycle through all the files in a folder, and import each of those files. Here is a step by step guide to how I do that.

1) To a SSIS package add a For Each Loop Container

2) Inside the For Each Loop container, add a For Each Sequence container
3) Right-click on an empty space on the package file, and choose Variables
4) Create a string variable to hold your file-name
5) Right-click On the For Each Loop Container, choose Edit, choose Variable Mappings
6) From the drop-down list, select the variable you created to hold the file-name

7)

Additional Notes:

VALIDATION

When the package executes its first task is to validate connections and other elements. If certain files do not exist when the package first executes, this can cause validation errors and the package can fail.

To avoid this, on the connection or task properties set Delay Validation to True.

Another Problem

UNICODE

In a data-flow task, I got the error message "cannot convert between unicode and non-unicode string types."

The solution was to add a data conversion component to the middle of the data flow task.

To set the data conversion component up, select the columns that need transforming in the input columns list. Then in the list below, select Unicode String, or whatever Unicode type is appropriate. Also set an alias for the  column. The alias should be different from the original column name.

Finally, after closing the conversion component, redo the mappings going into the destination so that your new aliases are used, rather than the original unconverted column.

Wireless Network - Local Access Only

I have a Windows Vista machine, with a wireless network USB key, both the key and the router are by Netgear.

This would occassionaly loose it wireless connection, and report "Local Access Only".
Rebooting the PC did not solve this problem. I tried hard coding the IP address, sub net mask and gateway, but this did not resolve the problem.

Finally it did start again, I carried out these steps:
1) Rebooted the router
2) Disabled the network adapter
3) Rebooted the PC.

Then the network worked fine.

It may be that only steps 2 and 3 are required.

I still have to find out why it stops working after 2 or 3 days.

Building Web Pages with Telerik components

Validation of required fields

To validate required field add a system.requiredfieldvalidator for each component, and set the ControlToValidate property.

RadMenu

• Number of Visible Rows When Dropping Down - this is controlled from the properties for the menu. In the section default group setting height controls how deep the menu will appear.

sql server 2008 EXPRESS with Management Studio

SQL Server 2008 Express is available to download for free from Microsoft.

The basic installation file does not install the management studio.

There is a version of Management Studio for Express which is also free, this is a separate download.

When installing the management studio, there is a gotcha to beware of.

You are given the choice of 1) adding to an existing instance, or 2) installing a new instance. It would appear that 1 is the correct choice, but in fact it is 2.

Janus Scheduler Multiple Owners

It is possible in Janus Scheduler to have multiple owners, as shown in this example.

To achieve this:

Dim owner1 As New ScheduleAppointmentOwner

owner1.Key = "Studio 1"

owner1.Text = "Studio 1"

Me.Schedule1.Owners.Add(owner1)

Dim owner2 As New ScheduleAppointmentOwner

owner2.Key = "Studio 2"

owner2.Text = "Studio 2"

Me.Schedule1.Owners.Add(owner2)

Dim owner3 As New ScheduleAppointmentOwner

owner3.Key = "Studio 3"

owner3.Text = "Studio 3"

Me.Schedule1.Owners.Add(owner3)

Populating a schedule with multiple owners:

Private Sub SchedTest_Load(ByVal sender As Object, ByVale As System.EventArgs) Handles Me.Load

CreateAppointmentOwners()

Me.Jtktf01TableAdapter1.FillByAssigned(Me.TaC20081.jtktf01)

With Schedule1

.Date = "04/24/2008"

.View = Janus.Windows.Schedule.ScheduleView.DayView

.SetDataBinding(TaC20081, "jtktf01")

.OwnerMember = "grpcode"

.StartTimeMember = "schdate"

.EndTimeMember = "schdate"

.TextMember = "invno"

End With

End Sub

Creating a Virtual Hard Drive from an Existing Macine

This shows how to create a VHD from an existing non-virtual PC.

For windows XP or greater it is very easy.

Disk2VHD is a free microsoft utility that runs under windows and created a VHD on the same computer, that is a model of the machine. This can be attached to a virtual machine in Virtual PC and started. All that is required is sufficient disk space on the machine to create this VHD. On the first machine I tried this on it took a few hours to build.

Download Disk2VHD here http://technet.microsoft.com/en-us/sysinternals/ee656415.aspx

For Windows 2000 and older this approach will not work.

I am testing a shareware utility called WinImage (winima85.exe)

This did not work either.

Final course, I am going to pull the HD out of the laptop running windows 2000, plug it into a Windows 7 machine, and use Disk2VDH to clone it from there.

Unknown server tag telerik:grid

When trying to open aspx forms that contained Telerik controls I received the error message, "Unknown server tab telerik:grid". My colleague was able to open thse

We tried cleaning and rebuilding, opening and closing the project, which made no difference.

Then we noticed that in the solution explorer there were warning symbols against the telerik references.

My colleague had referenced the Telerik controls from the bin folder in the solution, but this folder had not been checked into source safe.

When my colleague added the bin folder to source safe, and I rechecked the code out, all worked fine.

So the answer we have found is to make sure that the Telerik dlls are in the project binary folder, and to reference them from there.

A similar problem occurred when the project refused to build. This was solved by adding a blank form, and dragging one of the telerik controls onto it.

Generic Charting Concept

ASP Flexible Charting Solution

The challenge is to add a chart output option to a system containing many existing reports. This legacy system is written in classic asp, but it would be desirable to create the charts in asp.net so that the latest controls could be used.

Ideally, the solution should be reusable, so that many reports can have this facility added. If we can do this in a elegant way that allows the use of asp.net controls in a system running classic asp then we have achieved our goals.

New asp Function fnctSaveValuesForCharts

On classic asp system create a function with 3 arguments:

1) Recordset

2) Name of measure column

3) Name of description column

When the report is run for the first time, this new function should be called at the same time.

New asp.net aspx page.

The second part of the system will be a new asp.net page.

This will take three arguments:

1. Segments (number of segments (n)),
2. Style (Pie, Bar)
3. Title (The title for the aspx page)

This will pick up the values and captions from the cookie saved by the classic asp page.

The top n-1 values will be displayed in bars or slices

The rest or the values that are not in the top n-1 will be summarised and displayed in a single bar/slice with the caption "Others"

For example, if the user chooses 5 slices, the pie chart will show the top 4 categories as individual slices, then all the other values will be summarised in the "Others" slice.

Having an "others" column/slice avoids cluttering up the chart with insignificant data.

The advantages of this overall approach are:

• It avoids the need to run the same query a second time
• Avoids need to transfer a complex object, such as a recordset, between asp version
• The data required for the charting is saved in a simple form ready for use
• The charting can easily be added to many pages. Changes to the look of the charts only need to be done in one place.

See this article on how to share session state information between asp versions:

http://msdn.microsoft.com/en-us/library/aa479313.aspx

How to add a total to an SSRS Report

To put totals into a SSRS report:

1) click on the detail of the report

2) Right click on the side bar that appears

3) Select footer from the menu

4) Note the extra row that appears in the report

In each cell of the footer where you want a total to be, right click and choose Expression.

Paste in something like this: =SUM(Fields!WORK_HOURS.Value)

The bit in the braces will normally be identical to the value in the field of of the detail report.

To format right click the cell again, choose Properties

In the format tab enter N2 to format to 2 decimal places.

ASP.Net

I had a strange problem with a asp.net form created by another developer. It had some totals labels that were updated on the fly depending on the values of text boxes on the form.

However, one of the total labels did not update.

The solution was to add this line in the source of the page as shown in the image below.