You are currently offline, waiting for your internet to reconnect

Encoding form data for SQL Server input and output to HTML

Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Summary
When you are working with records in a database using Microsoft Active Server Pages(ASP) for Internet Information Services (IIS), errors may occur when the text hasnot been properly formatted for creating a SQL Server clause.

For example, when you create an ASP page to store information posted from an HTMLform to a database, you may see an error message similar to the following:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '<SQL Text>'.
/<path>/<filename.asp>, line <nnn>
More information
The error message above occurs when you try to submit a name with anapostrophe, (for example "O'Malley"), and then insert that name into a SQL Server clause withoutfirst "escaping" it. This can result in a SQL Server clause that resembles thefollowing:
INSERT INTO Table (txtName VALUES ('O'Malley')				
Because SQL Server uses the apostrophe as a text delimiter, the apostrophe in the nameinadvertently signifies an end to the data, which causes the error message to occur. This textcan be "escaped" by using two apostrophes, for example:
INSERT INTO Table (txtName VALUES ('O''Malley')				
Obviously, it is not ideal to mandate that all users on aWeb site enter two apostrophes for each desired apostrophe.In addition, when text is later extracted from a database, certain formattingcharacters are ignored during HTML parsing on a client (for example CR-LF, TAB,and so on.)

An ASP solution

The following ASP code defines two functions that are designed to handlesome simple parsing of information for input/output of SQL Server data, and can easilybe expanded to incorporate more functionality:
  • FixForSQL() parses a string for input to SQL Server
      Function FixForSQL(tmpText1)    ' define a working variable    Dim tmpText2    ' populate our working variable    tmpText2 = tmpText1    ' compact a CR-LF sequence as CR to save space    tmpText2 = Replace(tmpText1,vbCrLf,Chr(13))    ' replace each apostrophe with two apostrophes    tmpText2 = Replace(tmpText2,Chr(39),String(2,39))    ' return the fixed string    FixForSQL = tmpText2  End Function						
  • FixForHTML() parses a string for output to HTML
      Function FixForHTML(tmpText1)    ' define a working variable    Dim tmpText2    ' populate our working variable    tmpText2 = tmpText1    ' replace each CR with a line break tag and CR-LF    tmpText2 = Replace(tmpText2,Chr(13),"<br>" & vbCrLf)    ' replace each TAB character with four non-breaking space tags    tmpText2 = Replace(tmpText2,Chr(9),"&#xa0;&#xa0;&#xa0;&#xa0;")    ' return the fixed string    FixForHTML = tmpText2  End Function						

Practical example - Guestbook application

The following steps demonstrate how to use the above functions tocreate a simple guestbook application using ASP and a Microsoft Accessdatabase:
  1. Copy the following ASP code and save it to a folder in your Web site with at least IIS script access enabled and Copy the following ASP code and save it to a folder in your Web site with at least IIS "script" access enabled and Everyone - Change NTFS permissions:
    <% @Language="VBScript" %><%  Option Explicit  ' define our variables  Dim strMethod  Dim strURL  Dim strTitle  Dim strSQL  Dim objCN  Dim objRS  ' get some environment variables  With Request    strMethod = UCase(.ServerVariables("REQUEST_METHOD"))    strTitle = UCase(.ServerVariables("SERVER_NAME")) & " Guestbook"    strURL = LCase(.ServerVariables("URL"))  End With  ' open our database connection  Set objCN = Server.CreateObject("ADODB.Connection")  objCN.Open "driver={Microsoft Access Driver (*.mdb)};uid=admin;pwd=;" & _    "dbq=" & Server.MapPath(".\guestbook.mdb")  ' if the request was a POST then a guest submitted  If strMethod = "POST" Then    Dim strName, strComments    ' get the form data and format it    strName = FixForSQL(Request.Form("txtName"))    strComments = FixForSQL(Request.Form("txtComments"))    ' build the SQL string    strSQL = "INSERT INTO tblGuestbook (txtName, txtComments)"    strSQL = strSQL & " VALUES ('" & strName & "','" & strComments & "')"    ' execute the SQL    objCN.Execute(strSQL)  End If  ' create a SQL string to read all guest entries  strSQL = "SELECT * FROM tblGuestbook"  Set objRS = objCN.Execute(strSQL)%><html><head><title><%=strTitle%></title></head><body><h1><%=strTitle%></h1><%  ' only show the guestbook when there are entries  If Not objRS.EOF Then%><table border="1">  <tr>    <th>Name</th>    <th>Comments</th>  </tr><%  ' loop through the database  While Not objRS.EOF    With Response      .Write "<tr>" & vbCrLf      .Write "<td>" & FixForHTML(objRS("txtName")) & "</td>" & vbCrLf      .Write "<td>" & FixForHTML(objRS("txtComments")) & "</td>" & vbCrLf      .Write "</tr>" & vbCrLf    End With    objRS.MoveNext  Wend%></table><% End If %><form action="<%=strURL%>" method="POST">  <table>    <tr>      <td>Name</td>      <td><input type="text" name="txtName"><br>    </tr>    <tr>      <td>Comments</td>      <td><textarea name="txtComments"></textarea><br>    </tr>    <tr>      <td colspan="2"><input type="submit" value="Submit Comments">    </tr></form></body></html><%  Function FixForSQL(tmpText1)    Dim tmpText2    tmpText2 = tmpText1    tmpText2 = Replace(tmpText1,vbCrLf,Chr(13))    tmpText2 = Replace(tmpText2,Chr(39),String(2,39))    FixForSQL = tmpText2  End Function  Function FixForHTML(tmpText1)    Dim tmpText2    tmpText2 = tmpText1    tmpText2 = Replace(tmpText2,Chr(13),"<br>" & vbCrLf)    tmpText2 = Replace(tmpText2,Chr(9),"&#xa0;&#xa0;&#xa0;&#xa0;")    FixForHTML = tmpText2  End Function%>						
  2. Open Microsoft Access, create a new database, and then save it as Guestbook.mdb in the same folder as the above ASP page.Create a new table in the database named tblGuestbook with the following fields:
    Field NameData TypeField Size
    txtNameText50
    txtCommentsMemoN/A
    Save the table to the database, and then close Microsoft Access.
Testing the Guestbook Application
  1. When you browse the page for the first time, no data is displayed.When information has been submitted, any guestbook entries will appear.Entering apostrophes in names (for example O'Brien, O'Sullivan, and so on) or contractions (such as don't, can't, and so on) are properly formatted and stored in the database.
  2. When information has been submitted, any guestbook entries will appear.Entering apostrophes in names (for example O'Brien, O'Sullivan, and so on) or contractions (such as don't, can't, and so on) are properly formatted and stored in the database.
  3. Entering apostrophes in names (for example O'Brien, O'Sullivan, and so on) or contractions (such as don't, can't, and so on) are properly formatted and stored in the database.
References
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.For more information about Microsoft scripting technologies, visit the Microsoft Developer Network web site:
iis iis5 iis 5.0
Properties

Article ID: 246069 - Last Review: 06/19/2014 06:28:00 - Revision: 6.0

  • kbinfo KB246069
Feedback