Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 439
  • Last Modified:

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

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
mfehling
Asked:
mfehling
  • 7
  • 4
  • 3
1 Solution
 
shanesuebsahakarnCommented:
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
 
mfehlingAuthor Commented:
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
 
shanesuebsahakarnCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
apollo18Commented:
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
 
shanesuebsahakarnCommented:
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
 
mfehlingAuthor Commented:
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
 
mfehlingAuthor Commented:
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
 
apollo18Commented:
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
 
mfehlingAuthor Commented:
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
 
apollo18Commented:
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
 
mfehlingAuthor Commented:
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
 
apollo18Commented:
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
 
mfehlingAuthor Commented:
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
 
mfehlingAuthor Commented:
I found the solution, but I want to close the question and thank you for all your help.  
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now