June 13, 2011 at 6:56 am
using sql2k8 and classic asp I stumbled upon an problem that is not easy for me to tackle; it has something to do with special characters in a query.
example: select * from bo_cities where region='catalonië'
this give the desired result in the Management Studio but no result when called from an ASP page.
example: select * from bo_cities where region='balearen'
will give both in the Studio as in the website the result
it has to do with the special character but I can't figure it out or solve it. It shall be an setting but which one? The site is in charset "utf-8" and shows all special characters normally (that comes from the database; so fields types etc are correct).
I checked the web but I did not find any answer (perhaps I did not asked the right questions).
Hope somebody can point me in the right direction or better has a solutions..
thanx for your time and efford
June 13, 2011 at 7:05 am
Are u using nvarchar as the datatype of the column your returning ?
Also verify the collation of the columns
additonally when querying data operatoe = on a nvarchar column
the query should look like
select col from table
where col = N'textvalue'
June 13, 2011 at 9:04 am
yes the datatype of the column is nvarchar.
using the 'N' in the query does not change the result; nor in executing the query in Management Studio nor through executing in classic ASP.
June 13, 2011 at 9:11 am
are you using parameters from the asp page?
the datatype of the parameter is probably the issue in this case;
can you show us how the parameter is being passed to your CommandObject?
for example MyParam.Type = 200 would not allwo the high ascii characters like the [ë] in your query.
Dim MyCmd 'As ADODB.Command
Set MyCmd = Server.CreateObject("ADODB.Command")
MyCmd.CommandText = "dbo.SearchBookDetails "
MyCmd.CommandType = 4 'adCmdStoredProc
Dim MyParam 'As ADODB.Parameter
Set MyParam = Server.CreateObject("ADODB.Parameter")
MyParam.Name = "@NomGarage"
MyParam.Value = TRIM(Request.Form("BookTitle"))
MyParam.Size = 50
MyParam.Direction = 1 'adParamInput
MyParam.Type = 200 'adVarChar
MyCmd.Parameters.Append MyParam
Lowell
June 13, 2011 at 9:14 am
no parameters; just a straight query in classic ASP
sqlString = "select titel AS PLAATS from bo_plaats where me_online=1 and me_deleted=0 and gekoppeldeprovincie=(select bo_provincie.me_id from bo_provincie where bo_provincie.me_online=1 and bo_provincie.me_deleted=0 and bo_provincie.titel='" & tmpView & "')"
set sqlRS = sqlConnection.execute(sqlString)
where if the vaiable "tmpView" has the value without special characters like "bealaren" it just works fine and when it has the value 'Catalonië' it just returns nothing.
Again when I execute the same query directly in Management Studio both will give results.
June 13, 2011 at 9:24 am
yep implicit conversion:
also note your command is wide open to sql injection; you'd be better off switching to parameters.
add the N to the front of your command:
sqlString = "select titel AS PLAATS
from bo_plaats
where me_online=1
and me_deleted=0
and gekoppeldeprovincie
=(select bo_provincie.me_id
from bo_provincie
where bo_provincie.me_online=1
and bo_provincie.me_deleted=0
and bo_provincie.titel=N'" & tmpView & "')"
Lowell
June 13, 2011 at 9:24 am
Apart from the SQLInjection vulnarability of your query , I would start sql profiler to see what query text actually comes in for sqlserver.
Did you test your query using:
.... where bo_.. = N'xyz' ;
In both ssms and asp ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 13, 2011 at 9:36 am
as I did directly after "JAYANTH KURUP" has suggested it... I have also said that this had no effect both on the Management Studio or in ASP. the results stays the same.
also but thats another matter; in the templates are enough controls to ensure that SQ Injection is not/hardly possible 😛
in the project we use stored procedures.
But the question is really why there is a difference between results in the Management Studio and in ASP
June 13, 2011 at 9:40 am
In that case, I'd start a profiler trace and capture the actual command sqlserver receives
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 13, 2011 at 10:24 am
with the profiler I get the next query returned:
select titel AS PLAATS from bo_plaats where me_online=1 and me_deleted=0 and gekoppeldeprovincie=(select bo_provincie.me_id from bo_provincie where bo_provincie.me_online=1 and bo_provincie.me_deleted=0 and bo_provincie.titel=N'Catalonië')
where the special character e (with a trema; last charcter of the string "Catalonië") is replaced by a "ë"
Stil I get the same / required resultset in the Management Studio. It has to have something to do with code paging / language settings.. any ideas?
I can make it work by creating another way to get the required key (perhaps I will do that) but still I'm curious for the reason why the resultsets are different in Management Studio (proper result) and in ASP (no result at all)
June 13, 2011 at 11:04 am
i think it's all back to html and your form...clearly someone typed a value into a input type="text", right?
and all values in a form are encoded to html-acceptable values....
can you try this and see if that addresses the issue?
not sure which might be better...Server.UrlDecode or Server.HTMLDecode
sqlString = "select titel AS PLAATS
from bo_plaats
where me_online=1
and me_deleted=0
and gekoppeldeprovincie
=(select bo_provincie.me_id
from bo_provincie
where bo_provincie.me_online=1
and bo_provincie.me_deleted=0
and bo_provincie.titel=N'" & Server.HTMLDecode(tmpView) & "')"
Lowell
June 13, 2011 at 11:59 am
sorry classic ASP so the functions HTMLDecode and URLDecode are not avaiable. I have found some functions to do the trick (I understand what you mean) but the result stays the same.
June 13, 2011 at 1:06 pm
I'm not a asp/html addict myself, but as shown in the trace result, there is your problem.
So your queries from ssms and asp didn't match.
I'm sorry I cannot help out with this asp/html front end.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply