Driving Microsoft Office from VS.NET

In theory…

To read the rather disjointed documentation that MS publishes, you’d think that all you needed to do is:

1) Download the Primary Interop assembly installer appropriate to your version of Office. - e.g., O2003PIA.EXE from http://www.microsoft.com/downloads/details.aspx?familyid=3c9a983a-ac14-4125-8ba0-d36d67e0f4ad&displaylang=en)

2) Install the download

3) Start VS and, in your project, go to Add Reference, COM tab and set a reference to the particular Office application you need to interface with – e.g., Microsoft Word 11.0 Object Library

4) At the beginning of the code file for your class, insert a corresponding “Imports” statement – e.g.,
Imports Microsoft.office.Interop.Word

5) … and program happily ever after.

‘T’aint necessarily so!

If you do steps 1-3, but step 4 results in a green squiggle under Microsoft.office.Interop.Word, you might have run into the problem – or something very like it - described on page http://support.microsoft.com/kb/823996, though the symptom described at the top of that page is probably not at all what you would notice first (“…you notice that Visual Studio .NET 2003 creates an Interop Assembly (IA) for the library instead of referencing the Primary Interop Assembly (PIA).” Yeah, right; this symptom just leaps to the eye!)

To check if you indeed have this problem, right-click and open My Project in Solution Explorer for your project, click the References tab (at the left hand side of the window) and widen the Path column so you can see the full paths associated with each reference.

If the Microsoft Office references are correct, they will have paths that begin
C:\Windows\assembly\GAC\

If the references are not correct, the paths will point to a location within the project’ own folder structure – e.g., to a sub-folder of %My Documents%\Visual Studio 2005\Projects\.

If the referenced path is wrong, close VS and do the resolution step as described at http://support.microsoft.com/kb/823996 - “To resolve this problem, Microsoft recommends that you run Office Setup from Add or Remove Programs in Control Panel:

•If the PIA does not appear in the GAC, run Office Setup and then mark the PIA for the application as Run from My Computer. Each Office PIA appears as a .NET Programmability Support feature for the corresponding Office application or for the corresponding Office component.

•If the PIA does appear in the GAC, run Office Setup and then select the option to repair your Office installation.”

That is, for each Office application, click the drop-down list beside .NET Programmability Support and set it to “Run from My Computer”. (The picture here shows that option selected for Word).

After that, there are another couple of steps that the Microsoft KB article does not cover, but which I was alerted to be a post at http://www.vbforums.com/showthread.php?t=510713. (Thanks, masfenix)

· Reopen your project and go to My project > References

· Remove the incorrect references and save the project.

· Add once more a reference to the Office application you need to work with –
e.g., Microsoft Word 11.0 Object Library

· Note that a corresponding reference to (e.g.) the Microsoft Object 11.0 Object Library has also been automatically added, along with a reference to the Visual Basic for Applications Extensibility Library.

· Confirm that the paths now point to locations in C:\Windows\assembly\GAC\
(if they don’t, you will have to Google further – sorry!).

Binding an asp:checkbox to a Boolean but Nulls-permitted Field

I needed to bind a check box to a database field whose data type is boolean (bit), but nulls are also permitted.

If nulls were not permitted, the control could be declared like this:

<asp:CheckBox ID="CheckBox1" runat="Server"
Checked='<%# Eval("MyBooleanField") %>'
/>

However, the Eval throws an error when the supposedly Boolean field contains a null value. Therefore, I adapted an idea from Jason Kester that I found at
http://www.velocityreviews.com/forums/t93739-checkbox-databind-does-not-work.html

The binding container from which the checkbox gets its data is a FormView control, so I created the following function in the page's code-behind:

Public Function fbool(ByVal o As Object, _
ByVal ColName As String) As Boolean
Try
Dim fv As FormView = _
CType(o, FormView)
With fv
Dim di As System.Data.DataRowView = _
CType(.DataItem, System.Data.DataRowView)
Return CBool(di.Item(ColName))
End With
Catch ' to catch the null data case
Return False
End Try
End Function

The checkbox declaration now looks like this:

<asp:CheckBox ID="CheckBox1" runat="Server"
Checked=
'<%# fbool(BindingContainer, "MyBooleanField") %>'
/>

So far, no problems - handles nulls ok. I'll update the post if any gotcha's arise.

The Single Most Important Thing for ASP.NET Developers to Know

Imho, it is this:
"Close Visual Studio and delete all contents of C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files"

Do this whenever you see error message BC30456 ("'some_object_name' is not a member of..."), or error BC30554 ("'some_object_name' is ambiguous"), or a compilation error message about a missing resource.

Restart Visual Studio.

8 times out of 10 the problem has gone away. The other two times, it genuinely is your fault - e.g., you have got two code-behind classes that have the same name.

I have a VB script file on my desktop to automate deleting the temporary folder:

Option explicit
Const sFolderName = _
"[your website's name]" ' <==== ### Customize this ####
Const sFolderPath = _
"C:\WINDOWS\Microsoft.NET\Framework\" & _
"v2.0.50727\Temporary ASP.NET Files\"

Const sBoxTitle = "Delete Web Site's Temporary folder from Server"

Dim sMsg ' as string
Dim ofs 'as Scripting.FileSystemObject
Dim fol 'as Scripting.folder

sMsg = "If the application is running, please close it." & _
vbNewLine & "Click OK when ready to continue."

if msgbox(sMsg, vbOKCancel, sBoxTitle) = vbCancel then wscript.quit

'Else
set ofs = CreateObject("Scripting.FileSystemObject")
set fol = ofs.getfolder(sFolderName & sFolderName)

on error resume next
fol.Delete
if err.number > 0 then
sMsg = "You didn't shut down the application!"
msgbox sMsg, vbexclamation, sBoxTitle
end if
set fol = nothing
set ofs = nothing

Truly Understanding...

Before I could solve the problem described in my previous post, I had to understand why my page wasn't working as originally written. The information I found in Dave Reed's blog on the topic "Truly Understanding ViewState" proved vital. I can't recommend his article (and others by him) too highly.

Once I understood why my page wasn't working (my FormView needed rebinding), I could then go about finding how to achieve that result, as described in the earlier post. But I couldn't even start looking for the solution until I understood the problem, and that's where Dave's article was essential.

Using RaiseBubbleEvent to Tell a Parent ASP.NET Control to Rebind

I am developing a web page that has a MultiView control whose Views each contain a FormView control. The FormView controls are bound to an SQLDataSource that is declared in the page (.aspx) that contains the MultiView control. Listing 1 shows the structure of the code.

I discovered that, when switching FormView mode (e.g., from the default read-only mode to Edit mode, I needed to get the SQLDataSource to rebind, so my problem was, how to make that happen? The FormView was the object that knew it was changing mode, but the SQLDataSource was an object on the parent page. How could I get them talking to each other, so to speak?

My first thought was to create a rebinding method on the parent page and find a way to call that method from the FormView's ItemCommand event handler. (The ItemCommand event is triggered when any mode-change button is clicked within the FormView). A little research showed that it wasn't feasible to directly call a method belonging to the parent, and, in any case, I should have known better.

The correct strategy was (1) to raise a "BubbleEvent" that would carry the message up the hierarchy, then (2) to trap the event at the page level and rebind in response to it.

In order to raise a BubbleEvent that could be recognized as carrying the "RebindNeeded" message, I had to write a new class and save it into my App_Code folder. Listing 2 shows the code. I named the class "RebindNeededEventArgs" and saved the code as "RebindNeededEventArgs.vb", but those names were arbitrary. The class could have been "Jane" and the code file "Jim.vb" and everything would have worked just the same - it just would have been harder to make sense of later.

Listing 3 shows how the ItemCommand event handler for one of the FormView controls creates a "RebindNeededEventArgs" object and passes it as a parameter to "RaiseBubbleEvent".

The parent object may receive bubbled events for more than one reason. It is the EventArgs parameter that allows it to recognize what kind of event this is that has been bubbled to it, so it can react accordingly. Listing 4 shows the OnBubbleEvent handler for the parent page in my project. Note how ElseIfs could be inserted to handle other kinds of bubbled events, if neccessary.

I hope these notes provide a useful summary of this design pattern. The particular context here is a FormView within a MultiView within a Page that also holds the FormView's datasource control, but the same pattern should apply in any situation when a child control needs to notify its parent that a rebinding is required.

Listing 1

<%@ Page Language="VB" MasterPageFile="~/masters/Initial.master" AutoEventWireup="false" CodeFile="BlogDemos.aspx.vb" Inherits="pages_BlogDemos" title="Demo Code for Posting to Blog" %> <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString= "[connection string declared here]" ProviderName="[provider declared here]" SelectCommand= " " /> <%-- Note: the SelectCommand for the SQLDataSource gets set dynamically in code that need not concern us here. --%> <asp:MultiView ID="MultiView1" runat="server" EnableViewState="True" > <asp:View ID="View1" runat="server"> <asp:FormView ID="FormView1" runat="server" DataSourceID="SqlDataSource1" ... > <ItemTemplate> <%-- ItemTemplate contents, + a user control that adds the row of command buttons--%> </ItemTemplate> <EditItemTemplate> <%-- EdItemTemplate contents, + a user control that adds the row of command buttons--%> </EditItemTemplate> <InsertItemTemplate> <%-- InsertItemTemplate contents, + a user control that adds the row of command buttons--%> </InsertItemTemplate> </asp:FormView> </asp:View> <asp:View ID="View2" runat="server"> <asp:FormView ID="FormView2" runat="server" DataSourceID="SqlDataSource1" ... > <ItemTemplate>...</ItemTemplate> <EditItemTemplate>...</EditItemTemplate> <InsertItemTemplate>...</InsertItemTemplate> </asp:FormView> </asp:View> </asp:MultiView> </asp:Content>

Listing 2
Imports Microsoft.VisualBasic
' Objects of this class provided the RebindNeededEventArgs object for events
' bubbled from a child control within a databound control to its parent.
' Its particular use is for when a FormView within a MultiView changes mode.
Public Class RebindNeededEventArgs
Inherits System.EventArgs

Public Sub New()
' No action needed
End Sub
End Class

Listing 3
Protected Sub FormView2_ItemCommand(ByVal sender As Object, _
ByVal e As System.Web.UI.WebControls.FormViewCommandEventArgs) _
Handles FormView2.ItemCommand
Try
' Create a RebindNeededEventArgs object
Dim RebindNeededArgs As New RebindNeededEventArgs()
' Call RaiseBubbleEvent, passing the RebindNeededArgs object
RaiseBubbleEvent(Me, RebindNeededArgs)
Catch e2 As Exception
'add error-handling code here, if appropriate
End Try
End Sub

Listing 4
Protected Overrides Function OnBubbleEvent( _
ByVal source As Object, ByVal args As EventArgs) As Boolean

If TypeOf args Is RebindNeededEventArgs Then
' [Insert code here to implement the rebinding]
Return True ' Stop the bubbling of the event
'ElseIf TypeOf args Is [some other kind] Then
' insert appropriate code here to handle the other event
Else
Return MyBase.OnBubbleEvent(source, args)
End If
End Function