Specifying time zone in SQL DATETIME DEFAULTs

You’ve had your existing application running for years, relying on DEFAULT constraints to set DATETIME fields to the current date using GETDATE(). Everything was great until you migrated to Azure SQL Database – and realized that your SQL Server is now set to UTC, and you can’t change it.

Fortunately, you can change your DEFAULT GETDATE() constraint to save date in your time zone. Just specify the default as shown below, specifying your time zone of choice:

default convert(datetimeoffset, getdate()) at time zone 'Eastern Standard Time'

To prove this, run the following, and note the output will show the time in Eastern Standard Time as opposed to UTC.

create table #test (
     val nvarchar(max),
     dt datetime not null default convert(datetimeoffset, getdate()) at time zone 'Eastern Standard Time'
 )
 insert into #test ( val ) values ('yo')
 select * from #test

Voila! No more time zone issue.

OneDrive sync fails because filename starts with a space

Windows (and, it seems, Microsoft in general) does not like filenames that start with a space. You can’t even create them in Windows Explorer — go ahead and try! If you start the name of a file or folder with a space, Windows will remove the space for you — automatically. Frustrating, eh?

Regardless, there’s sufficient ways to wind off with a file that starts with a space, and inevitably, if you use OneDrive, you’ll get sync errors, because OneDrive (being Microsoft) does not like filenames that start with a space.

The logical step would be to rename the file in Windows Explorer and remove the leading space — but that doesn’t work; Windows will tell you that the filename already exists. (Apparently, those spaces are ignored in more places than one.)

So how do you solve this? Two ways.

  1. Rename the file to something entirely new without a leading space.
    OR
  2. Open a command prompt and type this:
    ren ” myfile.txt” “myfile.txt”

The command prompt lets you use spaces (provided they are in double quotes), and will (accurately) know that a file name with a leading space is not the same as the same file name without the leading space.

Best solution? Avoid the darn spaces altogether.

Force a direct download from a shared DropBox file

While working with DropBox today, I created shared links to a file, and the links looked like this:

https://www.dropbox.com/s/abcxyz123/filename.zip?dl=0

This link goes to a nice DropBox web page that provides a lot of information — information that would confuse a less-sophisticated user. Why can’t we just get them right to the download?

You can. In the DropBox shared file link, change the “dl=0” at the end of the URL to “dl-1”.

So the same URL above as a direct download would be:

https://www.dropbox.com/s/abcxyz123/filename.zip?dl=1

It’s always fun hacking URLs!

Forcing the min/max on HTML number inputs

HTML5 has fantastic new features — one of them is type="number" attribute for input tags, which (among other things) restricts the user to only entering numbers (and spawning the number keyboard on mobile phones) and giving a (not always useful) widget that lets you increase/decrease the number. Unfortunately, one piece is missing (at least from most browsers I’ve seen): restricting the input to numbers within the min/max range.

Fortunately, a little JavaScript (via jQuery) can fix that. Add the below to your site, and any input type="number" tags on your site will have their min/max values enforced.
Continue reading

Making the old new: Classic ASP and CSFBL

It’s embarrassing to say that my longest project, CSFBL (a web-based multiplayer baseball game), still has most of its interface written in Classic ASP. It’s also embarrassing to say that the web interface for CSFBL is a bit 1990s. I’ve had a lot of false starts moving forward to new technologies, having experimented on moving to WebForms, Castle MonoRail, and ASP.Net MVC (twice). The “next big thing” for CSFBL has, sadly, become a bit of phantomware similar to Duke Nukem 3D.

The hard part of moving to a new technology is getting rid of technical debt. I started coding CSFBL in 1999, using classic ASP and SQL Server. It’s amazing to think that some code is still the same as it was 17 years ago, but that’s not surprising for a product that’s been around for, well, 17 years. But it is what it is, and moving to something new means breaking something old and reliable — even if old and reliable is a beat-up old car that still runs great but is not so pretty to look at.

Taking a hard step backwards, I had to take stock and decide what is truly holding me back, and realized that it’s not the technology that holds me back, it’s that technical debt. So I experimented for a few hours, and then realized that my platform — Classic ASP and SQL Server — is already solid. I just needed to clean it up and make it work.

Classic ASP is more powerful than people give it credit for. I’ve done a lot with it over the years, even to the point of building a mini-ORM to make coding easier and modular. The newer code I’ve written is clean and functional. So why not take it all the way?

First, here’s three screenshots of a typical CSFBL page. The one on the left is the current (“legacy”) version, the other two are the new (“modern”) versions, built with Twitter Bootstrap, showing the desktop and mobile versions of the same page (fully responsive design so one page works for all devices).

CSFBL (legacy) CSFBL (modern, desktop) CSFBL (modern, mobile)

 

What a difference design makes… but how do we make that work in Classic ASP in a clean way?

Here’s the basic ASP template:

<%@ Language=VBScript %>
<% Option Explicit %>
<!-- #INCLUDE VIRTUAL="scripts/cTemplate.asp" -->
<%
	Response.Write template.GetHtmlHeader("Template")
	Response.Write template.GetPageHeader()
%>
	<div class="container" id="container-main">
		<div class="row">
			<h3>Template</h3>
		</div>
	</div>
<%
	Response.Write template.GetHtmlFooter()
%>

That’s a nice clean template. And the bulk of the work is done in the cTemplate class. What is that, you ask? It’s a class that allows us to do things like GetHtmlHeader() and GetPageHeader(). Here’s a snippet. The “htmlheader.html” files is a plain HTML file, with some curly braced tags for inserting the page title and timestamp of the CSS file (so we can better handle browser caching).

class cTemplate
	private m_htmlheaderfile
	private m_cssfile

	private sub Class_Initialize()
		set m_htmlheaderfile = new cFile
		m_htmlheaderfile.Load( Server.MapPath("/scripts/htmlheader.html") )

		set m_cssfile = new cFile
		m_cssfile.Load( Server.MapPath("/css/csfbl.css") )
	end sub

	private sub Class_Terminate()
		set m_htmlheaderfile = nothing
		set m_cssfile = nothing
	end sub

	public function GetHtmlHeader(title)
		dim output
		output = m_htmlheaderfile.GetText
		output = Replace(output, "{title}", title)
		output = Replace(output, "{csstimestamp}", CDbl(m_cssFile.DateLastModified))
		GetHtmlHeader = output
	end function
end class

dim template
set template = new cTemplate

What about things like querystring handling, since the URL of this page is “/team/awards.asp?teamid=###”? Simple — just parse the querystring, and load the team, and if it fails, send the person to a friendly “not found” page.

Dim team
Set team = new cTeam
team.Load(utility.ParseInteger(Request.QueryString("teamid")))

If team.ID = 0 Then
	Server.Transfer "/notfound.asp"
End If

That “utility” is an instance of another class, with utilities to do similar things in repetitive ways, like parsing integers.

The point is this: classic ASP isn’t bad. Like any technology, it can be mis-used. But when used properly, there’s a lot that can be gained with a small amount of work — especially when moving from a legacy codebase to a clean codebase.

For a functional comparison of the two pages above — both working on the same Classic ASP/MSSQL platform, and both using the same SQL back-end — try the following two links. The only difference between the two is the HTML/CSS/JS rendered.

Legacy: http://www.csfbl.com/teamawards.asp?teamid=5
Modern: http://dev.csfbl.com/team/awards.asp?teamid=50