Archive for February, 2008

DLookup for Excel

I had to do a couple of ad-hoc Excel jobs today and found that whilst Excel has a VLookup function for spreadsheet/ranges it doesn’t have one for databases.

It’s been a while since I touched VBA and Access but the DLookup function was quite useful so here it is for Excel. Read the warnings below before you use it!

Public Function DLookup(expression As String, domain As String, criteria As String, Optional connectionString As String)

    Dim con As ADODB.connection
    Set con = New ADODB.connection
    If IsMissing(connectionString) Then
        con.Open "Driver={SQL Server};Server=abc;Database=def;Trusted_Connection=Yes;"
    Else
        con.Open connectionString
    End If
    
    Dim rs As ADODB.Recordset
    Dim sql As String
    sql = "SELECT TOP 1 " + expression + " FROM " + domain + " WHERE " + criteria
    Set rs = con.Execute(sql)
    If Not rs.EOF Then DLookup = rs(0)
    rs.Close
    Set rs = Nothing
        
    con.Close
    Set con = Nothing

End Function

Copy and paste the code above into an Excel module and replace the “Driver=… with what you want to be your default connection.

Some examples of use:

To select the Name column from the Customers table where the numeric ID is held in column 2.

=DLookup("Name","Customers","ID=" & A2)

To return Forename and Surname columns joined together with a space from the Contacts table in the crm database using the postcode from column 5

=DLookup("Forname + ' ' + Surname","crm..Contact","PostCode='" & A5 & "'")

To return Description from a Products table held in another connection using a partial like match from column 1:

=DLookup("Description","Products","OrderCode LIKE '" & A1 & "%'", "DSN=SecondaryDSN")

I think you get the idea!

There are a few caveats you need to be aware of using this function:

  • It only returns the first value of the first row
  • DLookup’s are not efficient opening a connection each time it is referenced. This could put a lot of strain on your server.
  • DLookup syntax is open-ended and has some attack vectors.

If that doesn’t scare you then give it a shot. As usual no warranty expressed or implied!

[)amien

Hibernate presentation in Guernsey

My good friend Steve Streeting is giving a talk about the Hibernate object-relational mapper at the Guernsey Software Developer Forum tomorrow night. Hibernate is a very successful ORM for Java which has been ported to .NET under the moniker NHibernate.

The event, as always, is open to everyone and commences at 6pm at the Guernsey Training Agency above the old Post Office in Smith Street, St. Peter Port. They normally run for around 1 hour followed by an optional 30 minutes in the pub for those who can’t get enough (code, not alcohol).

In related news I have been liasing with Microsoft who have agreed to provide support to host a VS 2008 InstallFest event to celebrate the launch of Visual Studio 2008. The idea is you bring along your laptop (if you have one) and install one of the copies of Visual Studio 2008 Professional we will be giving away (full, not-for-resale copies) as well as enjoying some food, demos of the new features of .NET 3.5 and a bit of Xbox 360 guitar hero.

We’re just looking for a venue for this event (and potentially future GSDF meetings) as the Post Office has now been sold and will not be available from the end of February :(

[)amien

Microsoft opens Office binary file format specifications

Microsoft have released the binary file format specifications to their Office suite (the XML ones are already published) under their Open Specification Promise.

I am not a lawyer but as far as I understand this means you are free to implement the standards with a promise that Microsoft will not use any patents under its control that are required to implement the specification against you.

Hopefully Apple will now address Keynote’s PowerPoint support bug so exported PPT’s works with Office 2007.

Now that the .NET Framework 3.5 source is available (for reference) and Scott Guthrie (now VP) announcing the MVC framework will be user-buildable and patchable (not redistributable) and include project templates for a number of open-source testing frameworks the future is looking very rosy.

[)amien

My favourite WordPress plugins

I’ve been asked what plugins I recommend for WordPress so here’s the ones I currently use. Some of them require work in your theme – I started with the Redoable 1.2 theme which supports many of them.

Akismet

Probably one of the most well-known plugins for WordPress this little wonder screens all comments for spam using the Akismet web service. Get a key to access the service by signing up for a at WordPress.com and then configuring it in Plugins > Akismet Configuration.

Feed Statistics

I’m don’t want my subscribers in the control of a third party but I do like FeedBurner’s subscriber counts and analysis tools.

The Feed Statistics plugin provides a small subset of that functionality, the important one being a subscriber count which I now show in the sidebar. I went with a 3 day count configured from the Feedin WordPress admin.

Google Analytics for WordPress

There are a bunch of Google Analytics integration plugins out there but Google Analytics for WordPress apears to be the one currently using Google Analytics New Tracking Code ga.js instead of the old Urchin one. This actually uses a new URL and technique that hopefully won’t be blocked by so many viewers and also promises access to exciting new features as they become available…

Google Reader widget

I’m still in love with Google Reader especially since they added search to it (quite how they forgot that I’ll never know). One of the great things is that you can share your stories with your friends or better yet expose it as another RSS.

Google Reader widget adds a sidebar widget to show the stories you have chosen to share in your sidebar so no need for the annoying link-list posts (unless you need to add opinion or commentary of course). Configurable via Plugins > Google Analytics.

Gravatars2

Blog that don’t allow comments don’t get onto my Reader list without a fight. Without comments a post can’t be trusted – the author isn’t interested in any other opinions or thoughts.

Gravatar is a great site where you can register a picture with your email address so any site implementing Gravatars will show it next to your comments. In no time you’ll start recognising faces and pictures and decide if you want to check out their blogs too.

The Gravatars2 plugin puts those images next to the people who comment on your blog and can helpfully cache them locally for you given permission. It is configured from Options > Gravatars and the cache managed from Manage > Gravatar Cache.

Gregarious

Social bookmarking is incredibly popular through sites like Digg, StumbleUpon, Reddit, Delicious, Technorati, Windows Live Favourites or plain old email.

Gregarious takes care of providing links to submit your posts to these sites at the whim of a passing viewer. You can configure it in Options > Gravatars to choose the sites you want (I added DotNetKicks with a URL of http://www.dotnetkicks.com/kick/?url={url} ) as well as emailing you when a post is dugg and draw those famous little ‘n diggs’ yellow buttons.

Related Posts

Problogger’s Darren Rowse recommends interlinking posts to keep readers on your site and interested.

Related Posts automatically provides a list of likely related posts & pages based on keyword matching.

StatPress

Google Analytics is nice but the stats tend to lag a bit behind and sometimes you want to know what’s happening right now.

StatPress collects and reveals interesting real-time stats on Dashboard > StatPress including per-day & month counts of visitors, pageviews, spiders and feeds as well as recent hits, search terms and referrers. It also shows some visitor analysis and an interesting spy mode that shows recent visitors path through the site including how they got there.

My only complaints are that the MySQL database grows quite quickly and the analysis pages are slow. This is most likely caused by logging and analysis of raw data. Still it seems a lot less resource hungry that FireStats.

WP-PostRatings

An attempt to get quick feedback on what posts people are finding interesting and which aren’t with a simple star-rating next to each post.

Through no fault of it’s own WP-PostRatings has failed rather miserably here with few people wanting to click a star to rate a post. Will be dropped in the redesign.

WP-PostViews

Another visitor-retention seeking effort. By presenting the most popular content in the sidebar I’m hoping to entice people to look at a couple of other posts and hit the magic RSS subscribe button.

WP-PostViews records the stats and provides a method to get the post stats out you can put into your theme but most importantly comes with a widget to render a sidebar full of your most popular content.

[)amien