• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 300
  • Last Modified:

inserting ° into a narvarchar sql field?

Hello, I just noticed I have a problem with an insert of a degree character.

when I update a record for example: 350.5@ 200°
it will put the degree symbol in, but when I go back to the edit page and make another change it converts and adds the symbol A with a "~" on top?  Any ideas as to what would cause this?
The form field:
<input type="text" name="Dist_1" value="<%= rs("Dist_1") %>" size="25">
 
The insert script:
Set rs = Server.CreateObject("ADODB.RecordSet")
rs.Open "SPEC_SHEETS", conn, 3, 3 
	sql = "Update SPEC_SHEETS Set note='"& Replace(request.Form("note"), "'", "''") & "'"
	sql = sql & ", spec_sheet_name='"& Replace(request.Form("spec_sheet_name"), "'", "''") & "'"
	sql = sql & ", gravity='"& Replace(request.Form("gravity"), "'", "''") & "'"
	sql = sql & ", color='"& Replace(request.Form("color"), "'", "''") & "'"
	sql = sql & ", api_gravity='"& Replace(request.Form("api_gravity"), "'", "''") & "'"
	sql = sql & ", leaded='" & Cbool(request.form("leaded")) & "'"
	sql = sql & ", Lead_content='"& Replace(request.Form("Lead_content"), "'", "''") & "'"
	sql = sql & ", Oygenated='" & Cbool(request.form("Oygenated")) & "'"
	sql = sql & ", Purity='"& Replace(request.Form("Purity"), "'", "''") & "'"
	sql = sql & ", MON='"& Replace(request.Form("MON"), "'", "''") & "'"
	sql = sql & ", RON='"& Replace(request.Form("RON"), "'", "''") & "'"
	sql = sql & ", RM2='"& Replace(request.Form("RM2"), "'", "''") & "'"
	sql = sql & ", RVP='"& Replace(request.Form("RVP"), "'", "''") & "'"
	sql = sql & ", OX_stab='"& Replace(request.Form("OX_stab"), "'", "''") & "'"
	sql = sql & ", OX_content='"& Replace(request.Form("OX_content"), "'", "''") & "'"
	sql = sql & ", Dist_1='"& Replace(request.Form("Dist_1"), "'", "''") & "'"
	sql = sql & ", Dist_2='"& Replace(request.Form("Dist_2"), "'", "''") & "'"
	sql = sql & ", Dist_3='"& Replace(request.Form("Dist_3"), "'", "''") & "'"
	sql = sql & ", Dist_4='"& Replace(request.Form("Dist_4"), "'", "''") & "'"
	sql = sql & ", Dist_5='"& Replace(request.Form("Dist_5"), "'", "''") & "'"
	sql = sql & ", Production='"& Replace(request.Form("Production"), "'", "''") & "'"
	sql = sql & ", Avail='"& Replace(request.Form("Avail"), "'", "''") & "'"
	sql = sql & ", Rev='"& Replace(request.Form("Rev"), "'", "''") & "'"
	sql = sql & ", Left_Column='" & Cbool(request.form("Left_Column")) & "'"
	sql = sql & ", msds='"& Replace(request.Form("msds"), "'", "''") & "'"
	sql = sql & ", tech_bull='"& Replace(request.Form("tech_bull"), "'", "''") & "'"
	sql = sql & " where id="& regEx.Replace(request("id"), "")
	response.write sql

Open in new window

0
remedy_rider
Asked:
remedy_rider
  • 4
  • 2
  • 2
  • +1
4 Solutions
 
Wayne BarronCommented:
See if maybe you are writing it wrong?
http://www.asciitable.com/

Carrzkiss
0
 
Chris LuttrellSenior Database ArchitectCommented:
Make sure your insert/update statement is constructed where you end up with yourNVarcharColumn=N'your value with degree character', the N is the key.
0
 
remedy_riderAuthor Commented:
We use dotdefender on our sever and it's not allowing the "#" to be inserted. arrrgh.
it looks like it should be &#167;  is there another way to input ascii characters?

CGLuttrell, i'm not sure i understand. My line of code on the insert script is below. Do i need to add something to that line?



sql = sql & ", Dist_1='"& Replace(request.Form("Dist_1"), "'", "''") & "'"

Open in new window

0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
Wayne BarronCommented:
can't you do a replace function?
Change the # to another character to insert into the DB
And then use the same function in reverse to display it to the page?

Example

rs(replace(MyChar,"#","^"))

Then when called back out
rs(replace(MyChar,"^","#"))

I gave it that little   ^  (The 6 key on top of keyboard)
As chances of you having that character appear in your db is slim-to-none.

Good Luck
Carrzkiss
0
 
Chris LuttrellSenior Database ArchitectCommented:
to preserve the special characters in the nvarchar field in the database you have to prefix the literal string with the N indicator like I have done in your example below, see it that helps.
sql = sql & ", Dist_1=N'"& Replace(request.Form("Dist_1"), "'", "''") & "'"

Open in new window

0
 
MaxOvrdrv2Commented:
Server.HTMLEncode and Server.HTMLDecode are your best friends my friend...
0
 
MaxOvrdrv2Commented:
as an example using your code above:
WHEN SAVING TO THE DB:
 
sql = sql & ", Production='"& Server.HTMLEncode(Replace(request.Form("Production"), "'", "''")) & "'"
 
WHEN DISPLAYING IN A TEXT FIELD:
<textarea><%=Server.HTMLDEcode(RecordSet("Production"))%></textarea>
 
WHEN DISPLAYING AS NORMAL TEXT ON AN HTML PAGE:
 
<p><%=RecordSet("Production")%></p>

Open in new window

0
 
MaxOvrdrv2Commented:
and if your DB doesn't allow you t use the # characters, you can user URLEncode and URLDecode instead of HTMLEncode and HTMLDecode... replaces the # with %, BUT, you will have to use Decode in all examples above... as shown below:


WHEN SAVING TO THE DB:
 
sql = sql & ", Production='"& Server.URLEncode(Replace(request.Form("Production"), "'", "''")) & "'"
 
WHEN DISPLAYING IN A TEXT FIELD:
<textarea><%=Server.URLDecode(RecordSet("Production"))%></textarea>
 
WHEN DISPLAYING AS NORMAL TEXT ON AN HTML PAGE:
 
<p><%=Server.HTMLEncode(Server.URLDecode(RecordSet("Production")))%></p>

Open in new window

0
 
MaxOvrdrv2Commented:
and if you want to see what the whole thing would do with your content, try this in a new ASP page, it should give you a good idea of what the content looks like :

(of course you need to modify the Action on the form to the test page)


<%
if Request.Form("testdata")<>"" then
	response.write("<table>")
	response.write("<tr>")
	response.write("<td>Text Entered (no changes):</td>")
	response.write("<td>" & Request.Form("testdata") & "</td>")
	response.write("</tr><tr>")
	response.write("<td>Text after Server.URLEncode() applied:</td>")
	response.write("<td>" & Server.URLEncode(Request.Form("testdata")) & "</td>")
	response.write("</tr><tr>")
	response.write("<td>URLDecoded text after URLEncode originally applied:</td>")
	response.write("<td>" & Server.UrlDecode(Server.URLEncode(Request.Form("testdata"))) & "</td>")
	response.write("</tr><tr>")
	response.write("<td>HTMLEncoded text after URLDecode applied:</td>")
	response.write("<td>" & Server.HTMLEncode(Server.UrlDecode(Server.URLEncode(Request.Form("testdata")))) & "</td>")
	response.write("</tr></table>")
end if
%>

Open in new window

0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now