Displaying Listboxes and Hyperlinks with GetString

Recently I was searching 4Guys for information on GetString, an ADO method that converts a Recordset's contents into a compact string with user-defined delimiters. The purpose of using GetString is to greatly speed up the construction of blocks of data-embedded HTML, such a HTML TABLES. In my search for information on GetString, I came across a previous article entitled: Speeding Up ASP by Using GetString.

Throughout the article I noticed a mention about populating listboxes using GetString... however, I could not find any bit of code relating to this, so I decided to write my own! In this article I'd like to show you how to populate a listbox using GetString, and also how to create a list of hyperlinks from a database. If you are not familiar with GetString, I highly recommend that you read Speeding Up ASP by Using GetString first, since I will not delve into details on how GetString works.

Populating Listboxes with GetString
Before we examine the code, let's look at the functional syntax for GetString (all parameters are optional):

String = recordset.GetString(StringFormat, NumRows, ColumnDelimiter, RowDelimiter, NullExpr)

Note that for our examples we'll be using the ColumnDelimiter, RowDelimiter, and NullExpr parameters, which specify, respectively, what string to delimit each Recrodset column with, what string to delimit each Recordset row with, and what string to use if a Recordset cell contains a NULL value. Also, you can try out each of these scripts I've included in my article! Just click on the View Live Demo link at the conclusion of the code!

<% Option Explicit 'Create the SQL query Dim mySQL mySQL="select Name from tblFAQCategory order by Name" 'Create the Connection and execute the query. Dim objConn Set objConn = Server.CreateObject("ADODB.Connection") objConn.Open "DSN=MyDSN" Dim objRS Set objRS = objConn.Execute(mySQL) 'Incase no records exist, have a handler to present a sweet message 'and call the subroutine CloseConn which closes the connection. If objRS.eof then Response.Write "Sorry, No records were found.<br>" Call CloseConn Response.End End If 'This part of the code creates the selectbox, with a 'selected option for all companies. Response.Write "<select size=""5"" name=""company"">" & _ "<option selected value=""All"">All Companies</option>" Response.Write "<option>" Response.Write objRS.GetString(,,,"</option><option>", "--NULL--") Response.Write("</option></select><br><br>") Call CloseConn 'Clean up! ' Sub to handle closing and cleaning up connections Sub CloseConn objRS.close set objRS = Nothing objConn.close Set objConn = Nothing End Sub %>

[View Live Demo]

While the above script may seem nice, many developers would like enhance the code so that instead of producing HTML output like:

<SELECT SIZE="5" NAME="company"> <OPTION SELECTED VALUE="All">All Companies</OPTION> <OPTION>AT&T</OPTION> <OPTION>MCI</OPTION> <OPTION>Sprint</OPTION> </SELECT>

it would populate each row's unique ID in the VALUE parameter of the OPTION tag like so:

<SELECT SIZE="5" NAME="company"> <OPTION SELECTED VALUE="All">All Companies</OPTION> <OPTION VALUE="3">Application</OPTION> <OPTION VALUE="4">Strings</OPTION> <OPTION VALUE="1">Session</OPTION> ... </SELECT>

To accomplish this we only need to make a few minor changes. First, in our SQL statement, we'll need to grab the unique ID in the SELECT statement:

Dim mySQL mySQL="select FAQCategoryID, Name from tblFAQCategory order by Name"

and then we'll need to alter the GetString statement some:

Response.Write "<select size='5' name='Company'>" & _ "<option selected value='All'>All companies</option>" Response.Write("<option value='") Response.Write objRS.getstring(,,"'>", _ "</option><option value='", "-null-") Response.Write("'></option></select>")

[View Live Demo]

That's it! In Part 2 we'll examine how to construct a list of database-created hyperlinks using GetString. I'll be presenting a bit of I think most developers will want to use quite often!