[thelist] Freebie ASP Tip: Getstring & SQL Concatenation

<tip type="ASP Getstring & SQL Concatenation">
So you like the speed benefits of getString, but its formatting options
aren't flexible enough? In some cases, you can do some creative SQL
concatenation to take advantage of GetString.

For example, say your query returns 100 records with three fields:
FirstName
LastName
Title

You'd like to spit them all out like this:

FirstName LastName, Title<br>
FirstName LastName, Title<br>
FirstName LastName, Title<br>
FirstName LastName, Title<br>
....

Normally, with Getstring, you can only specify a character(s) between
*every* field, not just between fields two and three. If you used this:

myStr = objRS.GetString(,,", ","<br>","&nbsp;")

You'd get

FirstName, LastName, Title<br>

However, you can use SQL to concatenate FirstName and LastName into one
field, like this:

"SELECT FirstName & " " & LastName AS FullName,Title From Authors"

(Concatenation syntax may vary depending on your datastore, above is for
Access)

then, using the same getstring command,

You'll get:

FullName, Title<br>
FullName, Title<br>
FullName, Title<br>
FullName, Title<br>

.. where full name is the first and last name separated by a space - exactly
what we're looking for.

Kinda long winded, but useful for speeding things up!

</tip>

Regards,

Eric Engelmann
Technology Architect
Geonetric Technologies, LLC
eric@geonetric.com / http://www.geonetric.com / 319.221.1667