Thursday, 19 February 2009

Chart Control Problem

I've been banging my head againsta problem with the new Microsoft Charting control for an hour or so.

It turns out that the problem is simply that it can not be invisible!

I originally had the control in a Multiview and the View it was on was not the first view shown, you had to click a button or two and then the chart should appear by setting its view to be the active one. No luck, when it came time to display the view the "Error executing child request for ChartImg.axd" error came up.

So thinking it might be linked to the Multiview, I simply turned the chart invisible. Same problem.

But if I leave the chart on the screen at all time and simply use a style sheet to hide and show it, it works fine. So it seems that the problem is just that if the chart is first invisible ( server-side ) it will crash when you make it visible.

A bit crap really, but never mind, it's done now, phew!

Wednesday, 18 February 2009

C# Extention methods

This is a way of extending a class by adding methods to it. Heres a sample extention method class.

namespace ExtentionMethodSample
public static class ExtentionMethodSampleClass
public static Int32 myToInt32(this string s)
return Convert.ToInt32(s);

This example method adds a new method called "myToInt32" to the standard "string" class. This method will appear in intellisense.

In this case it simply allows you to convert a string to an Int32, which is a bit daft, but it could do any number of things. For instance it might return -1 if the string is empty. You can put any code into the method.

Another example is you might extend the DataRow class to return a Company object, as an alternative to creating a class that requires a DataRow in its constructor.

Another sample I've seen is where "string" was extended to give it a method to return only the numeric parts of the strings contents.

Tuesday, 17 February 2009

Returning tables from TSQL Functions

I was just blown away when I saw that you could return a table from a TSQL function. It's just something I've not seen before despite working with TSQL for years.

Heres a sample function :

RETURNS @MyTbl TABLE(ID INT NOT NULL, Keyword varchar(max))
INSERT INTO @myTbl (ID,Keyword)
SELECT CompanyID, CompanyName FROM Company

...and calling it ...

select * from dbo.FNHTest()

... I wonder if this is how Views are created and used internally by SQL?

Thursday, 15 January 2009

Creating a simple Pivot Table example

This has been driving me batty for hours. I finally managed to get one working. Here's what I did.

Firstly for this example I created a time table here's the data. As you can see it's a simple table showing year,town and an amount, this is the source data which is in a table I've called "felbrigg".


Now here is what I'm trying to achieve.


And here is the pivot statement to do it. with line numbers to help explain

SELECT [year],[towcestor],[mk],[barley]
FROM (SELECT [year],town,amount from felbrigg) as source
for town in ([towcestor],[mk],[barley])
) as pvt

1. Is the column list in the final output

2. Is the source of data, matches my source table in this example.

5. Is the calculation that is to appear in each cell of the final results

6. This specifies the column in the source that will be turned into columns in the final result, and you have to hardcode the values you want from the source column!