Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2003
    Posts
    17

    Thumbs down Unanswered: Passing a string containing a single quote

    I am trying to pass a string that contains a single quote. I can do it if I put double quotes around it, however, if the string is larger then 128 characters then I get an error that says:

    Run-time error '-2147217900 (80040e14)':
    The identifier that starts with '01234567890...' is too long. Maximum length is 128"

    Which, I think, via my research, is telling me that "it" thinks that the column name is too long. Regardless of what that does or does not mean, I have worked through using the data types and setting quote identifyer on and off, etc. It is none of thoes. I have traced it down to that single quote. I can run the stored procedure directly from the Access window by double clicking on it and when it prompts me for the parameter I can enter the string containing the single quote and it accepts it just fine, so it is not the table design or the actual stored procedure.

    If I pass the string using a single quote it is OK, but the single quote will give the error about being too long when there is a single quote in the string and it will sstart with the "2" so i know it is that single quote.

    I have tried to wrap the string in double double quotes and double single quotes, and nothing seems to work.

    How can I pass the string that contains a single quote?!!

    I am using Access 2000 (.ADP not .MDB) and MS SQL 7.

    =========================
    Dim strInfo As String
    strInfo = "01'2000012345678901234567890123456789012345678901 23456789012345678901234567890123456789012345678901 2345678901234567890123456789012345678"

    ' strInfo = Chr(39) & Chr(34) & strInfo & Chr(34) & Chr(39)
    strInfo = Chr(39) & strInfo & Chr(39)

    Dim cnADOConnectionObject As ADODB.Connection
    Set cnADOConnectionObject = New ADODB.Connection

    cnADOConnectionObject.ConnectionString = "Provider=SQLOLEDB;Data Source=MERCURY;Initial Catalog = pm-data;User ID=mitch; Password=mitch;"
    cnADOConnectionObject.Open
    '=====================
    'Chr(39)=' chr(34)= "

    Call cnADOConnectionObject.Execute("spLogAddTest" & " @mystrInfo=" & strInfo)

    'To Disconnect:
    cnADOConnectionObject.Close
    'Set rs = Nothing
    Set cnADOConnectionObject = Nothing
    Thanks for your help, Mitch

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    start by fireing up Profiler so you can look at the commands you are sending your server. I learned some interesting things about ADO this was.

    Single quotes can be handdled in two ways:

    select 'Can''t tell if this will work'
    select "Can't tell is this will work"

    I am sure there are more but these are the most common. Next I would look at youe execute statment, unless I am wrong you are sending the following commnd to your server...

    Code:
    spLogAddTest @mystrInfo= 01'20000123456789012345678901234567890123456789012
    34567890123456789012345678901234567890123456789012
    345678901234567890123456789012345678
    Could you change your .Execute statment to "spLogAddTest" & " @mystrInfo=""" & strInfo & """"

    or add replace(strinfo, "'","''")
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Mar 2003
    Posts
    17

    Talking I got this to work for me.

    I was able to get this to work using the following code:

    =======================

    strInfo = "66'3300000000000000000000000000000000123456789012 34567890123456789012345678901234567890123456789012 34567890123456789012345678901234567890123456789012 3456789012345678"

    strSingleQ = Chr(39)
    strDoubleQ = Chr(39) & Chr(39)
    strInfo = Replace(strInfo, strSingleQ, strDoubleQ, , , vbTextCompare)

    Call cnADOConnectionObject.Execute("spLogAddTest" & " @mystrInfo=" & Chr(39) & strInfo & Chr(39))
    Thanks for your help, Mitch

  4. #4
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Paul Young,
    "Can't tell is this will work" is a column name.

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    ispaleny,
    "Can't tell is this will work" is a column name, or object identifier, when SET QUOTED_IDENTIFIER is ON.

    "Can't tell is this will work" is a literal string when SET QUOTED_IDENTIFIER is OFF.
    Paul Young
    (Knowledge is power! Get some!)

  6. #6
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    So it is setting dependent. I wonder mistux get it to work, he has QUOTED_IDENTIFIER OFF by default on both servers.

    I never have had seen it before you posted. It violates SQL-92 rules. Pre-MSSQL2K syntax ?

  7. #7
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    I think this goes back to Sybase Ver 4.2 when Microsoft ripped off, oops I mean briefly collaborated with Sybase to make SQL Server.

    I should have stated that using double quotation marks for literal strings requires a non-standard setting.

    Perhaps I should adopt a practice of posting my QA settings when offering code examples.

    IMHO, braces "[]" are better delimiters for non-standard object identifiers. But then again I truly feel that Null equals Null.
    Last edited by Paul Young; 04-02-03 at 09:12.
    Paul Young
    (Knowledge is power! Get some!)

  8. #8
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Paul Young,

    NULL can never equals NULL.
    Why other programming languages do not have better NULL support.
    VB6 had a variant datatype and it don't like variants, but wkat about nullable integer, boolean or string?

    Also more kinds of NULL would help. Sometimes it is "missing value" other times "not existing value" and others.

    But let's stop talking about NULLs. I have found a solution for "Ad hoc non-trusted user access to OLE DB provider". Many Webs have pages written about it, but none works. I know how to do it, but I don't know how it works. Can you look at it at http://www.experts-exchange.com/Data..._20565290.html

    I reply to many questions. But when I ask, nobody replies.

    ( If you cannot access that site, I will repost it to dbforums.com. )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •