|
Introduction
Often in ASP-related forums and newsgroups I find questions written by ASP beginners regarding input and output of database fields containing textual information. I decided to group some of these FAQs and answer them in this article. This should help Web creators who wish to use dynamically generated contents (stored in text files or memo fields).
First, it will help to include apostrophes and pipe characters in databases.
It will also show how to make that text look better by replacing CRLFs with BR tags and spaces with " " sequences.
And it will preach the right way to store textual contents in database memo fields.
I will be using the light version of the ASP component called 1touch. 1touch Light is free and can be downloaded form the 1touch Web site (http://www.1touchASP.com).
Part 1: How can I insert the apostrophe symbol in my database?
This is a common problem when dealing with databases.
Databases reserve the ‘ sign to start and end strings (e.g., ‘restaurant of McDonald’). So by typing ‘ in the text you actually enter a third apostrophe in the middle of the string and "confuse" the database (e.g., ‘McDonald’s’). An important note is that there is no problem with regular quotes. The " symbol can be inserted to any database field without special manipulations.
The solution: Under the same MS conventions, a string containing two consecutive apostrophes will be entered to the database as one apostrophe.
So, double your apostrophes – instead of McDonald’s write ’McDonald’’s ’.
So now you think, "That’s good, but what if I gather information from Web users? I can’t force them to double each ‘ they type, can I?"
True. But you can manipulate what users enter into forms with ASP and a VBSscript function called Replace. Here is how you can use the Replace function to replace each ‘ with ‘’:
MemoField = Request.Form("MemoField") ' Field from form
Replace (MemoField,"'", "''")
Note: In 1touch we created the InsertApos function for similar purposes, yet more elegant syntax, which despite of its name, replaces not only the ‘ character but also the | (pipe character) with their doubles.
Look at this ASP code, which is a part of an ASP file that inserts data to a database (called "registration") from a Web form:
<%
' Create the 1touch Object on the page.
set 1t = Server.CreateObject("OneTouchASP.StrFunctions")
' Define ADODB parameters
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "dsn=DSNname;uid=UserID;pwd=UserPassword"
' Get parameters from the form collection and
' apply 1t.InsertApos on them
First_Name=1t.InsertApos(request.form("First_Name"))
Last_Name=1t.InsertApos(request.form("Last_Name"))
Company=1t.InsertApos(request.form("Company_Name"))
Email=1t.InsertApos(request.form("Email"))
WWW_Site=1t.InsertApos(request.form("WWW_Site"))
' id - is a parameter you get from the DB's id field.
' Put parameters in registration database.
SQLStmt = "INSERT INTO Registration (id, " &_
"First_Name, Last_Name, Company, " &_
"Resume, Email, Www_Site)"
SQLStmt = SQLStmt & "VALUES (" & id
SQLStmt = SQLStmt & ",'" & First_Name & "'"
SQLStmt = SQLStmt & ",'" & Last_Name & "'"
SQLStmt = SQLStmt & ",'" & Company & "'"
SQLStmt = SQLStmt & ",'" & Resume & "'"
SQLStmt = SQLStmt & ",'" & Email & "'"
SQLStmt = SQLStmt & ",'" & Www_Site & "'" & ")"
Application.Lock
Conn.Execute(SQLStmt)
Conn.Close
Application.Unlock
...
%>
Part 2: Line breaks originally entered "disappear."
Well, those line breaks do not disappear, but actually become invisible or inactive. MS Windows translates the key <enter> into special characters called CR LF AKA chr(13) chr(10) or vbCrLf, but Web browsers are not familiar with any of these symbols. Browsers need the <br> tag to break between HTML lines.
So what you need to do is to convert those CR LF symbols to <br> tags (actually to <br> and vbCrLf, so the source code would be readable for debugging).
After reading the answer to the previous question, you should be able to do it yourself:
MemoField = RSlist("MemoField") ' Field from database
Replace(MemoField,chr(13) & chr(10), "<br>")
Note: In 1touch we created the ConvertBreak function for similar purposes.
Look at this code, which is a part of an ASP file, which retrieves fields from a database and shows them on the page. Notice the memo field "Resume":
<%
' Create the 1touch Object on the page.
set 1t = Server.CreateObject("OneTouchASP.StrFunctions")
' Define ADODB parameters
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "dsn=DSNname;uid=UserID;pwd=UserPassword"
' Get parameters from the registration DB using SQL Query
SQLQuery = ("SELECT * FROM registration")
Application.Lock
RSlist = Conn.Execute(SQLQuery)
RSlist.Movefirst
' Retrieve the records, Run Convert Break on the memo field
' and write to the HTML
Do while not RSlist.EOF
First_Name=RSlist("First_Name")
Last_Name=RSlist("Last_Name")
Company=RSlist("Company")
Email=RSlist("Email")
Www_Site=RSlist("Www_Site")
Resume = 1t.ConvertBreak(RSlist("Resume"))
Response.write First_Name & " " & Last_Name & "<br>"
Response.write "Works in " & Company & "<br>"
Response.write "Email : " & Email &_
" , URL : " & Www_Site &_
"<br><br>"
Response.write "Resume : <br>"
Response.write Resume
Response.write "<hr>" ' add a delimiter at the end of the record
RSlist.Movenext
Loop
Conn.Close
Application.Unlock
...
%>
You can also use ConvertSpace in 1touch, which turns a streak of at least two spaces to HTMLs chars, like this:
<%=1t.ConvertBreak(1t.ConvertSpace(MemoField))%>
Writing your own ConvertSpace using Replace is a bit tricky and is where 1touch Light comes in handy.
Part 3: Why not use both functions together and store HTML code in the database?
It seems that Part #1 and Part #2 could be used together in one multipurpose function. This would result in less trouble for the programmer, who would only have to to manipulate the content once vs. twice, and in HTML code (<br> tags and chars) stored in memo fields in the database.
Well, so it seems. However, doing this would be poor practice.
It is best to keep the content of your database HTML free.
Even though you may not plan it, it is probable that the contents of these database fields may some day serve you in the desktop environment (Visual Basic, MS Access applications or plain "copy and paste" to Office files). It will be a real pain to get rid of those <br> tags and chars then.
Use InsertApos when inserting content into a database (going IN) and ConvertBreak and ConverSpace when retrieving content from a database (going OUT).
Conclusion
Now, using 1touch Light, you can enter apostrophes into your database, display line breaks and spaces when retrieving it to a Web page, and you know to keep your content in the database HTML code free.
About the Author
Boaz Sigelman is vice president of Zplusplus Systems and Communication Ltd. in
Israel (http://zplusplus.co.il). He is a BSc graduate of Industrial Engineering and Management in the Tel Aviv University.
In his 3 years of experience in Zplusplus, he has managed dozens of Web application projects for Zplusplus customers, mostly in the ASP environment.
He wrote a seven-article HTML guide titled "Make Your Own Web Site," which was published in Ziff-Davis' Israeli PC Media magazine in 1997-98.
Boaz likes to relax with Jazz music and a good glass of whiskey, and hates the fact that he is unable to resist checking his E-mail from his home computer.
|
