articles:

Formatting ADO GetString

GetString is a very efficient way of extracting and displaying a large amount of data from a database (well technically from a recordset, but this is nearly always going to have come from a database) - both in terms of code verbosity and performance.

However, it does have a downside: the options to format the output are somewhat limited...or are they?

The GetString method looks like this:

str = rs.GetString(format,rows,coldel,rowdel,nullexpr)

As you can see every row and column has to be delimited in the same way (if you're not too sure how the GetString method works then have a look at this w3schools page); as a result GetString is invariably used to output a table where each cell is formatted in the same way ~ which to be fair is probably how it was intended to be used.

BUT this does not have to be the case!

You can make GetString format its output - for example you could make it generate product listings with images and links.

How to format ADO GetString

Well, the secret lies in the SQL you use to create the recordset in the first place - you inlude the formating you want in the query itself.

So for example take a typical product table, which inludes the following fields: id, title, image and we use the following SQL to create our recordset:

SELECT id, title, image FROM products

And using a generic GetSting call like this:

str=rs.GetString(,,"</td><td>","</td></tr><tr><td>"," ")

Will give us the contents of a table with 3 columns, one for each of the fields we selected. The contents of the cells will just be text

Now lets say we want to display the image in the table rather than just the image name, and we want to make the title be a link using the id as part of the url, well by modifying the SQL we use to create the recordset we can achieve this:

select ('<img src="/productimages/' + image + '">'), ('<a href="/products.asp?id=' + cstr(id) + '">' + title + '</a>) from products

Now, even though we are still selecting id,title and image; this query will only return two columns: one with the image mark up (including the image name), and one with the link (including both the id and the title)

When you call the GetString Method on a record set created using that SQL the output will still be in a table, but the left column will have actual images and the left column will have actual links.

TIPS:

  • There are certain string manipulation functions you can use in the SQL itself if you need to make slight changes to the output such as instr, mid, left, right etc
  • Remember to escape the double quotes if you are running the SQL on a web page / script
  • This has been tried and tested when running the SQL against MS Access (and will most likely work running against SQL Server)
  • You can also achieve the same effect when using GetString with a recordset generated from a MySQL database, but you have to change the way you create the formatting in the query to use CONCAT('<p>',columnname,'</p>')

Comments

Posted on Friday, March 11, 2016 5:06:00 PM GMT by donld
great example I used in a asp classic pp that rocks!!!! I dont use .net its slow

Post a comment

name:
(optional)

email:
(optional)

comment: