views:

38

answers:

1

I have the following Stored Procedure, Im looking for the correct syntax so I can play the Comments Value in the Comments column with N in front end the value for Unicode I need save Russian Characters Values

So at the moment the comments value is being passed as such

@comments

I want to do

N@comments but not working

ALTER PROCEDURE [dbo].[spInsertContactUs]
(
@title VARCHAR(20) = default,
@forename VARCHAR(100) = default,
@surname VARCHAR(100) = default,
@gender VARCHAR(50) = default,
@address1 VARCHAR(100) = default,
@address2 VARCHAR(100) = default,
@city VARCHAR(50) = default,
@county VARCHAR(50) = default,
@country INT = default,
@zipcode VARCHAR(50) = default,
@email VARCHAR(200) = default,
@comments NVARCHAR(MAX) = default,
@mailinglist BIT = default,
@address3 VARCHAR(100) = default,
@dateOfBirth datetime = default
)
AS
SET NOCOUNT ON

INSERT INTO tblContactUs (
dateAdded,
title,
forename,
surname,
gender,
address1,
address2,
city,
county,
country,
zipcode,
email,
comments,
mailinglist,
address3,
dateOfBirth)
VALUES (
getdate(),
@title,
@forename,
@surname,
@gender,
@address1,
@address2,
@city,
@county,
@country,
@zipcode,
@email,
@comments,
@mailinglist,
@address3,
@dateOfBirth
)

SET NOCOUNT OFF
RETURN

;

+2  A: 

Use Nvarchar data type in table's fields and stored procs parameters.

ADDED

See this link, maybe this will help you.

igor
Hey Igor, yup as you can see in the stored procedure above I ami.e @comments NVARCHAR(MAX) = defaultBut no joy
StevieB
What about field in the table?
igor
Yeah I changed that to NVARCHAR(MAX)
StevieB
So your saying when I use NVARCHAR theres no need for me to look at add N before the value when inserting into DB
StevieB
@StevieB - the N just makes whatever follows in the single quotes in unicode format.
JNK
@StevieB How do you call this stored procedure?
igor
@JNK Yeah I think I need that though in my stored procedures because Im having issues saving russian characters i.e if i do plain sql UPDATE tblContactUsSET comments = N'Админ'WHERE (id = 2228) it saves fine and im trying to get this same logic in a stored procedure with a variable but proving impossible
StevieB
@igor in classis ASPDim comments : comments = database_FilterInput(request.Form("comments"))Set objSP = SQLGetProcedure("spInsertContactUs")...SQLSetProcedureParam objSP, "comments", comments...
StevieB
What is `SQLSetProcedureParam`? I can't see many google results for that. Maybe use SQL profiler to see what your asp application is sending to the server.
Martin Smith
AH sorry its a customer method..but when I print out to the screen the value of comments before it goes into the stored procedure the value is Админ. So no issue on the server I think. I just need to find a way to get a N in front of that variable
StevieB
@StevieB I have added link to my answer. please, see on it. maybe it is a problem asp, i think. luck
igor
@StevieB - Just because the screen shot shows Russian characters doesn't mean that's how it's being passed back to the database. My guess is that it's being converted away from unicode somewhere between when it is in the form field and when it gets passed to the server. SQL Profiler should be able to confirm this for you.
Tom H.
@igor Yup that worked !<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%> added to top of page + changing column type to NVARCHAR did the trick
StevieB
@Tom H. Yup your right, just realised this to.And also whats SQL Profiler ?
StevieB
SQL Profiler is a tool that comes with MS SQL Server. It lets you monitor exactly what is coming into the server. You can filter which events you track so you could see the exact stored procedure call as the server is seeing it. It can be a very useful debugging tool in these situations.
Tom H.
Tom H. I.e do i need to keep watching it in real time waiting for inserts to be done or how would that work i.e can i run some dummy values in it
StevieB