locked
Apostrophe handled differently between environments RRS feed

  • Question

  • Hi,

    I have an issue whereby apostrophes seem to handled differently between two different SQL environments. Essential I have a piece of code that takes the contents of a text box within an ASP page and inserts them into a MS SQL DB. If I run this code on a SQL 2008 R2 platform the code runs fine. If I run this on a SQL 2005 platform it breaks the code and an error message is generated. Are there some specific settings in SQL 2005 that need to be set to handle the insertion of apostrophes that I have missed out? I'm perplexed as to why it works in one environment but not another.  

    Tuesday, August 23, 2016 10:57 AM

Answers

  •     I've supplied the code and answered peoples queries so I don't know why you have a bad attitude. I hate it when douchebags like you reply to peoples questions with condescending remarks. Everyone else bar you has replied in a helpful and respectful manner. You are obviously not someone who enjoys helping people so why even comment and waste your oh so precious time? Absolutely pathetic behaviour.

    I certrainly enjoy helping people. Who also cares to make an effort to help themselves and help others to help them. It is however far too often that people request help without giving sufficient information.

    You may have supplied code, but as we have pointed out that code does not have any problem, so you will need to dig deeper. And you are the one who will need to do the digging, because you are the only one with access to the system. We have also supplied information on how you should do that digging.

    But with the information you have supplied so far, we will not be able to get any further.

    Wednesday, August 24, 2016 9:05 AM

All replies

  • Can you show the piece of code  along  with error message?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, August 23, 2016 11:14 AM
    Answerer
  • If Request.Form("txtEditRestricted") = "False" Then
    objCmd.Parameters("@Restrict") = 0
    objCmd.Parameters("@desc").Value = Request.Form("txtDescription")
    objCmd.Parameters("@result").Value = Request.Form("txtResult")
    Else
    objCmd.Parameters("@Restrict") = 1
    objCmd.Parameters("@desc").Value = "Ignore"
    objCmd.Parameters("@result").Value = Request.Form("txtResult")
    End If

    The error page is a standard "The Website cannot display the page" error that is generated by IIS. What should be displayed is a your results have been saved page.


    Tuesday, August 23, 2016 11:34 AM
  • It's good that you are using parameters but the important piece missing from this code snippet is the actual query you are running. 

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Tuesday, August 23, 2016 12:18 PM
  • Where is the actual sql query?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, August 23, 2016 12:56 PM
    Answerer
  • Can you kindly post the final T-SQL query? you just showed us .aspx code
    Tuesday, August 23, 2016 1:03 PM
  • DECLARE @actionBeingCompleted BIT
    SET @actionBeingCompleted = 0
    IF @pStatus IN ('Completed', 'Closed') AND EXISTS ( SELECT 1
    FROM tblAction
    WHERE actionID = @pActionID AND
    status NOT IN ('Completed', 'Closed'))
    SET @actionBeingCompleted = 1

    -- Update the action record.
    IF @Restrict = 0 BEGIN

    UPDATE tblAction SET tblAction.Title =  @pTitle , tblAction.Type =  @pType , tblAction.ChangeID =  @pChangeID , 
    tblAction.PriorActionID =  @pPriorActionID , 
    tblAction.SuccessorActionID =  @pSuccessorActionID , tblAction.ActionType =  @pActionType , 
    tblAction.TargetDate =  LEFT(Convert(DateTime, @pTargetDate, 103), 11) , 
    tblAction.IssueDate =  LEFT(Convert(DateTime, @pIssueDate, 103), 11) , 
    tblAction.CompletedDate =  @pCompletedDate , 
    tblAction.Status =  @pStatus , 
    tblAction.MessageID =  @pMessageID , tblAction.NotifyDone =  @pNotifyDone , 
    tblAction.Approved =  @pApproved , 
    tblAction.AdvanceDate =  LEFT(Convert(DateTime, @pAdvanceDate, 103), 11) , 
    tblAction.OverdueWarn =  @pOverdueWarn , tblAction.OverdueFrequency =  @pOverdueFrequency , 
    tblAction.OverdueCopy =  @pOverdueCopy , tblAction.ReceiverID =  @pReceiverID , tblAction.SenderID =  @pSenderID,
    tblAction.CommentOnClosure = @pCOC, tblAction.UserCompletionDate = @pUCD, tblAction.AbortChange = @pAC, tblAction.CreateNewAction = @pCNA,
    tblAction.RedirectAction = @pRA, tblAction.IncreasePriority = @pIP, tblAction.Priority = @pPRI, tblAction.AttachDocument = @pAD,
    tblAction.RepeatPath = @pRP, tblAction.NotificationGroupID = @pNG, tblAction.Acknowledgement = @pACK, tblAction.[Description] = @desc, tblAction.Result = @result

    WHERE tblAction.ActionID= @pActionID

    END
    ELSE BEGIN

    UPDATE tblAction SET tblAction.Title =  @pTitle , tblAction.Type =  @pType , tblAction.ChangeID =  @pChangeID , 
    tblAction.PriorActionID =  @pPriorActionID , 
    tblAction.SuccessorActionID =  @pSuccessorActionID , tblAction.ActionType =  @pActionType , 
    tblAction.TargetDate =  LEFT(Convert(DateTime, @pTargetDate, 103), 11) , 
    tblAction.IssueDate =  LEFT(Convert(DateTime, @pIssueDate, 103), 11) , 


    -- tblAction.CompletedDate = LEFT(Convert(DateTime, @pCompletedDate, 103), 11),
    tblAction.CompletedDate = @pCompletedDate,

    tblAction.Status =  @pStatus , 
    tblAction.MessageID =  @pMessageID , tblAction.NotifyDone =  @pNotifyDone , 
    tblAction.Approved =  @pApproved , 
    tblAction.AdvanceDate =  LEFT(Convert(DateTime, @pAdvanceDate, 103), 11) , 
    tblAction.OverdueWarn =  @pOverdueWarn , tblAction.OverdueFrequency =  @pOverdueFrequency , 
    tblAction.OverdueCopy =  @pOverdueCopy , tblAction.ReceiverID =  @pReceiverID , tblAction.SenderID =  @pSenderID,
    tblAction.CommentOnClosure = @pCOC, tblAction.UserCompletionDate = @pUCD, tblAction.AbortChange = @pAC, tblAction.CreateNewAction = @pCNA,
    tblAction.RedirectAction = @pRA, tblAction.IncreasePriority = @pIP, tblAction.Priority = @pPRI, tblAction.AttachDocument = @pAD,
    tblAction.RepeatPath = @pRP, tblAction.NotificationGroupID = @pNG, tblAction.Acknowledgement = @pACK, tblAction.Result = @result

    WHERE tblAction.ActionID= @pActionID

    END

    -- Check if the job associated with this action needs to be auto-closed if required.
    IF @actionBeingCompleted = 1
    BEGIN
    EXEC qupdCheckIfJobNeedsToBeAutoClosed @pActionID
    EXEC qupdAuditTrailCompletedActionInJobTracker @pActionID
    END


    IF @pStatus IN ('Completed', 'Closed')
    BEGIN
    DECLARE @RFCID INTEGER,
    @jobModule INTEGER,
    @jobID INTEGER
    SELECT @RFCID = R.RFCID,
    @jobModule = jobModuleID,
    @jobID = jobID
    FROM tblAction A
    LEFT JOIN tblChange C ON A.changeID = C.[ID]
    LEFT JOIN tblRFC R ON C.parentID = R.RFCID
    WHERE actionID = @pActionID
    IF @jobModule = 9
    INSERT INTO IssTblActiveDocuments
    SELECT @pActionID,
    [ID]
    FROM IssTblAssDocs
    WHERE rowID = @jobID AND
    active = 1
    END

    Also please note I made an error in my initial post. The environment that fails is SQL 2012 NOT 2005

    Tuesday, August 23, 2016 1:13 PM
  • Can you test your code against SQL Server 2014 or 2016?
    Tuesday, August 23, 2016 1:25 PM
  • The error page is a standard "The Website cannot display the page" error that is generated by IIS. What should be displayed is a your results have been saved page.

    How do you know then that the problem is with the apostrophes? For all I care, this may not even be an SQL problem - but may tell how little I know about ASP .NET, that is nothing.

    In any case, if you want help with SQL, we need to at least see the error message. If you don't know how to get that error message, the advice I can give from the SQL side of things is to run Profiler and caputre the events SP:StmtStarting, Error:Exception and Error:UserMessage so that you can see what the error message is. Although, I would suggest it would be better to add exception-handling code with better diagnostics in the ASP code. How to that, however, is a question for a different forum.

    • Proposed as answer by Naomi N Tuesday, August 23, 2016 4:28 PM
    • Unproposed as answer by LordRamlington Wednesday, August 24, 2016 7:27 AM
    Tuesday, August 23, 2016 1:25 PM
  • I don't have those environments available unfortunately
    Tuesday, August 23, 2016 1:34 PM
  • I know its apostrophes because if I enter text into the box without one it works and if I enter text with one it fails.
    Tuesday, August 23, 2016 1:35 PM
  • Can you check your sql code in both SQL Server 2005 and 2008 R2 again? The symptom looks like the input text value with the apostrophe is not passed in with parameter.
    • Edited by Jingyang Li Tuesday, August 23, 2016 1:43 PM
    • Proposed as answer by Naomi N Tuesday, August 23, 2016 4:28 PM
    Tuesday, August 23, 2016 1:41 PM
  • Single Apostrophe has Special Meaning in SQL. So you Text isnt terminated.

    You have to escape a single Apostrophe.

    Have a look here for further Infos:

    http://blog.sqlauthority.com/2008/02/17/sql-server-how-to-escape-single-quotes-fix-error-105-unclosed-quotation-mark-after-the-character-string/

    Tuesday, August 23, 2016 1:46 PM
  • Are you running this code locally? Do you have full developer's SQL Server 2012 version installed?

    If that's the case, then I second Erland's recommendation. The best way to find out what's going on is to run SQL Profiler and then examine the command being passed to SQL Server.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, August 23, 2016 4:30 PM
  • You have to Escape your inputted text.

    CREATE FUNCTION [dbo].[fnEscapeSingleQuote]
        (@StringToCheck NVARCHAR(MAX))
    RETURNS NVARCHAR(MAX)
    AS
    BEGIN
        DECLARE @Result NVARCHAR(MAX)
        SELECT @Result = REPLACE(@StringToCheck, CHAR(39), CHAR(39) + CHAR(39))
        RETURN @Result
    END


    My Technet Articles

    If you like this or another reply, vote it up!
    If you think this or another reply answers the original question, mark it or propose it as an answer.


    Mauricio Feijo
    www.mauriciofeijo.com


    Tuesday, August 23, 2016 4:36 PM
  • I know its apostrophes because if I enter text into the box without one it works and if I enter text with one it fails.

    So much for helping us to help you.

    What we can say is that the code you posted have no issues. But there are calls to two stored procedures and there could be triggers on the table. Any of these could contain poorly written dynamic SQL.

    What we can also say is that there are no differences between SQL 2008 or SQL 2012 in this regard, nor is it likely that it related to some setting. Thus, it is more likley that the bad code is not present on SQL 2008.

    Tuesday, August 23, 2016 9:49 PM
  • So much for helping us to help you.

        What are you talking about?

        I've supplied the code and answered peoples queries so I don't know why you have a bad attitude. I hate it when douchebags like you reply to peoples questions with condescending remarks. Everyone else bar you has replied in a helpful and respectful manner. You are obviously not someone who enjoys helping people so why even comment and waste your oh so precious time? Absolutely pathetic behaviour.

    Wednesday, August 24, 2016 7:25 AM
  •     I've supplied the code and answered peoples queries so I don't know why you have a bad attitude. I hate it when douchebags like you reply to peoples questions with condescending remarks. Everyone else bar you has replied in a helpful and respectful manner. You are obviously not someone who enjoys helping people so why even comment and waste your oh so precious time? Absolutely pathetic behaviour.

    I certrainly enjoy helping people. Who also cares to make an effort to help themselves and help others to help them. It is however far too often that people request help without giving sufficient information.

    You may have supplied code, but as we have pointed out that code does not have any problem, so you will need to dig deeper. And you are the one who will need to do the digging, because you are the only one with access to the system. We have also supplied information on how you should do that digging.

    But with the information you have supplied so far, we will not be able to get any further.

    Wednesday, August 24, 2016 9:05 AM
  • Erland is one of the most helpful people in this forum and his replies were to the point. You also ignored my and Erland's advice to run SQL Server Profiler and find out the exact command being executed. If we would see the command, then the answer will be obvious.

    Also, you didn't provide full code of your web page. We do see you're using parameter, which is good, but I'd like to see more code there.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, August 24, 2016 10:37 AM
  • CREATE FUNCTION [dbo].[fnEscapeSingleQuote]
        (@StringToCheck NVARCHAR(MAX))
    RETURNS NVARCHAR(MAX)
    AS
    BEGIN
        DECLARE @Result NVARCHAR(MAX)
        SELECT @Result = REPLACE(@StringToCheck, CHAR(39), CHAR(39) + CHAR(39))
        RETURN @Result
    END

    My Technet Articles

    If you like this or another reply, vote it up!
    If you think this or another reply answers the original question, mark it or propose it as an answer.


    Mauricio Feijo
    www.mauriciofeijo.com

    Wednesday, August 24, 2016 2:33 PM