SQL string formatting in VBScript

If you’re a software developer, string concatenation is something you do regularly, irrespective of the programming language you use. While languages such as C, C++, Perl and PHP all support the very useful sprintf() function, the VB/VBScript developers have traditionally had to resort to using a combination of single and double quotes, escape characters, text, line continuation characters and ampersands (&) to get the job done.

The .NET framework provides a much cleaner approach with the string class format() method by allowing the creation of string values by specifying insertion points for other values. Like the sprintf() function, which was inherited from the C language, the .NET string.format() method accepts a format string followed by one to many variables that are to be formatted. The format string consists of placeholders, which are essentially locations to place the value of the variables you pass into the function.

The practicality of sprintf() and string.format() is especially evident when interacting with SQL databases, based on user specified parameters. Traditionally, VB/VBScript programmers have had to do something like this to build their query:

surname = "dover"
firstname = "ben"
orderby = "surname"
sql = "select surname, firstname, address, phone, email
         from contacts where surname like '" & surname & "%' _
         or firstname like '%" & firstname & "%' _
         order by " & order & " asc"

This got even messier if you wrote anything for the web in a classic ASP environment, as you often had HTML code mixed in with your server-side logic. This is where our home-grown fmt() function comes in. So, instead of the above, you can instead do this:

surname = "dover"
firstname = "ben"
orderby = "surname"
sql = fmt("select surname, firstname, address, phone, email _
         from contacts where surname like '{0}%' or _
         firstname like '%{1}%' order by {2} asc", _
         array(surname, firstname, orderby))

Much easier to read, no? It’s also a lot less error-prone and therefore more time-effective. Basically, the numbers in braces in the first parameter correspond to array elements in the second parameter.

Just add this your current VBScript library:

function fmt(str, args())
   dim res, i
   res = str
   for i = 0 to ubound(args)
      res = replace(res, chr(123) & cstr(i) & chr(125), cstr(args(i)))
   next
   fmt = res
end function

Keep in mind that the args parameter has to be an array at all times. This is because VBScript does not support a variable number of arguments to be passed to a function, unlike C, C++, Perl, PHP or Visual Basic.

Posted in ASP, Database, Programming, SQL, Visual Basic by Ivan