O2 Mobile Broadband Dongle Connection Manager Woe

O2 sucks with my Huawei E160 yada yada. The O2 Connection Manager software just would not connect this morning, with a "Failed To Connect" and "Check your network coverage and user profile" error.

This may be cargo cultish but the solution I found was to:

- Uninstall O2 Connection Manager
- Delete the Cellular Profile from Control Manager / Network Connections
- Restart the machine
- Reinstall O2 Connection Manager by plugging in the dongle
- MOST IMPORTANTLY: Run this patch

Only then did it work again.

iPhone iPlayer Click To Play Stopped Working

Hi! I LOVE my iPhone 3GS. I also LOVE BBC iPlayer. So imagine my horror when one day I clicked on a radio show to play and it didn't work anymore! I pressed on the "Click to play" triangle, the screen flickered, but the standard Quicktime thingy didn't appear. Nada, zip. Same for TV shows.

At first I thought it was a problem with my wifi but no. Turned out I'd somehow turned off PlugIns in the iPhone browser. To reactivate them:

Go to Settings > Safari > Plug-Ins and turn it back ON :)

SQL Server 2005 - Snapshot Publishing Fun!

I tried to set up Publishing from my local SQL Server but it reckoned that the server name I was using was an alias. It also suggested that I use it's 'proper' server name, which I recognised as being the machine's name from ages ago before I changed it.

Turns out when I ran SELECT @@SERVERNAME , it was returning the OLD name.

The fix was to run this script (found here):

-- Use the Master database
USE master
GO

-- Declare local variables
DECLARE @serverproperty_servername  varchar(100), 
 @servername    varchar(100)

-- Get the value returned by the SERVERPROPERTY system function
SELECT @serverproperty_servername = CONVERT(varchar(100), SERVERPROPERTY('ServerName'))

-- Get the value returned by @@SERVERNAME global variable
SELECT @servername = CONVERT(varchar(100), @@SERVERNAME)

-- Drop the server with incorrect name
EXEC sp_dropserver @server=@servername

-- Add the correct server as a local server
EXEC sp_addserver @server=@serverproperty_servername, @local='local'

The server needed a reboot afterwards to make this fix work.

Export / Convert DataTable to Excel (XML format XLS file) in MVC

I see a thousand ASP.NET developers wanting to output an Excel-format document based on a lowly .NET DataTable.

I see people trying to output in CSV and then running into problems with Excel misinterpreting the cell datatype formatting.

I see people wanting to create an Excel Workbook with more than one Worksheet.

So, I present this! A little standalone class that lets you

• create an Excel document (XMLSS format)
• add as many Worksheets as you like by simply chucking DataTables at it
• send it to the browser for download

Usage an an MVC Controller Action:

public void ExportData()
{
    DataTable dtYourData = YourApp.GetYourDataTable();
    ExcelWorkbookGenerator exGen = new ExcelWorkbookGenerator();
    exGen.AddWorksheet("YourWorksheetTitle", dtYourData);
    exGen.SendToBrowser("YourSuggestedFilename");
}

Code:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Xml;
using System.Web;

namespace DaddyCode.Utilities
{
    /// <summary>
    /// ExcelWorkbookGenerator : Generate Excel XML - compatible documents from DataTables.
    /// </summary>
    /// <remarks>
    ///      Author: James McCormack, DaddyCode Ltd
    /// </remarks>
    public class ExcelWorkbookGenerator
    {
        private class Worksheet
        {
            public string Title = "";
            public DataTable Data = null;

            public Worksheet(string title, DataTable dataTable)
            {
                this.Title = title;
                this.Data = dataTable;
            }
        }

        private List<Worksheet> Worksheets = new List<Worksheet>();

        /// <summary>
        /// Add a new Worksheet to the Workbook, based on a DataTable that you provide
        /// </summary>
        /// <param name="title"></param>
        /// <param name="dataTable"></param>
        public void AddWorksheet(string title, DataTable dataTable)
        {
            Worksheets.Add(new Worksheet(title, dataTable));
        }

        /// <summary>
        /// Send the current Workbook to the Web Browser to view or save the file
        /// </summary>
        /// <param name="suggestedFileName"></param>
        public void SendToBrowser(string suggestedFileName)
        {
            HttpContext.Current.Response.ClearContent();
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" + suggestedFileName);
            HttpContext.Current.Response.Write(getWorkbookXML());
            HttpContext.Current.Response.End();
        }

        /// <summary>
        /// Generate an Excel-compliant XML Workbook
        /// </summary>
        /// <returns></returns>
        private string getWorkbookXML()
        {
            XmlDocument xDoc = new XmlDocument();
            xDoc.AppendChild(xDoc.CreateNode(XmlNodeType.XmlDeclaration, null, null));

            string strCustomNamespace = "urn:schemas-microsoft-com:office:spreadsheet";

            XmlElement root = xDoc.CreateElement("Workbook");
            root.SetAttribute("xmlns", strCustomNamespace);
            xDoc.AppendChild(root);

            XmlElement styles = xDoc.CreateElement("Styles");

                XmlElement styleBold = xDoc.CreateElement("Style");
                    XmlElement font = xDoc.CreateElement("Font");

                    XmlAttribute xStyleBoldID = xDoc.CreateAttribute("dc", "ID", strCustomNamespace);
                    xStyleBoldID.Value = "dc1";
                    styleBold.Attributes.Append(xStyleBoldID);

                    XmlAttribute xFontWeight = xDoc.CreateAttribute("dc", "Bold", strCustomNamespace);
                    xFontWeight.Value = "1";
                    font.Attributes.Append(xFontWeight);

                    styleBold.AppendChild(font);
                styles.AppendChild(styleBold);

                XmlElement styleDateTime = xDoc.CreateElement("Style");
                    XmlElement numberFormat = xDoc.CreateElement("NumberFormat");

                    XmlAttribute xStyleDateTimeID = xDoc.CreateAttribute("dc", "ID", strCustomNamespace);
                    xStyleDateTimeID.Value = "dcDateTime";
                    styleDateTime.Attributes.Append(xStyleDateTimeID);

                    XmlAttribute xStyleNumberFormat = xDoc.CreateAttribute("dc", "Format", strCustomNamespace);
                    xStyleNumberFormat.Value = "General Date";
                    numberFormat.Attributes.Append(xStyleNumberFormat);

                    styleDateTime.AppendChild(numberFormat);
                styles.AppendChild(styleDateTime);

            root.AppendChild(styles);

            // Populate worksheets

            foreach (Worksheet wSheet in Worksheets)
            {
                XmlElement worksheet = xDoc.CreateElement("Worksheet");

                XmlAttribute xSheetTitle = xDoc.CreateAttribute("dc", "Name", strCustomNamespace);
                xSheetTitle.Value = System.Text.RegularExpressions.Regex.Replace(wSheet.Title, "[^a-z0-9 -]", "", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                worksheet.Attributes.Append(xSheetTitle);

                XmlElement table = xDoc.CreateElement("Table");

                // Populate header row

                XmlElement header = xDoc.CreateElement("Row");
                XmlAttribute xHeaderStyle = xDoc.CreateAttribute("dc", "StyleID", strCustomNamespace);
                xHeaderStyle.Value = "dc1";
                header.Attributes.Append(xHeaderStyle);

                foreach (DataColumn col in wSheet.Data.Columns)
                {
                    XmlElement headerCell = xDoc.CreateElement("Cell");
                    XmlElement headerData = xDoc.CreateElement("Data");
                    headerData.InnerText = col.ColumnName;

                    XmlAttribute xHeaderDataType = xDoc.CreateAttribute("dc", "Type", strCustomNamespace);
                    xHeaderDataType.Value = "String";
                    headerData.Attributes.Append(xHeaderDataType);

                    headerCell.AppendChild(headerData);
                    header.AppendChild(headerCell);
                }
                table.AppendChild(header);

                // Populate data rows

                foreach (DataRow drData in wSheet.Data.Rows)
                {
                    XmlElement row = xDoc.CreateElement("Row");

                    foreach (DataColumn col in wSheet.Data.Columns)
                    {
                        XmlElement cell = xDoc.CreateElement("Cell");
                        XmlElement cellData = xDoc.CreateElement("Data");
                        XmlAttribute xCellDataType = xDoc.CreateAttribute("dc", "Type", strCustomNamespace);

                        if (drData[col.ColumnName] == DBNull.Value)
                        {
                            cellData.InnerText = "";
                            xCellDataType.Value = "String";
                        }
                        else
                        {
                            switch (col.DataType.Name)
                            {
                                case "Single":
                                case "Double":
                                case "Decimal":
                                case "Int16":
                                case "Int32":
                                case "Int64":

                                    cellData.InnerText = drData[col.ColumnName].ToString();
                                    xCellDataType.Value = "Number";
                                    break;

                                case "DateTime":

                                    XmlAttribute xCellStyleID = xDoc.CreateAttribute("dc", "StyleID", strCustomNamespace);
                                    xCellStyleID.Value = "dcDateTime";
                                    cell.Attributes.Append(xCellStyleID);
                                    if (drData[col.ColumnName] != null 
                                            && drData[col.ColumnName] != DBNull.Value
                                            && (DateTime)drData[col.ColumnName] != DateTime.MinValue)
                                    {
                                        cellData.InnerText = ((DateTime)drData[col.ColumnName]).ToString("o");  // ISO 8601 DateTime String Format
                                    }
                                    xCellDataType.Value = "DateTime";
                                    break;

                                case "Boolean":

                                    cellData.InnerText = (bool)drData[col.ColumnName] ? "1" : "0";
                                    xCellDataType.Value = "Boolean";
                                    break;

                                default:

                                    cellData.InnerText = drData[col.ColumnName].ToString(); // XmlElement.InnerText escapes reserved XML characters automatically
                                    xCellDataType.Value = "String";
                                    break;
                            }
                        }

                        cellData.Attributes.Append(xCellDataType);
                        cell.AppendChild(cellData);
                        row.AppendChild(cell);
                    }

                    table.AppendChild(row);
                }

                worksheet.AppendChild(table);
                root.AppendChild(worksheet);
            }

            StringWriter swOut = new StringWriter();
            xDoc.Save(swOut);

            return swOut.ToString();
        }
    }
}

If you're reading this and know a better way to do this sort of thing - PLEASE LET ME KNOW. I was driven to this because of the crap documentation on the web. I only achieved this limited success by reverse-engineering an existing Excel doc and latterly discovering the MS XML Spreadsheet Reference. Why they don't tell you that the XMLSS DateTime format is ISO 8601, I don't know...

iPhone or Android

ASP.NET GridRow - Get Cell when you don't know the index - using HeaderText, DataField, SortExpression etc.

Sometimes you want to reference a cell in a gridview row, but you don't know its ordinal index, so Row.Cells[x] is no good for you. In this circumstance it would be nice to say something like "just get me the cell from the column with the HeaderText value 'Price'".

Voila:

       public static int GetCellIndexByFieldHandle(this GridView grid, string fieldHandle)
        {
            int iCellIndex = -1;

            for (int iColIndex = 0; iColIndex < grid.Columns.Count; iColIndex++)
            {
                if (grid.Columns[iColIndex] is DataControlField)
                {
                    DataControlField col = (DataControlField)grid.Columns[iColIndex];
                    if ((col is BoundField && string.Compare(((BoundField)col).DataField, fieldHandle, true) == 0)
                        || string.Compare(col.SortExpression, fieldHandle, true) == 0
                        || col.HeaderText.Contains(fieldHandle))
                    {
                        iCellIndex = iColIndex;
                        break;
                    }
                }
            }
            return iCellIndex;
        }

Usage:

   void myGrid_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
             TableCell cellPrice = e.Row.Cells[e.Row.GetCellIndexByFieldHandle('Price')];
        }
    }

The method works for column HeaderText, DataField and SortExpression, so you should always have a way to grab hold of that cell. One caveat - it can't reference AutoGenerated columns. They have to be defined in the GridView template a la:

<Columns>
     <asp:BoundField DataField="Price" />
</Columns>

or

<Columns>
    <asp:TemplateField HeaderText="Price">
    <ItemTemplate>
        <asp:Label ID="lblPrice" runat="server" />
    </ItemTemplate>
    </asp:TemplateField>  
</Columns>

A JQuery function to set the value of all input controls which have an ID containing a given string

        function setAll(idFilter, value) {

            $('input[id*=' + idFilter + ']').each(function() {
                $(this).val(value);
            });
        }

JQuery has some superduper wildcard attribute selectors like *= (contains), ^= (begins with) and $= (ends with). More here.

iPhone on Orange - Cellular Data Network problem

I have an iPhone 3GS on the Orange UK network. It stopped being able to access the internet by any means other than wifi. 3G, Edge, GPRS - none of it worked.

I phoned Orange support - they said that a Carrier Settings update had screwed up the configuration. Normally, you can manually configure your phone in the Settings > General > Network > Cellular Data Network menu, but the Orange carrier settings update restricts your access to this.

To sort the problem, I had to do a full restore, allow the iPhone to reconnect with Orange via iTunes, but then deny the requests to update the Carrier Settings. By doing this, the Cellular Data Network menu was restored and I was able to set the APN correctly to orangeinternet (no user/pass required). Now internet could be accessed by the cellular data network again.

After a few days I did another sync with iTunes and this time when the carrier settings prompt appeared, I allowed it to update. Again, the Cellular Data Network menu was removed and although the phone continued to be able to access the internet without WIFI, I was annoyed to discover that the phone seemed to have had its MMS settings removed - it had even removed the option to send a photo as an MMS.

Before I resorted to doing another restore, I tried turning the phone off and on again - hey presto, MMS came back and all was okay again. Phew!

Addictive Drums + Reaper + Roland TD3 VDrums + MIDI UM-1G USB

Maybe you're a daft sod like me that doesn't read the instructions, but I had a few hurdles setting up this combo. So here's some tips.

1. Attach the UM-1G unit MIDI OUT cable to the MIDI OUT socket on the TD-3.
2. Install the UM-1G drivers in Windows and then attach the UM-1G USB cable.
3. Download ASIO4ALL v2 and install.
4. Install Addictive Drums.
5. Fire up Reaper.
6. In Preferences > Audio > MIDI Devices, Enable the UM-1G.
7. In Preferences > Audio > Device, Select Audio System : ASIO and choose Asio4All as the Driver.
8. Create a new track and arm it. Turn record monitoring on. Select MIDI > UM-1G > All Channels as the Input source. Enter FX, add the Addictive Drums VSTi.

At this point you should be getting sound out of your drums.

If you can't find the Addictive Drums instrument, maybe you should locate the vst file and place in the Reaper VST path?

If the MIDI map is wrong for your kit, go into the AD VST and click the "?" button - open the Map Window. Under Map Preset, choose Roland > TD-3. You can do further custom mapping by creating a Reaper JS file in the Program Files / Reaper / Effects folder. I found a couple of useful ready-made ones here.

I now realise that ASIO4ALL grabs the sound device and stops other apps like Windows Media Player from using it - annoying when you want to jam. I don't know a way round it, can anyone shed some light? At the moment it's okay cos I've hooked up my GuitarPort and am using it's ASIO driver instead of ASIO4ALL.

Comments / Questions appreciated!

LogiTech S530 Keyboard + Mouse For Mac - On Windows

Quick one - if like me you're using a Mac with Bootcamp and wondered if your Logitech S530 keyboard/mouse combo would work okay with Windows (and Win 7 in my case) - the answer is yes. It works fine out of the box.

Even better news is it works with the S510 drivers (SetPoint suite) if you download them off the Logitech site here.

When you do that all the cool shortcut buttons work as intended - volume control, quicklaunch etc.

Hope that helps someone!