Help with ASP/Insert SP for Nvarchar (Non Latin characters)

I have a table that inserts comments, the comments field it is defined as nvarchar(MAX), but in the ASP i need to define a max number of characters, what should that number be?

This is part of the insert statement for the note in the ASP page:

sp_addnote.Parameters.Append sp_addnote.CreateParameter("@comments", 200, 1,8000000,sp_addnote__comments)

Open in new window

I entered   '8000000'  as the max number of characters, but I imagine there is a better number to put there?

2. Also the SP is failing to accept Korean characters. I am not sure where to add the  N'  before the value that will be inserted so that it recognizes the Korean characters, here is the SP as I have it right now:

ALTER PROCEDURE [dbo].[add_notes]
   @caseid INT,
   @visible INT,
   @comments NVARCHAR(MAX),
   @updatedby INT,
   @lastupdate DATETIME,
   @commby BIT,
   @sectioncase NVARCHAR(50),
   @firmid INT,
   @importance INT,
   @visibleemployer BIT
INSERT INTO dbo.Casecomments
(@caseid, @visible, (N'@comments), @updatedby, @lastupdate, '0', @sectioncase, @firmid, @importance, @visibleemployer);

Open in new window

I added the N'  in the values before the @comments parameter, but its failing, my syntax must be wrong ... or maybe it should not be there and should be in the line of the ASP ?

Without the N'  the characters are converted into ???? characters.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
you could use adLongVarWChar with a length of -1 when adding the parameter...

sp_addnote.Parameters.Append sp_addnote.CreateParameter("@comments", adLongVarWChar, adParamInput, -1 ,sp_addnote__comments)

Open in new window

And your parameter in the SP will need to be NVARCHAR and because it is you dont need N'@comments in the values part...
AleksAuthor Commented:
I got an error:

ADODB.Command error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/bluedot_qtr4_2017/Intranet/Contacts/sp_addnote.asp, line 81 

Open in new window

Which is the line I changed.
Mark WillsTopic AdvisorCommented:
Can we see all the adding of parameters block of code ?

How have you defined "sp_addnote__comments"  (and noticed the double underscore)
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
what if trying this?

sp_addnote.Parameters.Append sp_addnote.CreateParameter("@comments",  sp_addnote__comments)

Open in new window

AleksAuthor Commented:
Second proposed solution gives me this:

Provider error '80020005'

Type mismatch.

/bluedot_qtr4_2017/Intranet/Contacts/sp_addnote.asp, line 81

This is the entire ASP code that calls the SP.

Dim sp_addnote__caseid
sp_addnote__caseid = "0"
if(request("newuserid") <> "") then sp_addnote__caseid = request("newuserid")

Dim sp_addnote__visible
sp_addnote__visible = "0"
if(request("visible") <> "") then sp_addnote__visible = request("visible")

Dim sp_addnote__comments
sp_addnote__comments = "0"
if(request("summernote") <> "") then sp_addnote__comments = request("summernote")

Dim sp_addnote__updatedby
sp_addnote__updatedby = "0"
if(session("userid") <> "") then sp_addnote__updatedby = session("userid")

Dim sp_addnote__lastupdate
sp_addnote__lastupdate = "0"
if(request("lastupdate") <> "") then sp_addnote__lastupdate = request("lastupdate")

Dim sp_addnote__commby
sp_addnote__commby = "0"
if(session("userid") <> "") then sp_addnote__commby = session("userid")

Dim sp_addnote__sectioncase
sp_addnote__sectioncase = "0"
if("Alien" <> "") then sp_addnote__sectioncase = "Alien"

Dim sp_addnote__firmid
sp_addnote__firmid = "0"
if(session("firmid") <> "") then sp_addnote__firmid = session("firmid")

Dim sp_addnote__importance
sp_addnote__importance = "0"
if(request("importance") <> "") then sp_addnote__importance = request("importance")

Dim sp_addnote__visibleemployer
sp_addnote__visibleemployer = "0"
if(request("visibleemployer") <> "") then sp_addnote__visibleemployer = request("visibleemployer")



set sp_addnote = Server.CreateObject("ADODB.Command")
sp_addnote.ActiveConnection = MM_bluedot_STRING
sp_addnote.CommandText = "dbo.add_notes"
sp_addnote.CommandType = 4
sp_addnote.CommandTimeout = 0
sp_addnote.Prepared = true
sp_addnote.Parameters.Append sp_addnote.CreateParameter("@RETURN_VALUE", 3, 4)
sp_addnote.Parameters.Append sp_addnote.CreateParameter("@caseid", 3, 1,4,sp_addnote__caseid)
sp_addnote.Parameters.Append sp_addnote.CreateParameter("@visible", 3, 1,4,sp_addnote__visible)
sp_addnote.Parameters.Append sp_addnote.CreateParameter("@comments",  sp_addnote__comments)
sp_addnote.Parameters.Append sp_addnote.CreateParameter("@updatedby", 3, 1,4,sp_addnote__updatedby)
sp_addnote.Parameters.Append sp_addnote.CreateParameter("@lastupdate", 135, 1,10,sp_addnote__lastupdate)
sp_addnote.Parameters.Append sp_addnote.CreateParameter("@commby", 3, 1,4,sp_addnote__commby)
sp_addnote.Parameters.Append sp_addnote.CreateParameter("@sectioncase", 200, 1,50,sp_addnote__sectioncase)
sp_addnote.Parameters.Append sp_addnote.CreateParameter("@firmid", 3, 1,4,sp_addnote__firmid)
sp_addnote.Parameters.Append sp_addnote.CreateParameter("@importance", 3, 1,4,sp_addnote__importance)
sp_addnote.Parameters.Append sp_addnote.CreateParameter("@visibleemployer", 3, 1,4,sp_addnote__visibleemployer)

Open in new window

Mark WillsTopic AdvisorCommented:
OK, so line 81 seems to be the @comments line.

But when you use datatype 200 (varchar) it doesnt handle unicode characters properly, but, you also dont get the error

But when you use datatype 202 (nvarchar) it errors  (or did you use adVarWChar ? see below).

But @comments parameter if defined as NVARCHAR(max), which really is adVarWchar with a length of -1

So the mismatch is at the ADO end, it seems it needs to be VARCHAR ( or 200, or adVarChar)

So maybe @comments parameter needs to be VARCHAR(80000) and try that as a quick test with a very simple text message....

Also noticed  ADO constants (i.e. 200 instead of adVarChar) maybe you do not have included in your app.

Have you seen :
Big MontyWeb Ninja at largeCommented:
whenever i'm working with nvarchar( max ), I take the length of the string and use that as the size of the field:

sp_addnote.Parameters.Append sp_addnote.CreateParameter("@comments", 200, Len( "" & sp_addnote__comments ), sp_addnote__comments)

I make it like "" & sp_addnote__comments so that in case that variable is null, it'll default to a length of zero, and not throw an error trying to get the length of a null value
AleksAuthor Commented:
Ill try the above see what i get.  One min ...
AleksAuthor Commented:
Using the Big Monty's line I get the error below:

Provider error '80020005'

Type mismatch.

/bluedot_qtr4_2017/Intranet/Contacts/sp_addnote.asp, line 81 

Open in new window

Big MontyWeb Ninja at largeCommented:
can you post the line of code you added?
AleksAuthor Commented:
sp_addnote.Parameters.Append sp_addnote.CreateParameter("@comments", 200, Len( "" & sp_addnote__comments ), sp_addnote__comments)

Open in new window

AleksAuthor Commented:
If I use the line below it works but the Korean characters are turned into question marks.

sp_addnote.Parameters.Append sp_addnote.CreateParameter("@comments", 200, 1, Len( "" & sp_addnote__comments ), sp_addnote__comments)

Open in new window

My entry:

I-765/131 펙케지 금일 이민국으로 발송합니다. 
2-3주 안에 이민국으로부터 접수증 받으면 확인해드리겠습니다. 
혹시 관련으로 궁금하신 사항 있으시면 알려주시기 바랍니다.

Open in new window

What is saved:

I-765/131 ??? ?? ????? ?????.
2-3? ?? ??????? ??? ??? ?????????.
?? ???? ???? ?? ???? ????? ????.

Open in new window

Big MontyWeb Ninja at largeCommented:
beat me to it, I just noticed you're missing one other parameter (the SIZE parameter where you added the 1). Let's try changing 200 to 202, which is the value for adVarWChar which is the NVarChar equivalent.

If the proper encoding still isn't taking place, make sure you the following at the top of your page:
<%response.contentType = "text/plain; charset=utf-8"%>

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AleksAuthor Commented:
Ha!  That did the trick. it was 202 instead of 200 ... problem solved. Thank you master !
AleksAuthor Commented:
Thanks Master!  :)  perfecto as usual.
Big MontyWeb Ninja at largeCommented:
happy to help :)
Mark WillsTopic AdvisorCommented:
Ummm, excuse me - I pointed out 202 for adVarWChar, if not using back in #a42449659

And you can use length of -1 which translates to (max) very nicely.
Big MontyWeb Ninja at largeCommented:
Agreed, Mark did mention use of using 202 as a value (something I missed entirely when I read his post), even though it was a bit vague. I don't necessarily think using -1 is more efficient than calculation of the length of the string. I would recommend splitting the points as both suggestions would work.
AleksAuthor Commented:
Agreed. I am not a programmer so I did not understand half of what was suggested. I'll try and split the points as suggested.
Mark WillsTopic AdvisorCommented:
Thank you Big Monty, and Aleks Appreciate the consideration.
Big MontyWeb Ninja at largeCommented:
Hit Request Attention to ask one of the mods to come and re-open the question so you can split accordingly
AleksAuthor Commented:
Thanks to both.
Mark WillsTopic AdvisorCommented:
>>  I am not a programmer so I did not understand half of what was suggested.

Apologies, it appeared to me that you are pretty good at it :)

Didnt really notice not including until that post mentioned above.

Should have picked it up in my very first post - and by assuming too much, caused you a great deal of frustration and extra work. Apologies.

To both Big Monty and Aleks, thank you sincerely.
Big MontyWeb Ninja at largeCommented:
no worries, I'm not here for points, but rather to share my knowledge of this archaic (and still useful) language :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development

From novice to tech pro — start learning today.