Connecting to Excel and Access Files using .Net on a 64-bit Server

If you are trying to query a MS Excel (.xls, .xlsx, .xlsb) or MS Access (.mdb, .accdb) file on a 64-bit server and are getting one of the following error messages:

  • The ‘Microsoft.Jet.OLEDB.4.0’ provider is not registered on the local machine.
  • The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine.

Then it is necessary to install 2010 Office System Driver Beta: Data Connectivity Components on the server (the reason for this is the old Jet4.0 drover does not exist for 64 bit, and the ACE driver needed to read the newer formats is not installed by default). If you are using a version of Windows with UAC enabled, be sure to do this as Administrator.

After installing the components, use the following connection string formats (from the page linked-to above):

  • Using OLEDB, set the Provider argument of the ConnectionString property to “Microsoft.ACE.OLEDB.14.0”. Example: “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Path_To_File”
  • Using OLEDB and connecting to a Microsoft Office Excel file, add “Excel 14.0” to the Extended Properties of the OLEDB connection string defined in the previous bullet point.
  • Using ODBC:
    • Connecting to Microsoft Office Access (.mdb or .accdb): set the Connection String to “Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path to mdb/accdb file”
    • Connecting to Microsoft Office Excel (.xls, .xlsx, .xlsb): set the Connection String to “Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=path to xls/xlsx/xlsm/xlsb file”

Fixing the “circular file references are not allowed” Error in ASP.net

If you get the “circular file references are not allowed” error in an ASP.net Website Project and you do not have any controls that have any obvious circular references, what does the error mean and how do you fix it?

See this blog post from Siderite Zackwehdex as well as this MSDN forum post: by default, in a Website Project, ASP.net compiles one dll per folder in an ASP.net project. So if you have the following setup:

/folder1/Control1.ascx > References Control2
/folder2/Control2.ascx > References Control3
/folder1/Control3.ascx

This means that the folder1 dll will reference the folder2 dll which will again reference the folder1 dll, causing a “circular file reference”.

Even if there is not a direct path between the controls, looping into a circular reference, but there is a direct path connecting the circular reference through other controls in the same directories, it can throw the circular file references error. For example:

/folder1/Control1.ascx > References /folder2/Control2a.ascx
/folder2/Control2b.ascx > References /folder1/Control3.ascx
/folder1/Control3.ascx

Ways to fix it:

  1. Rearrange the layout of your controls (or masterpages) to remove the circular references (normally this will mean moving one control to another folder – in the example above, move control2 to folder1). This is the preferred solution.
  2. Use batch=”false” in the compilation tag of the web.config file. This will cause a new dll to be created for each control/page in the site. This should fix the error but is really lousy for performance, so it should be avoided (especially on production sites).

(This has happened to me a couple of times already, so posting it here as a reminder to myself for the next time).

Viewing Emails by Conversation in Outlook

I have been yearning for some time to have a gmail-like Conversation view in Outlook. Currently you can group by Conversation. However, this view will only allow you to group the emails in one folder (and if you can’t combine emails you have received with emails that you have sent, a conversation view is not worth much). However, by setting up a Search folder, you can get around this restriction:

  1. Set up a Custom Search Folder, set to include all messages that are in any of your mail folders (including Sent). You can get to this with File > New > Search Folder or Ctrl + Shift + P. There are also good instructions for this at HowToGeek.
  2. Click on the new Search Folder, and then go to View > Arrange By > Conversation.

That’s it. It works pretty well, though it does rely a bit too much on using the subject as the thread/conversation identifier (which is fine when you have a unique subject, but not fine for generic subjects). Hopefully this will be improved in 2010 (though as of now, initial reports are not good). Still, in many ways it is far superior to the regular inbox view (and unlike Gmail, you can still maintain a different grouping method in your inbox, and apply technologies like Sorting which for some reason are still beyond Gmail’s capabilities).

Optimal Setup for SyntaxHighlighter & TinyMCE

My favorite Javascript-based WYSIWYG editor is TinyMCE. I find it the most visually appealing, and the most well constructed from all of the offerings available on the Internet.

When posting code for others to see, I prefer using SyntaxHighlighter. This javascript-based formatting tool by Alex Gorbatchev allows you to put the code to be displayed into a Pre or TextArea tag, formats based on the code language, and allows for good degradation in case of NoScript.

I have previously posted on how to get SyntaxHighlighter to play nicely with the built-in text editor inside WordPress (TinyMCE). Now for the first time I needed to set it up on another platform (Telligent Community Server). Here are my recommendations on what to use to achieve optimal setup (this can work on any platform):

Problem 1: Need Friendlier Way to Input Code – The default method for adding code to be formatted woudl be to open up HTML mode in TinyMCE, type in the Pre or TextArea tag with specially formatted attributes, and put in the code. This is ok for myself (this is what I use in WordPress), but when designing a site for others, a friendlier interface is preferable. I found a plugin for TinyMCE: SyntaxHL by Richard Grundy. After installing this (download and copy files to tinymce/plugins directory, and add plugin and button to TinyMCE.Init), a new button is added to TinyMCE that loads a popup which allows easy entry of the code to be formatted and customization of language and display options, without the need to enter html mode. (I would prefer something that could load up highlighted text when the button is pressed, but I will settle on this one for now)

Problem 2: TinyMCE adds BR Tags within PRE – When opening up TinyMCE it performs some cleanup of the Html that is input. Part of this cleanup involves (by default) substituting <BR /> tags instead of New Lines in the source text. For code that has already been saved, this presents a problem. When editing a post with code formatted in a <PRE> tag, instead of preserving the new lines, <BR />’s are inserted, completely messing up the formatting. This has been noted by others, and has even been reported in the TinyMCE forums, but so far the bug is still in there. To the rescue, another plugin for TinyMCE: PreElementFix by T Andersen. After installing the files and adding the plugin to TinyMCE.Init, PRE acts as it should: no more insertion of <BR /> tags, and the tab key adds a Tab to the text, rather than advancing to the next field.

MS Office Live Addin for 2007 Causing Word, Excel and Powerpoint to Crash on Vista

I ran Windows Update a few days ago, and it ended up installing an add in for MS Office, to allow connectivity with Office Live Workspace. Very soon afterwards, Word, Excel and Powerpoint started to crash immediately after they were started up. The Office Live add in is the culprit. I was able to open up Word in safe mode (hold down Ctrl) and deactivate the add in, after which Word started working again. After I uninstalled the add in from my computer, Excel and Powerpoint are working again.

I am not the first person to whom this happened. Hopefully my wasted time figuring this out can help save someone out there a few hours.

Casting vs. Converting in .Net

I recently saw a piece of code in a project that was throwing an exception that I did not understand:

string boolString = (string)DataBinder.Eval(DataItem, "IsNew");

One would expect that boolString would be assigned the value “True” or “False” depending on the value of the IsNew property of the DataItem object. However, this through an Exception with the message: “Cannot explicitly convert bool to string”. What is going on here?

The syntax of (type)variable attempts to explicitly cast the variable into the given type. Casting does not attempt to interpret the data in the variable – it just tries to fit the object referred to by the variable into the new data type. This will work whenever the two types are somehow compatible (for example, an int can be cast into a float with no exceptions) though sometimes it may result in data loss (a float cast into an int). However, in cases where there is no connection between the two classes, casting will result in an Exception (like the one that I received above).

(There is another way to cast an object – using the as keyword. This will return a null if the cast fails, and is much faster than the explicit casting referred to above).

However, when converting an object, the conversion function has “knowledge” of the data contained in both the source and final object types, and will create the equivalent of the variable in the new data type. In the case above, using the System.Convert.ToString() method in place of the attempted cast to (string) would have worked fine. This is because the ToString method “knows” that a bool cannot be case into a string – but it also knows what the equivalent string to each value of a bool will be, and is able to process the operation accordingly. In this case, reflector uncovers the following code (in the Boolean class):

public string ToString() {
    if (!this) {
        return "False";
    }
    return "True";
}

This is obviously a very simple type conversion. However, there are many more complex conversion utilities built into the .Net framework (accessible through the System.Convert class – see the DateTime.ToString conversion for an example of this. Also see the IConvertible interface). While conversions can be more expensive to run, since they are strongly typed, they are checked at compile time, and when used properly are more reliable to use when producing stable code. (See the MSDN article on Casting and Type Conversions for more info).