Wayne Barron
asked on
Insert into SQL Server changes accented characters to ?
Hello, All.
SQL Server and ASP Classic.
SQL Server 2019
Column = NVarChar
ASP Parameter
sqlKey.commandtext="insert into Owner (OwnerName)values(?)"
sqlKey.Parameters.Append sqlKey.CreateParameter("@OwnerName", adVarchar, adParamInput, 350, strRecord)
I checked to see if there was an adNVarChar but did not find one in the ADOVBS.inc file.
I tried adVarWChar (NVarChar (SQL Server 7.0 +) ), and it inserted a
[Question make inside a Black Diamond] in the column.
I have this at the top of the insert.asp page.
Response.ContentType = "text/html"
Response.AddHeader "Content-Type", "text/html;charset=UTF-8"
Response.CodePage = 65001
Response.CharSet = "UTF-8"
Any assistance on this would be great.
Thanks.
Wayne
Hi,
This data type should indeed be available in the ADOVBS.inc file in the ASP Classic project.
Cheers
This data type should indeed be available in the ADOVBS.inc file in the ASP Classic project.
Cheers
ASKER
Big Monty.
Been a while, hope you are doing well.
I have <%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
Inside my database connection string page, so it is embedded in all pages, and unfortunately, it is not working.
dfke.
If it is, then please show its existence within the script.
Been a while, hope you are doing well.
I have <%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
Inside my database connection string page, so it is embedded in all pages, and unfortunately, it is not working.
dfke.
If it is, then please show its existence within the script.
ASKER
Also, within IIS
ASP Properties
Code Page: 65001
I read that it would need to be set to [0]
I tried setting it to [0] and inserting the data again, but it still does not work.
This is the name I am trying to insert.
Vincent Bolloré
ASP Properties
Code Page: 65001
I read that it would need to be set to [0]
I tried setting it to [0] and inserting the data again, but it still does not work.
This is the name I am trying to insert.
Vincent Bolloré
Hi,
You're right it's not in there. Sorry about that.
But Const adVarWChar = 202 does exist in the ActiveX Data Objects (ADO) library, which is part of the Microsoft Data Access Components (MDAC) package.
Cheers
You're right it's not in there. Sorry about that.
But Const adVarWChar = 202 does exist in the ActiveX Data Objects (ADO) library, which is part of the Microsoft Data Access Components (MDAC) package.
Cheers
ASKER
adVarWChar saves the character as a
[Question mark inside a Black Diamond]
[Question mark inside a Black Diamond]
what collation is your sql database?
ASKER
Hey, David.
SQL_Latin1_General_CP1_CI_ AS
My Media Upload script inserts the characters correctly into this database.
So I know that it is inserting the characters correctly.
SQL_Latin1_General_CP1_CI_
My Media Upload script inserts the characters correctly into this database.
So I know that it is inserting the characters correctly.
ASKER
I just found this and will try and set it to the database.
LATIN1_GENERAL_100_CI_AS_S C_UTF8
I got an error alter database failed. the default collation of database, so I will work through what it suggests and see what I can do.
Also.
I tried to send that same name straight through the page, and it changed the character to the Black Diamond Question mark.
So, it is not only inserting the data but trying to display it. However, It can display it if it is already IN the database.
Bizarre issue.
----
I tried doing it, and since I am mirrored, I have to stop the mirror to do it.
What a pain in the butt.
The only other way to do it is to create a new database, TEST to see if I can insert it into it, and if so.
Then I can script out the database and re-create it using that Collation.
LATIN1_GENERAL_100_CI_AS_S
I got an error alter database failed. the default collation of database, so I will work through what it suggests and see what I can do.
Also.
I tried to send that same name straight through the page, and it changed the character to the Black Diamond Question mark.
So, it is not only inserting the data but trying to display it. However, It can display it if it is already IN the database.
Bizarre issue.
----
I tried doing it, and since I am mirrored, I have to stop the mirror to do it.
What a pain in the butt.
The only other way to do it is to create a new database, TEST to see if I can insert it into it, and if so.
Then I can script out the database and re-create it using that Collation.
ASKER
Created a new database in the Lessons Servers, and set the collation to: LATIN1_GENERAL_100_CI_AS_S C_UTF8
And this in the insert page.
Still inserted a Question Mark in place of the accented character.
So, still at a loss on WHY it is so hard to make this work.
And this in the insert page.
Response.ContentType = "text/html"
Response.AddHeader "Content-Type", "text/html;charset=UTF-8"
Response.CodePage = 65001
'Response.CodePage = 28591
Response.CharSet = "UTF-8"
strCName = request.form("TakeMe")
Set upSite = CreateObject("ADODB.Command")
upSite.ActiveConnection=siteconn
upSite.Prepared = true
upSite.commandtext = "insert into TestTable (TestName)values(?)"
upSite.Parameters.Append upSite.CreateParameter("@TestName", 200, 1, 25, strCName)
upSite.execute
Still inserted a Question Mark in place of the accented character.
So, still at a loss on WHY it is so hard to make this work.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
My apologies, meant to respond sooner but got sucked into a meeting...good to hear from you as well Wayne...I pass through here every now and then :)
Looks like you got the answer, good on ya!
Looks like you got the answer, good on ya!
ASKER
Thank you, Big Monty.
Hope you have a great week.
Hope you have a great week.
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
this should encode everything on the page to UTC-8