Displaying data retrieved from SQL Server

[nicky77]

Hi there, I'm having trouble with £ signs and apostrophes when I'm retrieving data from my SQL Server database then displaying it on a web page. Is there a simple way round this type of problem, i.e using the Replace function? I've struggled to find an answer on the web so far and am now to using ASP and SQL Server. My columns are stored as varchar on the database.

thanks in advance for any help

[Spudhead]

It's a bit difficult to suggest a solution without seeing what code you're using to put data in or get data out of the database, or what display issues you're actually having.

[nicky77]

apologies - here are the relevant asp lines for retrieving a salary from the database

'Get job data
rs.Open "select * from vacancy where status = 'active' order by id desc", dbConn, 1 
Response.Write("<h4>Salary: " & rs("salary") & "</h4>")

and this is the code for inserting into the database, where salaryInput is a variable storing the value submitted in a form field

sqltext =  "insert into vacancy (title,....,creator) values ("
sqltext = sqltext & "'" & replace(salaryInput, "'", "''") & "', "
'the rest of the inserts
sqltext = sqltext & "'" & replace(Session("creator_id"), "'", "''") & "') "
dbConn.Execute(sqltext)

the problem with displaying the £ sign is in Safari, which displays a � instead.

the problem with the apostrophe is when i am retrieving data from the database to use as the title tag for an image - if there is an apostrophe in the image description, then the title tag cuts off the rest of the text after the apostrophe

i thought that both of these problems may be a result of the same issue, in terms of how i'm handling the data? hope this make more sense

[Spudhead]

Hmm. Yeah, you should be able to simply replace those characters.

Instead of this:

Response.Write("<h4>Salary: " & rs("salary") & "</h4>")

try this:

sSalary = rs.Fields("salary")
if sSalary <> "" then sSalary = replace(sSalary,"£","&pound;")
Response.Write("<h4>Salary: " & sSalary & "</h4>")

and replace an apostrophe with ’

[nicky77]

thanks spudhead - problem solved!