Unicode characters showing as ?

[lidds]

I have developed an application and need to allow users to enter text in any language and save it to my database. However the text is saved as '?' in my database. I have googled quite a bit and found that people have suggested not to use varchar, text types but use nvarchar and ntext etc in your database. I have gone through and made these changes to my database.

However it still saved text as '?', I then found people where saying about adding 'N' infront of the text prior to saving into the database. The problem is that I cannot figure out how to do this in a stored procedure.

Stored Procedure

CREATE PROCEDURE [dbo].[spUpdateActionTaken] @ID as varChar(4),@action as text,@modifyDate as varchar(50)

AS

UPDATE commentsTbl SET commActionTaken=@action,modifyDate=@modifyDate WHERE [ID]=@ID

Note: Fields commActionTaken set to nText

VB.Net Code

Dim actionTaken as String = Me.textBox.Text
Dim myCmd As New OleDb.OleDbCommand("spUpdateActionTaken")
myCmd.CommandType = CommandType.StoredProcedure
myCmd.Parameters.Add(New OleDb.OleDbParameter("@ID", OleDb.OleDbType.VarChar)).Value = ID
myCmd.Parameters.Add(New OleDb.OleDbParameter("@action", OleDb.OleDbType.VarChar)).Value = actionTaken
myCmd.Parameters.Add(New OleDb.OleDbParameter("@modifyDate", OleDb.OleDbType.Date)).Value = Now()

I am assuming that this is the solution or do I have to change anything else in the database? I am using MS SQL 2000 database.

Thanks in advance

Simon

[dee-u]

Perhaps this will help?

[lidds]

dee-u,

Thank you for the information, however I am still unsure how to add the 'N' to the stored procedure as the example on the website uses a string value instead of arguments/variable from the stored procedure. Would my sored procedure be like this:

CREATE PROCEDURE [dbo].[spUpdateActionTaken] @ID as varChar(4),@action as text,@modifyDate as varchar(50)

AS

UPDATE commentsTbl SET commActionTaken=N@action,modifyDate=@modifyDate WHERE [ID]=@ID

Thanks

[si_the_geek]

You have changed your fields from varchar/text to nvarchar/ntext, but have not changed the parameters of the procedure.

I think it will be OK once you have done that.

[lidds]

Thanks for the help, but unfortunately nvarchar / ntext is not supported by oledb so I am not able to make this change. Obviously I could use SQLClient but my application works with MS SQL and Oracle hence using Oledb.

Any other suggestion?

[si_the_geek]

I find it very hard to believe that OLEDB doesn't support unicode, because it has been a significant thing for many years.

What I suspect is that you are just going by the names of the data types, which do not necessarily have exactly the same meaning. Either check the help for them, or try a few things out.


Note that as far as the parameters for the procedure go, you don't have much choice - if they aren't the N variations, you will not receive the data correctly.

[lidds]

You are correct, I looked at the documentation for oledb and it is VarWChar for unicode string. Was looking for something like NVarChar or something.

Thanks for your help, will try this when I get home