Solved

inserting ° into a narvarchar sql field?

Posted on 2009-05-20
9
285 Views
Last Modified: 2012-05-07
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
Comment
Question by:remedy_rider
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 24434279
See if maybe you are writing it wrong?
http://www.asciitable.com/

Carrzkiss
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24434290
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
 

Author Comment

by:remedy_rider
ID: 24434447
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
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 
LVL 31

Accepted Solution

by:
Wayne Barron earned 300 total points
ID: 24434565
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
 
LVL 26

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 50 total points
ID: 24434576
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
 
LVL 10

Assisted Solution

by:MaxOvrdrv2
MaxOvrdrv2 earned 150 total points
ID: 24434723
Server.HTMLEncode and Server.HTMLDecode are your best friends my friend...
0
 
LVL 10

Expert Comment

by:MaxOvrdrv2
ID: 24434769
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
 
LVL 10

Expert Comment

by:MaxOvrdrv2
ID: 24435026
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
 
LVL 10

Assisted Solution

by:MaxOvrdrv2
MaxOvrdrv2 earned 150 total points
ID: 24435221
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question