Professional Web Applications Themes

Single quote (') problem in SQL Server query - Microsoft SQL / MS SQL Server

I am trying to run a simple select query but it bombs out because one of the fields may contain a single quote character. The field is a street name field and streets starting with "O'" bomb out. When there is no single quote, the query works fine. Here is my query: Set tmpRSet2 = conConn2.OpenRecordset("SELECT Street + ', ' + Suburb + ', ' + State + ', ' + Cast (Postcode as char) as [CombinedFields], ID, Street, Suburb, State, Postcode, Area, STD from Addresses where (Street + ', ' + Suburb + ', ' + State + ', ...

  1. #1

    Default Single quote (') problem in SQL Server query

    I am trying to run a simple select query but it bombs out
    because one of the fields may contain a single quote
    character. The field is a street name field and streets
    starting with "O'" bomb out. When there is no single
    quote, the query works fine.

    Here is my query:
    Set tmpRSet2 = conConn2.OpenRecordset("SELECT
    Street + ', ' + Suburb + ', ' + State + ', ' + Cast
    (Postcode as char) as [CombinedFields], ID, Street,
    Suburb, State, Postcode, Area, STD from Addresses where
    (Street + ', ' + Suburb + ', ' + State + ', ' + Cast
    (Postcode as char) = '" & Trim((lstAddress.Text)) & "')",
    dbOpenDynaset)

    ---
    The fieldname in question is called "Street" above

    If anyone has any suggestions how I could get it to
    ignore the single quote, I would really appreciate it.

    Thanks
    Ben
    Ben Guest

  2. #2

    Default Re: Single quote (') problem in SQL Server query

    Use 'O''hara'

    --
    Michael Culley


    "Ben" <cbr.com.au> wrote in message
    news:03bb01c349b1$70500fc0$gbl... 


    Michael Guest

  3. #3

    Default Re: Single quote (') problem in SQL Server query

    Probably a better method is to use a stored procedure and pass the value
    into that. Depending on your language you can usually do something like

    MyParameter.Value = "o'hara"

    --
    Michael Culley


    "Michael Culley" <com.au> wrote in message
    news:phx.gbl... 
    >
    >[/ref]


    Michael Guest

Similar Threads

  1. CF7 query update with single quote
    By achab23 in forum Coldfusion Database Access
    Replies: 5
    Last Post: April 13th, 05:37 PM
  2. Single & Double Quote Problem in Database Insert/Update
    By smnbin in forum Coldfusion Database Access
    Replies: 3
    Last Post: January 27th, 09:16 AM
  3. Single Quote Problem in Excel to Export
    By Prasad Dannani in forum ASP.NET Data Grid Control
    Replies: 3
    Last Post: October 27th, 12:12 AM
  4. Query of Query with single quote (')
    By shp.jc in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: April 29th, 02:19 PM
  5. double single quote problem in cfquery
    By Bigio in forum Coldfusion Database Access
    Replies: 7
    Last Post: April 25th, 09:46 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139