When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
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.

- continued -

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!


  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article