Unicode Website/Forms save to SQL-Server2000 Problem

We're using SQL-Server2000 as follows...
There is a form at this asp-application where we enter a word with .cz-chars
like this: 'Ohřvače'. All this form, as well as other settings are set to
UTF-8. Output is ok, even in the sql-string we can see the word written
correctly, but in database, we get 'Ohrvace' instead of the original spelling.
Field-Type in database is nvarchar.
What's the problem there? How can we get the right data into the database?

dbConnectionString = "Provider=SQLOLEDB;Data
Source=\SQLEXPRESS;Initial Catalog=;User
Id=sa;Password="
set conn = CreateObject("ADODB.Connection")
conn.open dbConnectionString


Could you please try to help us as fast as possible, cause this is an
application we have to give to our customers soon...

--
CEB-Systems GbR
Sudetenstrasse 6
60437 Frankfurt

Re: Unicode Website/Forms save to SQL-Server2000 Problem

cebsystems wrote:
> We're using SQL-Server2000 as follows...
> There is a form at this asp-application where we enter a word with
> .cz-chars like this: 'Ohrvace'. All this form, as well as other
> settings are set to UTF-8. Output is ok, even in the sql-string we
> can see the word written correctly, but in database, we get 'Ohrvace'
> instead of the original spelling. Field-Type in database is nvarchar.
> What's the problem there? How can we get the right data into the
> database?
>
> dbConnectionString = "Provider=SQLOLEDB;Data
> Source=\SQLEXPRESS;Initial Catalog=;User
> Id=sa;Password="

sa????
Are you inviting hackers to take over your server?
Create a limited-rights sql login to use in your application. Don't use sa!

> set conn = CreateObject("ADODB.Connection")
> conn.open dbConnectionString
>
>
Why did you stop here? We can't answer without seeing how you're sending the
data to the database. Are you using dynamic sql (I hope not)? A recordset
(again, I hope not)? A stored procedure (recommended)? Parameterized sql
statement?

One thing you can do is use SQL Profiler to look at the sql statements being
executed in the database.

--
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: Unicode Website/Forms save to SQL-Server2000 Problem

Since we took this asp-website from our customer, we haven't coded it
ourselves, so what we have from our customer is the following:

sql = "update [menu_table] "
sql = sql & "set [name]='" & myname & "'"
sql = sql & " where id='" & myid & "'"
conn.execute(sql)

where myname is the string mentioned before and when we use response.write
to see the sql-statement, we still see the string in the correct form.

--
CEB-Systems GbR
Sudetenstrasse 6
60437 Frankfurt


"Bob Barrows [MVP]" wrote:

> cebsystems wrote:
> > We're using SQL-Server2000 as follows...
> > There is a form at this asp-application where we enter a word with
> > .cz-chars like this: 'Ohrvace'. All this form, as well as other
> > settings are set to UTF-8. Output is ok, even in the sql-string we
> > can see the word written correctly, but in database, we get 'Ohrvace'
> > instead of the original spelling. Field-Type in database is nvarchar.
> > What's the problem there? How can we get the right data into the
> > database?
> >
> > dbConnectionString = "Provider=SQLOLEDB;Data
> > Source=\SQLEXPRESS;Initial Catalog=;User
> > Id=sa;Password="
>
> sa????
> Are you inviting hackers to take over your server?
> Create a limited-rights sql login to use in your application. Don't use sa!
>
> > set conn = CreateObject("ADODB.Connection")
> > conn.open dbConnectionString
> >
> >
> Why did you stop here? We can't answer without seeing how you're sending the
> data to the database. Are you using dynamic sql (I hope not)? A recordset
> (again, I hope not)? A stored procedure (recommended)? Parameterized sql
> statement?
>
> One thing you can do is use SQL Profiler to look at the sql statements being
> executed in the database.
>
> --
> 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: Unicode Website/Forms save to SQL-Server2000 Problem

Use
Response.Write sql
to see the sql string being created. Is it what you expect it to be? If
not, the encoding of the Request may not be correct.

Having said that:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e


cebsystems wrote:
> Since we took this asp-website from our customer, we haven't coded it
> ourselves, so what we have from our customer is the following:
>
> sql = "update [menu_table] "
> sql = sql & "set [name]='" & myname & "'"
> sql = sql & " where id='" & myid & "'"
> conn.execute(sql)
>
> where myname is the string mentioned before and when we use
> response.write to see the sql-statement, we still see the string in
> the correct form.
>
> --
> CEB-Systems GbR
> Sudetenstrasse 6
> 60437 Frankfurt
>
>
> "Bob Barrows [MVP]" wrote:
>
>> cebsystems wrote:
>>> We're using SQL-Server2000 as follows...
>>> There is a form at this asp-application where we enter a word with
>>> .cz-chars like this: 'Ohrvace'. All this form, as well as other
>>> settings are set to UTF-8. Output is ok, even in the sql-string we
>>> can see the word written correctly, but in database, we get
>>> 'Ohrvace' instead of the original spelling. Field-Type in database
>>> is nvarchar. What's the problem there? How can we get the right
>>> data into the database?
>>>
>>> dbConnectionString = "Provider=SQLOLEDB;Data
>>> Source=\SQLEXPRESS;Initial Catalog=;User
>>> Id=sa;Password="
>>
>> sa????
>> Are you inviting hackers to take over your server?
>> Create a limited-rights sql login to use in your application. Don't
>> use sa!
>>
>>> set conn = CreateObject("ADODB.Connection")
>>> conn.open dbConnectionString
>>>
>>>
>> Why did you stop here? We can't answer without seeing how you're
>> sending the data to the database. Are you using dynamic sql (I hope
>> not)? A recordset (again, I hope not)? A stored procedure
>> (recommended)? Parameterized sql statement?

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Unicode Website/Forms save to SQL-Server2000 Problem

The SQL-Statement looks like expected, so no Response-Encoding-Problem.
Any other idea what could be the reason that the data is not saved in the
database correctly?
Cause when we enter the SQL-Statement by hand with the Sql Server Management
Studio, the data is saved correctly.

--
CEB-Systems GbR
Sudetenstrasse 6
60437 Frankfurt


"Bob Barrows [MVP]" wrote:

> Use
> Response.Write sql
> to see the sql string being created. Is it what you expect it to be? If
> not, the encoding of the Request may not be correct.
>
> Having said that:
> Your use of dynamic sql is leaving you vulnerable to hackers using sql
> injection:
> http://mvp.unixwiz.net/techtips/sql-injection.html
> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
>
> See here for a better, more secure way to execute your queries by using
> parameter markers:
> http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e
>
>
> cebsystems wrote:
> > Since we took this asp-website from our customer, we haven't coded it
> > ourselves, so what we have from our customer is the following:
> >
> > sql = "update [menu_table] "
> > sql = sql & "set [name]='" & myname & "'"
> > sql = sql & " where id='" & myid & "'"
> > conn.execute(sql)
> >
> > where myname is the string mentioned before and when we use
> > response.write to see the sql-statement, we still see the string in
> > the correct form.
> >
> > --
> > CEB-Systems GbR
> > Sudetenstrasse 6
> > 60437 Frankfurt
> >
> >
> > "Bob Barrows [MVP]" wrote:
> >
> >> cebsystems wrote:
> >>> We're using SQL-Server2000 as follows...
> >>> There is a form at this asp-application where we enter a word with
> >>> .cz-chars like this: 'Ohrvace'. All this form, as well as other
> >>> settings are set to UTF-8. Output is ok, even in the sql-string we
> >>> can see the word written correctly, but in database, we get
> >>> 'Ohrvace' instead of the original spelling. Field-Type in database
> >>> is nvarchar. What's the problem there? How can we get the right
> >>> data into the database?
> >>>
> >>> dbConnectionString = "Provider=SQLOLEDB;Data
> >>> Source=\SQLEXPRESS;Initial Catalog=;User
> >>> Id=sa;Password="
> >>
> >> sa????
> >> Are you inviting hackers to take over your server?
> >> Create a limited-rights sql login to use in your application. Don't
> >> use sa!
> >>
> >>> set conn = CreateObject("ADODB.Connection")
> >>> conn.open dbConnectionString
> >>>
> >>>
> >> Why did you stop here? We can't answer without seeing how you're
> >> sending the data to the database. Are you using dynamic sql (I hope
> >> not)? A recordset (again, I hope not)? A stored procedure
> >> (recommended)? Parameterized sql statement?
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

Re: Unicode Website/Forms save to SQL-Server2000 Problem

Try this:
Change this:

sql = "update [menu_table] "
sql = sql & "set [name]='" & myname & "'"
sql = sql & " where id='" & myid & "'"

To this:
sql = "update [menu_table] "
sql = sql & "set [name]=N'" & myname & "'"
sql = sql & " where id='" & myid & "'"

If that does not help, we will need to try using an explicit Command
object to pass the values with the correct datatypes.

cebsystems wrote:
> The SQL-Statement looks like expected, so no
> Response-Encoding-Problem.
> Any other idea what could be the reason that the data is not saved in
> the database correctly?
> Cause when we enter the SQL-Statement by hand with the Sql Server
> Management Studio, the data is saved correctly.
>
> --
> CEB-Systems GbR
> Sudetenstrasse 6
> 60437 Frankfurt
>
>
> "Bob Barrows [MVP]" wrote:
>
>> Use
>> Response.Write sql
>> to see the sql string being created. Is it what you expect it to be?
>> If not, the encoding of the Request may not be correct.
>>
>> Having said that:
>> Your use of dynamic sql is leaving you vulnerable to hackers using
>> sql injection:
>> http://mvp.unixwiz.net/techtips/sql-injection.html
>> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
>>
>> See here for a better, more secure way to execute your queries by
>> using parameter markers:
>>
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e
>>
>>
>> cebsystems wrote:
>>> Since we took this asp-website from our customer, we haven't coded
>>> it ourselves, so what we have from our customer is the following:
>>>
>>> sql = "update [menu_table] "
>>> sql = sql & "set [name]='" & myname & "'"
>>> sql = sql & " where id='" & myid & "'"
>>> conn.execute(sql)
>>>
>>> where myname is the string mentioned before and when we use
>>> response.write to see the sql-statement, we still see the string in
>>> the correct form.
>>>
>>> --
>>> CEB-Systems GbR
>>> Sudetenstrasse 6
>>> 60437 Frankfurt
>>>
>>>
>>> "Bob Barrows [MVP]" wrote:
>>>
>>>> cebsystems wrote:
>>>>> We're using SQL-Server2000 as follows...
>>>>> There is a form at this asp-application where we enter a word with
>>>>> .cz-chars like this: 'Ohrvace'. All this form, as well as other
>>>>> settings are set to UTF-8. Output is ok, even in the sql-string we
>>>>> can see the word written correctly, but in database, we get
>>>>> 'Ohrvace' instead of the original spelling. Field-Type in database
>>>>> is nvarchar. What's the problem there? How can we get the right
>>>>> data into the database?
>>>>>
>>>>> dbConnectionString = "Provider=SQLOLEDB;Data
>>>>> Source=\SQLEXPRESS;Initial Catalog=;User
>>>>> Id=sa;Password="
>>>>
>>>> sa????
>>>> Are you inviting hackers to take over your server?
>>>> Create a limited-rights sql login to use in your application. Don't
>>>> use sa!
>>>>
>>>>> set conn = CreateObject("ADODB.Connection")
>>>>> conn.open dbConnectionString
>>>>>
>>>>>
>>>> Why did you stop here? We can't answer without seeing how you're
>>>> sending the data to the database. Are you using dynamic sql (I
>>>> hope not)? A recordset (again, I hope not)? A stored procedure
>>>> (recommended)? Parameterized sql statement?
>>
>> --
>> Microsoft MVP -- ASP/ASP.NET
>> Please reply to the newsgroup. The email account listed in my From
>> header is my spam trap, so I don't check it very often. You will get
>> a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Unicode Website/Forms save to SQL-Server2000 Problem

Thanks for your help!
The N was the thing needed to put in data correctly.

--
CEB-Systems GbR
Sudetenstrasse 6
60437 Frankfurt


"Bob Barrows [MVP]" wrote:

> Try this:
> Change this:
>
> sql = "update [menu_table] "
> sql = sql & "set [name]='" & myname & "'"
> sql = sql & " where id='" & myid & "'"
>
> To this:
> sql = "update [menu_table] "
> sql = sql & "set [name]=N'" & myname & "'"
> sql = sql & " where id='" & myid & "'"
>
> If that does not help, we will need to try using an explicit Command
> object to pass the values with the correct datatypes.
>
> cebsystems wrote:
> > The SQL-Statement looks like expected, so no
> > Response-Encoding-Problem.
> > Any other idea what could be the reason that the data is not saved in
> > the database correctly?
> > Cause when we enter the SQL-Statement by hand with the Sql Server
> > Management Studio, the data is saved correctly.
> >
> > --
> > CEB-Systems GbR
> > Sudetenstrasse 6
> > 60437 Frankfurt
> >
> >
> > "Bob Barrows [MVP]" wrote:
> >
> >> Use
> >> Response.Write sql
> >> to see the sql string being created. Is it what you expect it to be?
> >> If not, the encoding of the Request may not be correct.
> >>
> >> Having said that:
> >> Your use of dynamic sql is leaving you vulnerable to hackers using
> >> sql injection:
> >> http://mvp.unixwiz.net/techtips/sql-injection.html
> >> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
> >>
> >> See here for a better, more secure way to execute your queries by
> >> using parameter markers:
> >>
> http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e
> >>
> >>
> >> cebsystems wrote:
> >>> Since we took this asp-website from our customer, we haven't coded
> >>> it ourselves, so what we have from our customer is the following:
> >>>
> >>> sql = "update [menu_table] "
> >>> sql = sql & "set [name]='" & myname & "'"
> >>> sql = sql & " where id='" & myid & "'"
> >>> conn.execute(sql)
> >>>
> >>> where myname is the string mentioned before and when we use
> >>> response.write to see the sql-statement, we still see the string in
> >>> the correct form.
> >>>
> >>> --
> >>> CEB-Systems GbR
> >>> Sudetenstrasse 6
> >>> 60437 Frankfurt
> >>>
> >>>
> >>> "Bob Barrows [MVP]" wrote:
> >>>
> >>>> cebsystems wrote:
> >>>>> We're using SQL-Server2000 as follows...
> >>>>> There is a form at this asp-application where we enter a word with
> >>>>> .cz-chars like this: 'Ohrvace'. All this form, as well as other
> >>>>> settings are set to UTF-8. Output is ok, even in the sql-string we
> >>>>> can see the word written correctly, but in database, we get
> >>>>> 'Ohrvace' instead of the original spelling. Field-Type in database
> >>>>> is nvarchar. What's the problem there? How can we get the right
> >>>>> data into the database?
> >>>>>
> >>>>> dbConnectionString = "Provider=SQLOLEDB;Data
> >>>>> Source=\SQLEXPRESS;Initial Catalog=;User
> >>>>> Id=sa;Password="
> >>>>
> >>>> sa????
> >>>> Are you inviting hackers to take over your server?
> >>>> Create a limited-rights sql login to use in your application. Don't
> >>>> use sa!
> >>>>
> >>>>> set conn = CreateObject("ADODB.Connection")
> >>>>> conn.open dbConnectionString
> >>>>>
> >>>>>
> >>>> Why did you stop here? We can't answer without seeing how you're
> >>>> sending the data to the database. Are you using dynamic sql (I
> >>>> hope not)? A recordset (again, I hope not)? A stored procedure
> >>>> (recommended)? Parameterized sql statement?
> >>
> >> --
> >> Microsoft MVP -- ASP/ASP.NET
> >> Please reply to the newsgroup. The email account listed in my From
> >> header is my spam trap, so I don't check it very often. You will get
> >> a quicker response by posting to the newsgroup.
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>