Returning recordsets with an apostrophe

Results 1 to 2 of 2

Thread: Returning recordsets with an apostrophe

  1. #1
    Join Date
    Dec 1969

    Default Returning recordsets with an apostrophe

    Hello,<BR><BR>I have drop down box that retrieves a recordset and displays it. The code is as follows:<BR>-------------------------------------------------------------<BR>Dim oRSCityListingsNF<BR>Set oRSCityListingsNF = Server.CreateObject("ADODB.recordset")<BR>oRSCityL istingsNF.Open "Select distinct city from tblDistinct_Postal_Join_w_Prov_city where province = &#039;NF&#039; order by city", oConn <BR> <BR>Do while NOT oRSCityListingsNF.EOF<BR>Response.write "&#060;option value= &#039;" & oRSCityListingsNF("city") & "&#039;&#062;" <BR>Response.write oRSCityListingsNF("city") & "&#060;/option&#062;"<BR>oRSCityListingsNF.MoveNext<BR>Loo p <BR>oRSCityListingsNF.Close <BR>set oRSCityListingsNF = nothing <BR>----------------------------------------------------------------<BR><BR>If the code is jumble read on. . .<BR><BR>displaying the names of cities with apostrophe&#039;s using the above code cannot be done (or at least will not throw an error). <BR><BR>To correct the issue I simply take out the single quotes around the recordset object (which in this case changes:<BR>"&#060;option value= &#039;" & oRSCityListingsNF("city") & "&#039; <BR>to the following:<BR>"&#060;option value= " & oRSCityListingsNF("city") & "<BR><BR>Now a city such as St.John&#039;s will be displayed in the drop down box but here is the catch.<BR><BR>I need to submit the recordsets retrieved value (which in this case is "St.John&#039;s") back to the database to another stored procedure (i.e sp_recieveValue(@City) select * from table where city = @city).<BR><BR>The problem that arises is that without the single quotes, only "St" is sent in the forms submit variable. For whatever reason, if there is a space in the retrieved recordset value, the form only passes a value up until a space is encountered.<BR><BR>If no space is encountered there is no problem. The form works if a city such as Gander is retrieved and submited back to the DB, but if a space is encountered such as found in the city of Mount Pearl, only the value "Mount" is submitted to the asp page (which then passes is back to the server for more work).<BR><BR>Is there another way of writing the above form to eliminate this problem?<BR><BR>Remember, the problem is not just displaying a value with an apostrophe, but submitting that retrieved recordset value back to a stored procedure. <BR><BR>Any help would be greatly appreciated.<BR>Dez.

  2. #2
    Join Date
    Dec 1969

    Default RE: Returning recordsets with an apostrophe

    Change your lines:<BR><BR>Response.write "&#060;option value= &#039;" & oRSCityListingsNF("city") & "&#039;&#062;" <BR>Response.write oRSCityListingsNF("city") & "&#060;/option&#062;"<BR><BR>To:<BR>Response.Write "&#060;option value=""" & Server.HTMLEncode(oRSCityListingsNF("city")) & """&#062;" & oRSCityListingsNF("city") & "&#060;/option&#062;"<BR><BR>Of course this will blow up if city is null.<BR><BR>-doug

Posting Permissions

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