02 Jul 09 MySql and the Art of Sql
I’ve been reading the The Art of SQL
Here’s somewhere I’ve implemented 1 of the techniques.
The task: return records for this month.
Usually I’d write something like
select * from results where month(dateadded) = month(now()) and year(dateadded) = year(now())
The problem with this is that 2 functions (month() and year()) need to be applied to every row to filter the results, this obviously requires overhead. Also we’re missing out on any indexes that might be on the dateadded field.
A better approach is to use
select * from results where dateadded >= DATE_FORMAT(now() ,'%Y-%m-01')
With this approach 1 function is run to find the first day of the month, and if the dateadded field is indexed we’ll benefit from this.
08 May 09 Import csv data into MySql
Unusually for an asp.net developer, MySql is my database platform of choice - very quick and less server intensive that certain other database platforms I could mention…
Anyway here’s how I go about loading csv data into MySql
- Prepare your data in a program such as excel or open office
- Save as comma delimied or tab delimited in the file system where mysql stores the data, e.g. if the data is to go in a database called “test” then save the csv in C:\Program Files\MySQL\MySQL Server 5.0\data\test
- Create a temporary import table whose structure matches the file to import
- Make each field a varchar(255) even if the field contains numbers, we can always remove rogue data later - let’s just get it into the database first!
- Load in the data (note: this is ignoring the first line as it contains the column headers and is set for a tab delimited file)
LOAD DATA INFILE 'myimport.txt' INTO TABLE test.tempimporttable FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' IGNORE 1 LINES
Tags: MySql
11 Apr 09 jQuery and asp.net validation controls
Just found a nice tip to check with jQuery if all built-in asp.net validation controls on a form are valid.
As an asp.net developer and jQuery advocate I know I’m going to find this tip very useful
http://devoma.com/post/JQuery-and-ASPNET-validators.aspx
06 Apr 09 Case insensitive xpath queries
I had an xpath query to test whether a node already exists in an xml document
Dim KeyphraseNode As XmlNode = xmlDoc.SelectSingleNode( _
String.Format("/websites/website[@url='{0}']/keyphrases/keyphrase[@value='{1}' and @type='{2}']" , _
WebsiteUrl, keyphrase.ToLower, keyphraseType))
Unfortunately users were testing for values with different capitalisations so the program was reporting that the nodes didn’t exist when actually they did.
I could have change the data in the xml file so that all data was stored as lowercase, but that would be nasty.
After a bit of digging round and consulting my trusty xpath book I found that there isn’t a nice and easy lowercase function built into xpath. And the best approach is to use the translate function. So the solution turned out to be
Dim KeyphraseNode As XmlNode = xmlDoc.SelectSingleNode( _
String.Format("/websites/website[@url='{0}']/keyphrases/keyphrase[translate(@value,
'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'abcdefghijklmnopqrstuvwxyz')='{1}' and @type='{2}']", _
WebsiteUrl, keyphrase.ToLower, keyphraseType))
Hope that helps if anyone else needs to do a case insensitive xpath search.
18 Mar 09 asp.net viewstate and seo
asp.net viewstate is a wonderful invention, but can bloat your html. For most users this is probably going to have minimal impact on the page load time. But for search engines spiders you really want to minimise needless information, especially near the top of the page.
It’s best practice to turn off viewstate for the whole page or for individual usercontrols if they don’t require viewstate. The deciding factor here is: does the page or usercontrol perform postbacks.
Even with careful pruning of viewstate you’ll probably find your viewstate is still large…
What you can do
Now there are a couple of strategies I’ve seen for working round this, both involve overriding the page
1 is to intercept the page rendering and move the viewstate hidden variable to the bottom of the page.
Here’s an example http://www.dotnetdiary.com/labels/Moving%20ViewState%20Field.html
2 is a very clever solution that keeps the viewstate information on the server and justs passes a key back and forth to the client.
I read about this approach here http://www.eggheadcafe.com/articles/20040613.asp
Here’s my approach
Now since I don’t think that viewstate bloat has too much of an impact on page performance for real users I’m only going to worry about search engine spiders.
asp.net has a way of detecting spiders - now I’m not saying it’s going to be 100% accurate but in my tests it’s been accurate enough for this technique.
In the page load of your page (or master page)
If Request.Browser.Crawler Then Page.EnableViewState = False
er that’s it, pretty simply eh? 
You can verify if this is working by viewing the source of the cached version of the page that google holds.
Hope that helps!
05 Feb 09 ASP.NET Event Validation and “Invalid Callback Or Postback Argument”
I was getting this and pulling my hair out.
My button (server-side) was pumping out a bit of javascript
Page.ClientScript.RegisterStartupScript(GetType(String),
"loginunsuccessful", "<script type=""text/javascript"">
alert('Login unsuccessful – please try again');</script>")
And this kept causing the error.
I tried using the prescribed fix: ClientScript.RegisterForEventValidation(mycontrol.uniqueid) in the prerender. Still got the error.
Here’s how I fixed it. RegisterStartupScript does the job of running at startup by emiting the js right at the foot of the page. I wrapped my JS in jQuery’s own onload code.
Page.ClientScript. RegisterStartupScript(GetType(String),
"loginunsuccessful", "<script type=""text/javascript"">
$(document).ready(function(){alert('Login unsuccessful – please try again');});
</script>")
Still the same error, I then changed to using Page.ClientScript.RegisterClientScriptBlock, which emits the JS in the midst of the html and hey presto no errors. This is without using the RegisterForEventValidation I’d like to add.
Final code:
Page.ClientScript.RegisterClientScriptBlock(GetType(String),
"loginunsuccessful", "<script type=""text/javascript"">
$(document).ready(function(){BlockAlert('Login unsuccessful – please try again');});
</script>")
Simple to fix when you know how.
26 Jan 09 Use jQuery to replace <body onunload=”GUnload()”>
When using Google maps it’s important to use GUnload on the page’s unload event. Fine when you can access the body tag directly…. But if you have the Google map functionality wrapped inside an ascx you need a different approach.
After a big of digging I found how to use jQuery to “catch” the unload event. In case anyone else needs it:
$(window).unload( function () { GUnload(); } );
Tags: jquery
16 Jan 09 Need to access session variables in an ashx?
Just a quick note if you need to access session variables from an ashx, you need to implement SessionState.IRequiresSessionState or IReadOnlySessionState if you only need to read the vars
In your ashx change
Public Class yourclass : Implements IHttpHandler Implements SessionState.IRequiresSessionStat
to
Public Class yourclass Implements IHttpHandler Implements SessionState.IRequiresSessionStat
25 Oct 08 My first iPhone app
Despite not owning an iPhone (I want one!), I’ve built my first iPhone application.
It’s a web app for playing Suduko. It generates a new grid every day. Utilises jQuery of course.
Find it here http://sudoku.nerdboy.co.uk/
I used http://www.testiphone.com/ to see that it worked and fitted the screen.
If anyone reading this actually has an iPhone and the time to test it I’d appreciate it.
Enjoy
25 Oct 08 Firebug for Google Chrome Campaign
I’ve been a Firefox fan for ages and when I discovered Firebug my life became a whole lot easier. So much so, that I’d hate to be without it.
But… I’m loving Google Chrome for it’s sheer speed. I never realised Firefox was slow until I tried Chrome - maybe it’s all the extensions I have installed
So I now spend my time between the 2 browsers.
I’d love to see a version of Firebug for Chrome, maybe it should be called ChromeBug…
