Monday, April 30, 2007

.Net web config error

If you get an error that looks like this: Could not load type 'System.Web.UI.Compatibility.CompareValidator' from assembly 'System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'. Don't spend too much time looking at your code for this, it appears to be a bug in the webconfig. Simply delete the validators from the webconfig.

The lines will look like this:

comment them out or remove them entirely and you're good to go.

The best bet when you get a strange webconfig error is to copy the entire text of the error and google it. That's the fastest way I've found to resolve runtime issues.

Thursday, April 26, 2007

Quick SQL

I'm almost embarrassed to admit this, I didn't know how to escape a quote (') in a sql string when doing an insert, 'hors d'ouvres'. My only consolation was that my hunch was right, double up the single quote within the string: 'hors d''ouvres'. Just like it!

Tuesday, April 24, 2007

Publish in VisStudio 2005

I'm a reluctant fan of the publish feature in VS 2005. It isn't easily tinkered with, at least not for me, and it forces a wholesale replacement of the website, including non-compiled pages (javascript, css, images, etc), which takes up too much time in my opinion, in addition to bringing over my development webconfig which is never the webconfig on a staging or production server.

So, a little trick I've come up with is to publish locally and then do a copy of only the bin directory and all ascx and aspx pages. For example, if I have a website under wwwroot (I know it's not necessary with VS2005, but i'm old-fashioned) called MySite, I will have a sister directory called MySite_Build. I publish to this directory and then copy to my remote servers.

When you publish, you have the option of local IIS and the file system. I would prefer local IIS because the path is simpler, but I find I get this error often:
"You must choose a publish location that is not a sub-folder of the source website." This error is confusing because the build directory is NOT a subdirectory and is in no way related to the source website. I haven't figured this out, when I get this error, I simply use the file system to identify the same directory and it works like magic. Go figure.

We'll be moving to the 3.0 framework pretty soon, but we'll still be working in vs2005 for awhile to come, so if anyone has any publishing tips, I can certainly use them!

Thursday, April 19, 2007

ADP Taxware Web API integration

This is just a short note about a gotcha I keep encountering with ADP taxware and then I neglect to document it, and it bites me in the a**. So here I am sharing my pain with the world (i should get a cookie).

ADP Taxware is a program that runs on a server. We have it running on our webserver with our website; currently our load allows this configuration. When the website calls the Taxware API, it needs access to the \program files\taxware directory. You MUST allow the internet anonymous account access to this directory or you will get an obscure error message, and stepping through the code will reveal it to be a permission denied error.

The only other quick advice I have to offer is make sure you copy your dlls to the system32 folder on your server; just residing in the taxware folder (where the install puts them) doesn't seem to be sufficient.

Finally, although Taxware's documentation is cumbersome, their tech support guy on this API is fantastic, his name is Jonathan Wang. Happy coding!

Wednesday, April 18, 2007

Aspdotnetstorefront Out-of-the-box Build Error

My company has begun offering an ecommerce solution to small-to-medium businesses called Aspdotnetstorefront. On behalf of our client, I purchased and download the product and installed it in my wwwroot directory. I made no changes to any files, I simply clicked on the sln file and loaded the solution into Visual Studio 2005 Team Edition.

Before I did anything else, I tried to build the solution. I was unsuccessful, and of course, seriously perturbed...I may have said unfriendly I sent an email off to tech support with a copy of the error.

I received a quick response (you gotta love responsive tech support) and they noted that I seemed to be referencing a file that should not be in that build. How could that happen? I looked in the directory, no file. Mysterious, eh? So I loaded the solution and looked in the directory, there was a file. Odd. So I looked at the path of the file, and it was a path to a storefront directory for a different storefront solution. Problem solved? almost.

I deleted all my files, extracted the download to my newly clean directory, and before I did anything with Visual Studio, I opened the sln file in text editor and modified all the paths in the solution to point to my correct directory. You will have this problem if you try to run more than one storefront solution side-by-side. I saved the sln file. Opened my project, all problems solved. To be safe, I renamed my sln and suo files to match my directory name.

I'm fairly new to this storefront thing, so if you have any tips to share, i'm all ears.

Friday, April 13, 2007

Faster Filter Queries

We should all now be indoctrinated enough to know the holy trinity of fast queries in .Net:

  • Stored procedures – NOT inline SQL
  • Table indexes – see your DBA if you’re unsure
  • Data Readers (if your data is read only)

I am also a strong proponent of the Microsoft Application Blocks, especially the Data Application Block. You will save yourself a lot of time, your code will be cleaner and easier to read, and your data activity will be optimized. (download here).

So that’s all good, but what I really want to talk about is an optimization that Nuri showed me for the query itself.

I was writing a standard read-only report on movie data in a GridView, and I had several filters for the users, such as date range, metro area, film, etc. My query passed in 0 (zero) for all filters that were unused. My initial WHERE clause looked like this:

WHERE (o.PostedDate BETWEEN @begindate AND DATEADD(d,1,@enddate) )
AND (o.FilmID = CASE @filmID WHEN 0 THEN o.filmID ELSE @filmID END)
AND (o.ContactID= CASE @contactID WHEN 0 THEN o.ContactID ELSE contactID END)
AND (o.MarketID = CASE @marketID WHEN 0 THEN o.MarketID ELSE @marketID END)
AND (o.NewspaperID = CASE @newspaperID WHEN 0 THEN o.NewspaperID ELSE @newspaperID END)

Well, that does the trick, but it’s not efficient. In the case of 0, I am still putting a requirement on the parser to go through each row and verify that the value equals itself, and the optimal solution is that the parser simply ignores the filter if a 0 is passed.

The optimized WHERE clause looks like this:

WHERE (o.PostedDate BETWEEN @begindate AND DATEADD(d,1,@enddate) )
AND ( @filmID = 0 OR o.FilmID = @filmID )
AND ( @contactID = 0 OR o.ContactID = @contactID )
AND ( @marketID = 0 OR o.MarketID = @marketID )
AND ( @newspaperID = 0 OR o.NewspaperID = @newspaperID )

Much better, right? If you have any query optimization tricks to share with me, I’d love to hear them.