Published: Thursday, October 26, 2000
Displaying Listboxes and Hyperlinks with GetString
By Robert Monteiro
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!
Read Part 2!
