ADODB.Command error '800a0cc1' Item cannot be found in the collection corresponding to the requested name or ordinal.

If you get this error when you're doing classic ASP with an MS Access backend:

ADODB.Command error '800a0cc1' 
Item cannot be found in the collection corresponding to the requested name or ordinal.

And it doesn't make sense.... check that you haven't got that table open in Access. Doh! Stupid error message!

Ordinal Suffix in C# / .NET e.g. the postfix part of 1st, 2nd, 3rd, 4th

DEFINITIVE ANSWER TIME :)

Here's a natty extension method to get the Ordinal Suffix of a number.

public static string GetOrdinalSuffix(this int number, bool includeNumberInResult = false)
{
 string suffix = "th";
 if (Math.Floor(number / 10f) % 10 != 1)
 {
  switch (number % 10)
  {
   case 1:
    suffix = "st";
    break;
   case 2:
    suffix = "nd";
    break;
   case 3:
    suffix = "rd";
    break;
  }
 }
 return includeNumberInResult ? number + suffix : suffix;
}

Yes, this handles teens correctly i.e.

1: 1st
11: 11th
21: 21st
111: 111th
1011: 1011th

etc.....

Handling File Uploads from Dropzone.js with ASP.NET MVC

I had a need recently on an MVC project to allow the upload of image files. Having a look around to check if I was still using the best tools for the job, I came across DropzoneJS , which is excellent.

However, the documentation is a little terse and there are no MVC integration guides yet, so let me present a sample Controller Action for anyone who wants to simply see how to use it.

public string UploadFile()
{
 string errorMessage = "";

 foreach (var fileKey in Request.Files.AllKeys)
 {
  var file = Request.Files[fileKey];
  try
  {
   if (file != null)
   {
     YourInputStreamSaveMethod(file.FileName, file.InputStream);
     // Hint, google the phrase 'save inputstream file .net'
   }
  }
  catch (Exception ex)
  {
   errorMessage = ex.Message;
  }
 }

 if (errorMessage != "")
 {
  Response.StatusCode = 418; // Teapot Fail!
 }
 return errorMessage;
}

Modelling a Stored Procedure in EF: The selected stored procedure returns no columns

Trying to map a SQL Stored Procedure in Entity Framework in Visual Studio, I clicked "Update my Model from Database" on the EDMX view, and added the Stored Procedure. However, unlike other ones I had imported, when I looked in Model Browser, no Complex Type was created in the model to represent the result rows of the Stored Proc.

When I clicked on the Function Import for the sproc, The Return Type was set to none, so I tried to edit it. On the Edit Function Import screen, clicking Get Column Information resulted in: "The selected stored procedure returns no columns". This was not true!

Well, to cut a long story short, the fix was to include this line at the top of the Stored Proc SQL:

SET FMTONLY OFF;

After that, clicking Get Column Information worked, and I could create the new Complex Type with no problems.

MVC 4 site in IIS virtual directory under a WordPress site returns 403 error

This was perplexing. I rolled out an MVC 4 subsite onto a virtual directory on a PHP (WordPress) site running on IIS 7.5. At first glance the MVC site was working okay, but then I realised that none of the routes were working, returning the following error:

403 - Forbidden: Access is denied.

You do not have permission to view this directory or page using the credentials that you supplied.

Viewing the pages from IE on the server provided a bit more information:

HTTP Error 403.18 - Forbidden

The specified request cannot be processed in the application pool that is configured for this resource on the Web server.

Module: IIS Web Core

Handler: PHP53_via_FastCGI

Well, there was the clue. The WordPress installation was handling the URL routing, even though my MVC site was running in a sub-application. I discovered WordPress was doing this by using an IIS web.config file with a rewrite rule in the root folder. So, the simple way to solve our problem was to override the rewrite rules, in the web.config of the subsite:

<system.webServer>
    <rewrite>
        <rules>
            <clear />
        </rules>
    </rewrite>
</system.webServer>

Repeatable colour generation from arbitrary strings in .NET

I had a need to show items on a web page in different colours. The colours needed to be consistent between page views, but the items came from a database and I didn't want to store colour values in there, or have a hardcoded index of colour values.

A fun solution was to create a string hash using the name of the item, and then use the first three bytes of the string as RGB values to style the item with inline CSS.

var md5Gen = System.Security.Cryptography.MD5.Create();
byte[] hash = md5Gen.ComputeHash(System.Text.Encoding.UTF8.GetBytes(myItem.Name));
string hexColour = string.Format("#{0:X}{1:X}{2:X}", hash[0], hash[1], hash[2]); 

.NET Regex - Match something that's NOT there, with a Regular Expression Negative LookAhead Assertion

I wanted to search through a bunch of html and stick the text "http://" in wherever I detect an anchor element where the "http" bit is missing from the URL in the href.

Naturally, I want a RegEx.Replace, but here's the thing. What I want to do here is match something that is not there, i.e. the absence of the "http" in the URL. I was dimly aware of doing something like this before in Perl but didn't know how to do it in .NET.

So it turns out, what I wanted is called a Zero-Width Negative LookAhead Assertion. You define it in your pattern with a (?!MyString) like so.

text = Regex.Replace(text, @"<a href=""(?!http)", @"<a href=""http://");

The Zero-Width part means that whatever it asserts, it doesn't appear in the matched text. The Negative LookAhead part means that it looks ahead and evaluates to TRUE if the given string is NOT found. (Positive LookAhead returns TRUE if the text IS found).

More info can be found here: http://msdn.microsoft.com/en-us/library/az24scfc.aspx

ASP VBSCRIPT ADO Insert into Access

Two strategies for getting data into Access from old-school ASP / VBScript

ASP VBSCRIPT ADO Insert into Access - With Named Parameters

        Dim objCgtCmd: Set objCgtCmd  = Server.CreateObject("ADODB.Command")
        with objCgtCmd
            .ActiveConnection = Conn
            .CommandText = "INSERT INTO tblCgtDisclosure (ConsId, TaxYear, AdditionalIncome, CgtAllowanceUsed, PartnerName, PartnerTaxCode, PartnerGrossIncome, PartnerCgtAllowanceUsed) VALUES (@ConsId, @TaxYear, @AdditionalIncome, @CgtAllowanceUsed, @PartnerName, @PartnerTaxCode, @PartnerGrossIncome, @PartnerCgtAllowanceUsed)"
            .Parameters.Append .CreateParameter("ConsId", 3, 1, , Session("ConsId"))
            .Parameters.Append .CreateParameter("TaxYear", 3, 1, , Request("TaxYear"))
            .Parameters.Append .CreateParameter("AdditionalIncome", 6, 1, , Request("AdditionalIncome"))
            .Parameters.Append .CreateParameter("CgtAllowanceUsed", 6, 1, , Request("CgtAllowanceUsed"))
            .Parameters.Append .CreateParameter("PartnerName", 200, 1, 255, Request("PartnerName"))
            .Parameters.Append .CreateParameter("PartnerTaxCode", 200, 1, 255, Request("PartnerTaxCode"))
            .Parameters.Append .CreateParameter("PartnerGrossIncome", 6, 1, , Request("PartnerGrossIncome"))
            .Parameters.Append .CreateParameter("PartnerCgtAllowanceUsed", 6, 1, , Request("PartnerCgtAllowanceUsed"))

            .Execute()
        end with
        Set objCgtCmd = Nothing

ASP VBSCRIPT ADO Combined Update / Insert into Access - With RecordSet

Dim rs_CgtDisclosure
Dim rs_CgtDisclosure_SQL
Set rs_CgtDisclosure=Server.CreateObject("ADODB.recordset")
rs_CgtDisclosure_SQL = "SELECT * FROM tblCgtDisclosure WHERE ConsId = " & Session("ConsId") & " AND TaxYear = " & Request("TaxYear")
rs_CgtDisclosure.LockType = 3
rs_CgtDisclosure.CursorType= 2
rs_CgtDisclosure.Open rs_CgtDisclosure_SQL, Conn

If rs_CgtDisclosure.BoF And rs_CgtDisclosure.EoF Then
    rs_CgtDisclosure.AddNew()
    rs_CgtDisclosure("ConsId")   = Session("ConsId")
    rs_CgtDisclosure("TaxYear")   = Request("TaxYear")
end if

dim additionalIncome: additionalIncome = 0: if (IsNumeric(Request("AdditionalIncome"))) then additionalIncome = Request("AdditionalIncome") end if
dim cgtAllowanceUsed: cgtAllowanceUsed = 0: if (IsNumeric(Request("CgtAllowanceUsed"))) then cgtAllowanceUsed = Request("CgtAllowanceUsed") end if
dim partnerGrossIncome: partnerGrossIncome = 0: if (IsNumeric(Request("PartnerGrossIncome"))) then partnerGrossIncome = Request("PartnerGrossIncome") end if
dim partnerCgtAllowanceUsed: partnerCgtAllowanceUsed = 0: if (IsNumeric(Request("PartnerCgtAllowanceUsed"))) then partnerCgtAllowanceUsed = Request("PartnerCgtAllowanceUsed") end if
dim partnerTaxCode: partnerTaxCode = "": if (Not IsNull(Request("PartnerTaxCode"))) then partnerTaxCode = Request("PartnerTaxCode") end if
dim partnerName: partnerName = "": if (Not IsNull(Request("PartnerName"))) then partnerName = Request("PartnerName") end if

rs_CgtDisclosure("AdditionalIncome")   = additionalIncome
rs_CgtDisclosure("CgtAllowanceUsed")   = cgtAllowanceUsed
rs_CgtDisclosure("PartnerName")     = partnerName
rs_CgtDisclosure("PartnerTaxCode")       = partnerTaxCode
rs_CgtDisclosure("PartnerGrossIncome")   = partnerGrossIncome
rs_CgtDisclosure("PartnerCgtAllowanceUsed")  = partnerCgtAllowanceUsed
rs_CgtDisclosure.Update()
ErrorMsg = "Contract details updated!"
    
rs_CgtDisclosure.Close

Linq "Sounds Like" queries on SQL using SoundEx / SoundCode

Using .NET 4 and Entity Framework it's quite easy to add "sounds like" query functionality using SQL's underlying SOUNDEX function.

The key is to use the SqlFunctions object found in System.Data.Objects.SqlClient, e.g.

var query = dataContext.Agencies.AsQueryable();

if (!string.IsNullOrEmpty(searchTerm))
{
   query = query.Where(agency => agency.Name.Contains(searchTerm) || SqlFunctions.SoundCode(agency.Name) == SqlFunctions.SoundCode(searchTerm));
}

When web-form submissions fail

When you submit information from a form on any website, there are several ways in which the submission may fail. 

• Client-script errors (bugs with the web-page code) or functioning client-side validation code may prevent the submission from being sent.
• Network problems at any point between the user and the server may physically stop the submission from being transmitted. 
• Server-side software errors, hardware problems, or functioning server-side validation code may prevent the submission from being processed.
• Temporary application configuration changes, database problems or application restarts may prevent the submission from being processed.

For all of these scenarios, it may still be possible, depending on the software on the web page or server, that the user may experience what seems to be a successful result, either by seeing an explicit success message, or by not seeing an explicit error message.