?
Solved

On my ASP form, text after an apostrophe doesn't post to my Access Database

Posted on 2003-02-21
14
Medium Priority
?
429 Views
Last Modified: 2008-03-04
If, when a user submits a form, the text includes an apostrophe, only text before the apostrophe will submit.  To see the page, visit http://www.marian.edu/ihelpdesk/request.asp to submit the form, then search by name on the right side of the page.  

-----------------------
The code
-----------------------

Below is what I think is the relevent code, though I may be wrong.  You can view the entire code at http://www.marian.edu/ihelpdesk/aspcode.txt

          ' this is where the information finally gets submitted to the database
          Name = request.form("Name")
          Building = request.form("Building")
          Room = request.form("Room")
          Extension = request.form("Extension")                              
          Email = request.form("Email")
          Subject = request.form("Subject")
          Urgency = request.form("Urgency")          
          Problem = request("Problem")
          Dim Conn, strProvider, idxProvider
          idxProvider="Provider=msidxs"
          strProvider = Application("strProvider")
          set conn = Server.CreateObject("ADODB.Connection")

     If conn.State = 0 Then
         conn.open strProvider
     End If
     ' ( 0 is closed, 1 is open )              
         
          SQLstmt = "INSERT INTO tblService_Request (Name,Building,Room,Extension,Email,Subject,Problem,Urgency)"
          SQLstmt = SQLstmt & " VALUES ("
          SQLstmt = SQLstmt & "'" & Name & "',"
          SQLstmt = SQLstmt & "'" & Building & "',"          
          SQLstmt = SQLstmt & "'" & Room & "',"
          SQLstmt = SQLstmt & "'" & Extension & "',"
          SQLstmt = SQLstmt & "'" & Email & "',"
          SQLstmt = SQLstmt & "'" & Subject & "',"
          SQLstmt = SQLstmt & "'" & Problem & "',"                    
          SQLstmt = SQLstmt & "'" & Urgency & "'"
          SQLstmt = SQLstmt & ")"


All help/comments are appreciated!!

Thanks,

Michelle
0
Comment
Question by:mfehling
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
14 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 7994162
Just replace your apostrophes with Chr(37), which are quote marks, eg:
SQLstmt = SQLstmt & Chr(37) & Problem & Chr(37) & ","

That will allow submitted text to include apostrophes.
0
 

Author Comment

by:mfehling
ID: 7998953
When I change the code to that, it returns the following error:

Microsoft OLE DB Provider for SQL Server error '80040e14'

Line 1: Incorrect syntax near 'I'.

/ihelpdesk/request.asp, line 246
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8001266
Comment out the lines that actually execute the SQL, and write out SQLstmt to screen so you can see what it contains, then paste it here - it'll be easier to see where the problem is.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Expert Comment

by:apollo18
ID: 8001878
Chr$(34) not Chr$(37)

Or type the quotation marks directly but twice (to indicate a literal character):

SQLstmt = SQLstmt & """" & Problem & ""","

If users start entering quotation marks too you might have to use a function to replace them with another character.





0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8002452
That's twice I did that yesterday - I managed to get it into my head that double quotes was 37, not 34...man, I really have to get more sleep.
0
 

Author Comment

by:mfehling
ID: 8036267
When I try

SQLstmt = SQLstmt & """" & Problem & ""","

or

SQLstmt = SQLstmt & Chr(34) & Problem & Chr(34) & ","

I get the following error:

Microsoft OLE DB Provider for SQL Server error '80040e14'

The name 'This isn' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

/ihelpdesk/request.asp, line 246
0
 

Author Comment

by:mfehling
ID: 8036297
Line 246 is

Set RS = conn.execute(SQLstmt)

Lines 246 to 269:

          Set RS = conn.execute(SQLstmt)
               If err.number>0 then
                  response.write "VBScript Errors Occured:" & "<P>"
              response.write "Error Number=" & err.number & "<P>"
              response.write "Error Descr.=" & err.description & "<P>"
              response.write "Help Context=" & err.helpcontext & "<P>"
              response.write "Help Path=" & err.helppath & "<P>"
              response.write "Native Error=" & err.nativeerror & "<P>"
              response.write "Source=" & err.source & "<P>"
              response.write "SQLState=" & err.sqlstate & "<P>"
             end if            
               IF conn.errors.count> 0 then
              response.write "Database Errors Occured" & "<P>"
              response.write SQLstmt & "<P>"
                  for counter= 0 to conn.errors.count
                   response.write "Error #" & conn.errors(counter).number & "<P>"
                   response.write "Error desc. -> " & conn.errors(counter).description & "<P>"
                  next
             else
               response.write "<font face='arial'><b>"
              response.write "The service request has been received.</b><p>"                    
              response.write "<a href='Queue.asp'>"
               response.write "View All Records</a></font>"
             end if
0
 

Expert Comment

by:apollo18
ID: 8038398
You're right, wrapping the input value in quotation marks does not eliminate the problem of truncation on an apostrophe.  The solution is to change the apostrophes in the input values to a double apostrophe.  SQL Server will put only one in your table, storing

"I can''t get no satisfaction"

as

"I can't get no satisfaction"

Apostrophes in variables can be doubled by either a built-in replace function or a created one.  Put each data field inside the function, like this

(built-in Replace function: Visual Basic script)
-----------------------------------------
SQLstmt = SQLstmt & "'" & Replace(Problem, "'", "''") & "',"


or you can add one such as this
SQLstmt = SQLstmt & "'" & FixApostrophe(Problem) & "',"

Visual Basic
-----------------------------------------
Function FixApostrophe(ByVal varValue As Variant) As Variant

    Dim b As Byte
    Dim strL As String, strR As String

     If IsNull(varValue) Then
         FixApostrophe = Null
     Else
         strR = varValue
         b = InStr(varValue, "'")
         While b > 0
            strL = strL & Left(strR, b) & "'"
            strR = Mid(strR, b + 1)
            b = InStr(strR, "'")
         Wend
    End If
   
    FixApostrophe = strL & strR

End Function

Visual Basic Script
-----------------------------------------
Function FixApostrophe(varValue)

    Dim b, strL, strR

     If IsNull(varValue) Then
         FixApostrophe = Null
     Else
         strR = varValue
         b = InStr(varValue, "'")
         While b > 0
            strL = strL & Left(strR, b) & "'"
            strR = Mid(strR, b + 1)
            b = InStr(strR, "'")
         Wend
    End If
   
    FixApostrophe = strL & strR

End Function
0
 

Author Comment

by:mfehling
ID: 8041971
When I tried:

SQLstmt = SQLstmt & "'" & Replace(Problem, "'", "''") & "',"

the data is still truncated on apostrophes.  When I tried the FixApostrophe approach, I receive the following error:

Microsoft VBScript compilation error '800a03ee'

Expected ')'

/ihelpdesk/request.asp, line 246

Function FixApostrophe(ByVal varValue As Variant) As Variant
--------------------------------------^
0
 

Expert Comment

by:apollo18
ID: 8045544
Sorry, I don't know what the problem is.  Since you're using VBScript, the Replace function should work.  

Are you sure you're applying the function to all input data fields?  Can you verify what is being fed to SQL Server by using a debug.print or something like this?

WScript.Echo "Problem field as modified: " & Replace(Problem, "'", "''")

Using a function to double the apostrophes works for me with SQL Server 7 but I don't have a server set up to test it on an ASP form.

By the way, the compilation error occurred with the FixApostrophe function because with VBScript you would need to use the second version function, different in that it does not declare variable data types:
 
Function FixApostrophe(varValue)

   Dim b, strL, strR
   ...

0
 

Author Comment

by:mfehling
ID: 8071621
I tried to use the Replace function to change the letter A to the letter B:

SQLstmt = SQLstmt & "'" & Replace(Problem, "a", "b") & "',"

After entering the string "I can't get no satisfaction," it still returned "I can."  Perhaps I am not calling the Replace function correctly?
0
 

Accepted Solution

by:
apollo18 earned 225 total points
ID: 8076258
I'm baffled.  You're calling the function correctly.  The results of

     Replace("I can't get no satisfaction", "a", "b") & "',"

should be:

     I cbnt get no sbtisfbction.

It looks as if the server is ignoring the function, but if it doesn't recognize it, it should give an error.  Are you sure you made the change in the active code, not a second, test copy of the code page in another location?

I doubt this is it, since I believe the problem is with SQL Server, not with Visual Basic Script, but you might try moving the function to where the text is first stored as a variable prior to it being built into the SQL insert statement.

Name = Replace(request.form("Name"), "'", "''")
Building = Replace(request.form("Building"), "'", "''")
Room = Replace(request.form("Room"), "'", "''")
Extension = request.form("Extension"), "'", "''")          
Email = Replace(request.form("Email"), "'", "''")

etc.
0
 

Author Comment

by:mfehling
ID: 8080178
I've fixed the problem.  I changed the code from

  Else
    response.write "<input type='hidden' name='"

to

  Else
    response.write "<input type='hidden' name="""

Thanks for all of your help! :-)
0
 

Author Comment

by:mfehling
ID: 8080195
I found the solution, but I want to close the question and thank you for all your help.  
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question