code-u-like

C#, .Net, Sql Server, Salesforce, Dynamics CRM etc,
Charity and Not-for-Profit sector

Monday, June 18, 2007

Sql 2000 Concatenation Trick

Last year I worked at a client site writing imports into the database for an Ascent CRM system. The import process was being implemented using Sql 2000 stored procedures. After getting used to a C# environment with source control and unit testing, working entirely in T-SQL was like going back to the 1970s. Still, the client had their reasons; although the job could have been done in C#, the charity in question figured that T-SQL would be cheaper to maintain in the long run, with programmers easier and cheaper to find.

So during my months of retro programming, I stumbled upon a SQL-2000 feature I hadn't seen before ... the ability to concatenate strings from multiple rows of a results set without using a cursor. It works like this:

The SET operator, as well as appearing before a SELECT statement, can also appear implicity inside a SELECT statement (and hence be used to set multple variables) like this:
declare @name varchar(40)
declare @id varchar(11)
select top 1 @name = au_lname,
  @id = au_id
from authors
print @name
print @id

So far, so normal. But it turns out you can also aggregate the SQL variable to itself, like this:
declare @combined varchar(2000)
select @combined = @combined + au_lname
from authors
where au_lname like 's%'
print @combined

The example above demonstrates the syntax, but won't actually work, due to NULLs. The variable is NULL to begin with, so every attempt to concatenate onto the end of it also returns a NULL. Here's a proper example:
declare @combined varchar(2000)
select @combined = isnull(@combined + ', ','') + au_lname
from authors
where au_lname like 's%'
print @combined

This will concatenate the last names from all the authors returned, and also put commas in between. If I'd known that before writing the first import, I could have saved myself a whole load of messing about with cursors.

(OK, some developers will point out that this sort of thing should be done in the presentation layer, but as I said, it was a 1970's style assignment - there was no presentation layer.)

If you're running SQL 2005, there are other ways of doing it, using FOR XML PATH or CROSS APPLY - see the second half of this article on aspfaq.com for details.

Labels:

Tuesday, June 12, 2007

ASP.NET 1.1 Panels render as tables in Firefox?

ASP.NET Panels are expected to render as <DIV> elements, but poking around with Firefox and FireBug the other day I was surprised to see them rendered as single-cell tables. It turns out that, by default, ASP.NET 1.x treats Firefox as a completely incompetent browser that can't even handle <DIV>s properly.

Though this sounds like another strike in the browser wars between IE and Firefox, apparently it is an accident of the development schedule of ASP.NET 1.1. Firefox's usage expanded so quickly after its initial release, that ASP.NET's handling of it was quickly out of date.

So, ASP.NET 1.1 treats Firefox as a 'downlevel' browser, and assumes it has only basic support for CSS, DOM and Javascript. Fortunately, this can be changed by adding a carefully-tuned <browserCaps> section inside the <system.web> section of web.config or machine.config. Get the <browserCaps> right, and ASP.NET will then treat Firefox as 'uplevel'. Note that ASP.NET 2.0 treats Firefox as uplevel by default, so doesn't need this fix.

The <browserCaps> syntax is pretty wierd, but an example (along with a much more detailed discussion of the problem) can be found in Bart Gerardi's article here:
Rendering to Firefox from ASP.NET
The article also discusses other differences and things to look out for when using ASP.NET with Firefox.

Labels:

Saturday, June 2, 2007

Another Development Blog

Well, most developers seem to have blogs these days, so I thought I'd better start one.

Quite often when I have a tricky problem or baffling error-message and I Google for an answer, I find it on a developer's blog or message-board. So thats another good reason for starting my own one.

Hopefully I will come up with some interesting content and/or rambling.