Tuesday, November 10, 2009

Simple Localization in SQL Server with a Formula field

This little trick works for an application supporting a few languages (English, Spanish, French), although it won't scale much further.

A SQL Server database table MyTable requires a Description field, but an English, Spanish, and French translation need to be stored in the record and (hopefully) retrieved transparently.  Three fields are created: EN_Description, SP_Description, FR_Description.  A fourth field Language holds one of three values ('EN','SP','FR').

A user-defined function is created:

CREATE FUNCTION dbo.udf_LanguageDependantValue
    (
    @language varchar(2),
             @EN_value varchar(100),
             @SP_value varchar(100),
             @FR_value varchar(100)
    )
RETURNS varchar(100)
AS
BEGIN
    DECLARE @result varchar(100)

    SET @result = 'Unassigned'
   
    IF @language = 'EN'
        SET @result = @EN_value
    IF @language = 'SP'
        SET @result = @SP_value
    IF @language = 'FR'
        SET @result = @FR_value

    RETURN @result
END

Back in the table definition, a final field Description is created, with the Formula:

([dbo].[udf_LanguageDependantValue]([Language], [EN_Description], [SP_Description], [FR_Description]))

Now use a standard SELECT statement and SQL Server automatically retrieves the correct language field:

SELECT Description FROM MyTable

Thursday, November 5, 2009

ASP.NET runtime error: ambiguous match found

The solution to this turned out to be far from obvious, and once again posts by other bloggers came to the rescue.

(If you want the executive summary: it was due to variables defined with different cases.)

I just converted a .NET web project to a VS2008 web application, also changing the .NET Framework used (both in the Project, and the IIS website) from 1.1 to 2.0. The conversion went quite smoothly using the VS Convert To Web Application wizard, but one page had the problem below. Other pages using the exact same control and TagPrefix line compiled and worked without complaint.

Parser Error

Description: An error occurred during the parsing of a resource required to service this request. Please review the following specific parse error details and modify your source file appropriately.

Parser Error Message: Ambiguous match found.

Source Error:



Line 1:  <%@ Register TagPrefix="axp" Namespace="Axezz.WebControls" Assembly="AxpDBNet" %>
Line 2:  <%@ Register TagPrefix="cr" Namespace="CrystalDecisions.Web" Assembly="CrystalDecisions.Web, Version=11.0.3300.0, Culture=neutral, PublicKeyToken=692fbea5521e1304" %>
Line 3:  <%@ Page language="c#" Codebehind="UpdateEventQueue.aspx.cs" AutoEventWireup="false" Inherits="CANBTV.UpdateEventQueue" %>

Source File: /CANBTV_3_0_0/UpdateEventQueue.aspx    Line: 1



Version Information: Microsoft .NET Framework Version:2.0.50727.3603; ASP.NET Version:2.0.50727.3082

There were no errors reported by Visual Studio.

Not finding anything wrong in the code, I exchanged the positions of Line 1 and Line 2 ... the warning remained on Line 1, it did not follow the Axezz.WebControls line. So it isn't about the TagPrefix lines themselves, but something else.

Due to changes in customer requirements, some lines had been removed from the .aspx and the aspx.cs source files, but those did not seem to be implicated and the HTML tags were still balanced and well-formed.

I did have some trouble with the Designer ("Error creating control: Unknown server tag 'axp:AxpDataGrid'). Removing the TagPrefix line completely changed the error message shown where the control should be ("This control cannot be displayed because its tag prefix is not registered in this web form") - makes sense - so I put back the TagPrefix line, the error message returned to the "unknown server tag" one.

What? Is this a problem with the TagPrefix line, third party controls, or as Peter Johnson's Blog implies, variable naming problems - use of inconsistent case? I started looking at the latter, but there were so many ... the first dozen or so variables were okay, I got tired of looking at more ...

Curious about the difference between a functioning page and this one, I right-clicked on the .aspx in VS and chose View in Component Designer almost at random ... it wouldn't open, a half dozen messages errors were listed of the type: The variable 'x' is either undeclared or was never assigned. Hmm.

Then in File Explorer, I noticed there was no .designer.cs file for this .aspx. The original pre-conversion website didn't have any .designer.cs files at all, I guess VS.NET didn't use them (used .aspx.resx files instead?)

I found this post by Jan Schrueder (Create a missing designer.cs file) - open the .ASPX in HTML mode, CTRL+A, CTRL+K, CTRL+F (select all and format), then it said to right-click the Project and then Convert to Web Application. I had already done that, so I just selected the offending .aspx file only and ran Convert to Web Application.

Voila! a message box reported that, indeed, one of my variables had a different case in the .aspx vs the .aspx.cs. It wasn't one of the ones reported, in the Component Designer error messages either.

It transpired that there were three such variable conflicts in the .aspx.cs file, like this:

        protected System.Web.UI.WebControls.DropDownList Dropdownlist_ProgramTypes;
        protected System.Web.UI.WebControls.DropDownList DropDownList_ProgramTypes;

the .aspx controls used the capitalized names DropDownList_... so I removed the Dropdownlist_ protected variables until the Convert to Web Application succeeded.

There was still no .designer.cs file, but I built and ran the web app anyway ... now it worked like a charm.

By the way, the VS Designer still showed the error messages Error creating control: Unknown server tag 'axp:AxpDataGrid' over the control until I closed that file and re-opened it in the Designer ... then the controls looked correct.

And the .designer.cs file never did show up, that .aspx still uses a .aspx.resx file instead.


I just came across Hariman Haghighi's blog where he points out how to configure a custom task to add to your project, to catch .Ambiguous problems at compile time.  I don't know if it would have helped me - but it might help you!

Mike