Problems when passing a sql statement containing non-english characters to a ADO.Recordset object

Dear Sir/Madam,

I am working on a web site which talks to a sql server in the backend.
I need to make the web page searchable on non-english characters.

So on the main page you can type non-english characters in a textbox
and then click on "Submit" button. On the next page, I will display
this non-english characters, run a sql statement, display the results
of this sql statement on the page.

Following is the sample sql statement:
select english_text from tblTest where translation =3D '我们'

Following is the code to get the result set:
Set rs =3D Server.CreateObject("ADODB.Recordset")
rs.Open "select english_text from tblTest where translation =3D
'我们'", conn, 3, 3

When I manually run this sql statement in SQL query analyser, it
returns data.
But when this sql statement passed to the Recordset object, it returns
nothing.

One thing might be related to this problem is:
When I display this non-english characters on the next page, this
non-english characters is
changed to some strange characters. When I check on
View->Encoding->Auto-Select in IE browser, this non-english characters
shows correctly.
So I guess when this sql statement passed to the RecordSet object, the
non-english characters is changed to some strange characters. That's
why it returns nothing from SQL server.

Does anyone have any idea how to resolve this problem?

Your response is greatly appreciated!

Tiffany

Re: Problems when passing a sql statement containing non-english characters to a ADO.Recordset objec

select english_text from tblTest where translation =N'??'"
--
--Mark Schupp


"tiffany" wrote in message
news:1133305288.783900.228920@g44g2000cwa.googlegroups.com.. .
Dear Sir/Madam,

I am working on a web site which talks to a sql server in the backend.
I need to make the web page searchable on non-english characters.

So on the main page you can type non-english characters in a textbox
and then click on "Submit" button. On the next page, I will display
this non-english characters, run a sql statement, display the results
of this sql statement on the page.

Following is the sample sql statement:
select english_text from tblTest where translation = '??'

Following is the code to get the result set:
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "select english_text from tblTest where translation =
'??'", conn, 3, 3

When I manually run this sql statement in SQL query analyser, it
returns data.
But when this sql statement passed to the Recordset object, it returns
nothing.

One thing might be related to this problem is:
When I display this non-english characters on the next page, this
non-english characters is
changed to some strange characters. When I check on
View->Encoding->Auto-Select in IE browser, this non-english characters
shows correctly.
So I guess when this sql statement passed to the RecordSet object, the
non-english characters is changed to some strange characters. That's
why it returns nothing from SQL server.

Does anyone have any idea how to resolve this problem?

Your response is greatly appreciated!

Tiffany

Re: Problems when passing a sql statement containing non-english characters to a ADO.Recordset objec

Thanks for the response.

Sorry. The SQL statement I used is
select english_text from tblTest where translation =3D N'我们'

I DO use N'...' in the sql statement.

I guess the problem is caused by:
The non-english characters was changed to binary format when passing to
Recordset object.

Any idea how to solve this issue?

Many thanks,
Tiffany

Re: Problems when passing a sql statement containing non-english characters to a ADO.Recordset objec

tiffany wrote:
> Thanks for the response.
>
> Sorry. The SQL statement I used is
> select english_text from tblTest where translation = N'??'
>
> I DO use N'...' in the sql statement.
>
> I guess the problem is caused by:
> The non-english characters was changed to binary format when passing
> to Recordset object.

Verify this using SQL Profiler. If you are not familiar with this tool, it's
now time to get to know it. if you've installed the SQL Server client tools
on your machine, Profiler will be in that program group.

The display problem will be solved by using the proper encoding in your
page. See http://www.w3.org/TR/REC-html40/charset.html#h-5.2.1 as well as
http://www.iana.org/assignments/character-sets for the list of currently
registered charsets.

This will also be helpful: http://vancouver-webpages.com/multilingual/

The Response object has an Encoding property which you can use to set the
charset of the Response.

More here:
http://www.google.com/search?hl=en&lr=&rls=GGLC%2CGGLC%3A196 9-53%2CGGLC%3Aen&q=html+charset

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Problems when passing a sql statement containing non-english characters to a ADO.Recordset objec

Thanks Bob.

You are right. The Profiler shows that the SQL statement contains the
non-readable characters.

My question here is:
We have a web site containing some english terms with the corresponding
translations in 24 foreign languages.
When users enter some non-English characters to look up it's
correspoding english term, we don't know what language they were
entered.

But one thing is sure, when the user entered non-English characters, he
needs to select the correct encoding to have these characters displayed
correctly.

Is there any way to get the encoding information about the Client
browse and use that infor to convert the SQL string to the right format
so I can query the database and get the result back to the web page?

Many thanks,
Tiffany

Re: Problems when passing a sql statement containing non-english characters to a ADO.Recordset objec

Thanks Bob.

You are right. The Profiler shows that the SQL statement contains the
non-readable characters.

My question here is:
We have a web site containing some english terms with the corresponding
translations in 24 foreign languages.
When users enter some non-English characters to look up it's
correspoding english term, we don't know what language they were
entered.

But one thing is sure, when the user entered non-English characters, he
needs to select the correct encoding to have these characters displayed
correctly.

Is there any way to get the encoding information about the Client
browse and use that infor to convert the SQL string to the right format
so I can query the database and get the result back to the web page?

Many thanks,
Tiffany