//  home   //  advanced search   //  news   //  categories   //  sql build chart   //  downloads   //  statistics
 ASP FAQ 
Home
ASP FAQ Tutorials

   8000XXXX Errors
   Alerts
   ASP.NET 2.0
   Classic ASP 1.0
   Databases
      Access DB & ADO
      General SQL Server & Access Articles
      MySQL
      Other Articles
      Schema Tutorials
      Sql Server 2000
      Sql Server 2005
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
databases.aspfaq.com

ASP FAQ Tutorials :: Databases :: Other Articles :: How do I deal with MEMO, TEXT, HYPERLINK, and CURRENCY columns?


How do I deal with MEMO, TEXT, HYPERLINK, and CURRENCY columns?

MEMO / TEXT / VARCHAR(>255) 
 
Depending on the version of your MDAC driver, and the database you are connecting to, these columns can either (a) not show up at all, (b) only show up the first time they're called, or (c) cause 'Unspecified Error', 'Exception Occured' or 'Errors Occurred' runtime errors, if the following recommendations are not observed: 
  • Avoid SELECT * notation; NAME your columns in a list, and name the offending column(s) LAST.
  • Assign the value of the column to a variable IMMEDIATELY, and only use this variable from that point on.
  • Make sure your MDAC drivers are most current (MDAC Download Page).
  • See KB #200124 and KB #175239
Another problem people have is retaining line breaks from a textarea, once the value is returned to the screen. Access and SQL Server DO store this information, it's just not in a format that a browser's HTML engine understands. You need to replace line feeds and carriage returns with HTML tags. 
 
Here is some example syntax: 
 
<% 
    set conn = CreateObject("ADODB.Connection") 
    conn.open "<connection string>" 
    sql = "SELECT idcolumn,intcolumn,memocolumn FROM table" 
    set rs = conn.execute(sql) 
    do while not rs.eof 
        memocolumn = rs("memocolumn") 
        response.write(replace(memocolumn, CHR(10), CHR(10) & "&nbsp;<br>")) 
        rs.movenext 
    loop 
%>
 

Hyperlink, Currency, MONEY 
 
Many people assume that, because Access can understand that a hyperlink column should be underlined and clickable and that a currency column starts with a dollar sign and is followed by two decimal places, the browser should be able to as well. So they write syntax like this: 
 
<% 
    response.write(rs("hyperlink_column"))  
    response.write(rs("currency_column"))  
%>
 
And expect the browser to automatically wrap the hyperlink in an anchor tag, and format the currency column appropriately. Unfortunately, this isn't how it works. HTML doesn't know what a hyperlink is, unless you tell it to use an <A> tag. Similarly, HTML doesn't automatically format your numbers and assume you want thousands separators and dollar signs. 
 
DO NOT use the hyperlink datatype in Access. This is designed to allow users to click on links in the GUI. It is NOT meant for presentation-layer formatting (e.g. HTML). Use a VARCHAR column for hyperlinks. I would also recommend using a numeric data type with two decimal places for currency; in SQL Server, use the MONEY datatype. storing dollar signs and #link# values in the table aren't going to help you in HTML, and only add unnecessary storage size to your tables. 
 
Based on that, to format these values in your HTML, you need to do something like this (from Access)): 
 
<% 
    hl = rs("hyperlink_column") 
    response.write "<a href='" & hl & "'>" & hl & "</a>" 
    curr = rs("currency_column") 
    response.write formatcurrency(curr,2) 
%>
 
To get a hyperlink formatted properly right out of SQL Server, you can use: 
 
SELECT 
    '<a href='+hyperlink_column+'>'+hyperlink_column+'</a>' 
    AS hyperlink_column 
    FROM table
 
To get a money value formatted properly right out of SQL Server, you can use: 
 
SELECT 
    '$'+CONVERT(VARCHAR(100), money_column, 1) 
    AS money_column 
    FROM table
 
Some people have reported that this doesn't properly insert commas (I assume it has to do with collation or regional settings on the server). So, to force it using a bunch of wacky string manipulation, you can try something like this: 
 
SELECT 
    '$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(100),CONVERT(MONEY,money_column),1)),1,100)) 
    AS money_column 
    FROM table
 
Please let us know if this still doesn't work in your environment.

Related Articles

How do I build a query with optional parameters?
How do I calculate the median in a table?
How do I create a store locator feature?
How do I deal with multiple resultsets from a stored procedure?
How do I debug my SQL statements?
How do I determine if a column exists in a given table?
How do I enable or disable connection pooling?
How do I enumerate through the DSNs on a machine?
How do I find a stored procedure containing <text>?
How do I get a list of Access tables and their row counts?
How do I get the latest version of the JET OLEDB drivers?
How do I handle alphabetic paging?
How do I handle BIT / BOOLEAN columns?
How do I handle error checking in a stored procedure?
How do I ignore common words in a search?
How do I page through a recordset?
How do I present one-to-many relationships in my ASP page?
How do I prevent duplicates in a table?
How do I prevent my ASP pages from waiting for backend activity?
How do I prevent NULLs in my database from mucking up my HTML?
How do I protect my Access database (MDB file)?
How do I protect my stored procedure code?
How do I protect myself against the W32.Slammer worm?
How do I remove duplicates from a table?
How do I rename a column?
How do I retrieve a random record?
How do I return row numbers with my query?
How do I send a database query to a text file?
How do I simulate an array inside a stored procedure?
How do I solve 'Could not find installable ISAM' errors?
How do I solve 'Operation must use an updateable query' errors?
How do I temporarily disable a trigger?
How do I use a SELECT list alias in the WHERE or GROUP BY clause?
How do I use a variable in an ORDER BY clause?
Should I index my database table(s), and if so, how?
Should I store images in the database or the filesystem?
Should I use a #temp table or a @table variable?
Should I use a view, a stored procedure, or a user-defined function?
Should I use recordset iteration, or GetRows(), or GetString()?
What are all these dt_ stored procedures, and can I remove them?
What are the limitations of MS Access?
What are the limitations of MSDE?
What are the valid styles for converting datetime to string?
What datatype should I use for my character-based database columns?
What datatype should I use for numeric columns?
What does "ambiguous column name" mean?
What is this 'Multiple-step OLE DB' error?
What is wrong with 'SELECT *'?
What naming convention should I use in my database?
What should I choose for my primary key?
What should my connection string look like?
When should I use CreateObject to create my recordset objects?
Where can I get this 'Books Online' documentation?
Where do I get MSDE?
Which database platform should I use for my ASP application?
Which tool should I use: Enterprise Manager or Query Analyzer?
Why are there gaps in my IDENTITY / AUTOINCREMENT column?
Why can I not 'open a database created with a previous version...'?
Why can't I access a database or text file on another server?
Why can't I use the TOP keyword?
Why do I get 'Argument data type text is invalid for argument [...]'?
Why do I get 'Not enough space on temporary disk' errors?
Why does ASP give me ActiveX errors when connecting to a database?
Should I use COALESCE() or ISNULL()?
Where can I get basic info about using stored procedures?

 

 


Created: 7/9/2000 | Last Updated: 3/24/2005 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (89)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...