Unable to Insert Char(63) by SQL query

Question

I have able to insert unusual character (returns 63) to my sql database, no problem with that.

Letsay ProductName = ኣድድድ

and then if I want to insert again but first check if productname exists in database

var product = db.Products.Where(x => x.Productname == txtproduct.Text.Trim()).FirstOrDefault();

then returns as there is already the same product name I mean

if(product == null)
{
   Products pr = new Producst();
   pr.ProductName = txtProductname.txt.trim() // tried even without trim()
   db.Products.Add(pr);
   db.Savechanges();    
}
else
{
    MessageBox.Show("There is the same productname registred"); // Returns allways this one , doesnt't matter which unusual character 
}

even if I write with another unusual character like productname = ሰግግግ then it returns "There is the same productname registred". In reality when I type them they are not the same words but when I check their ascii code they returns 63.

I don't want duplicate product names in database. Is there any way to solve this problem? Please help!

Answer 1

ASCII code 63 is a question mark ?. This means that the text can't be represented in the current varchar field, and all unsupported chars was converted to question marks. You either need to change the collation with one supporting these symbols, or better, change the data type of the column from varchar to nvarchar, to be able to store unicode chars.

Answer 2

Hi the query work i reproduce the error (duplicate) on first query

CREATE TABLE #MyTable  
(PrimaryKey   int PRIMARY KEY,  
   CharCol      varchar(10) COLLATE Finnish_Swedish_CI_AS NOT NULL,
   CONSTRAINT UC_CharCol UNIQUE (CharCol)  
  );  
GO  

INSERT INTO #MyTable 
SELECT 1, 'ኣድድድ'
INSERT INTO #MyTable 
SELECT 2, 'ኣድድኣ'
INSERT INTO #MyTable 
SELECT 3, 'ኣድኣ'

SELECT * FROM #MyTable


DROP TABLE  #MyTable

The result was :

PrimaryKey  CharCol
    3   ???
    1   ????

And error :

    (1 ligne affectée)
Msg 2627, Niveau 14, État 1, Ligne 10
Violation de la contrainte UNIQUE KEY « UC_CharCol ». Impossible d'insérer une clé en double dans l'objet « dbo.#MyTable ». Valeur de clé dupliquée : (????).

But when i try with the collation 'Finnish_Swedish_100_CI_AS_SC' and with the specified N'specialcharstring' they work great :

CREATE TABLE #MyTableBis  
  (PrimaryKey   int PRIMARY KEY,  
   CharCol      nvarchar(10) COLLATE Finnish_Swedish_100_CI_AS_SC NOT NULL,
   CONSTRAINT UC_CharCol UNIQUE (CharCol)  
  );  
GO  

INSERT INTO #MyTableBis 
SELECT 1, N'ኣድድድ'
INSERT INTO #MyTableBis 
SELECT 2, N'ኣድድኣ'
INSERT INTO #MyTableBis 
SELECT 3, N'ኣድኣ'

SELECT * FROM #MyTableBis


DROP TABLE  #MyTableBis

RESULT :

PrimaryKey  CharCol
3            ኣድኣ
2            ኣድድኣ
1            ኣድድድ

Source :

1. How to store unicode characters in SQL Server?
2. https://learn.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-2017

For using on Entity Framework : EntityFramework update or insert Chinese or non-English text

Answer 3

I tested and confirmed I would get both records back when only searching for one... could this work? Using UNICODE()

Create Table #tbl
(
f1 nVarChar(25)
)
Insert Into #tbl Values
(N'ኣድድድ'),
(N'ሰግግግ')


Select  
* 
From #tbl
Where  UNICODE (f1) = UNICODE (N'ኣድድድ')

UPDATE: Unicode only finds the first character. This could work if you can parse your search criteria into individual characters..

To find 'ኣድድድ'

Select  f1 From #tbl
Where  
   Unicode(substring(f1,1,1)) = 4771 And
   Unicode(substring(f1,2,1)) = 4853 And
   Unicode(substring(f1,3,1)) = 4853 And
   Unicode(substring(f1,4,1)) = 4853 

Answer 4

I think it's can be a Linq bad use the relevant piece off code that work great in my computer :

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                TESTEntities db = new TESTEntities();
                List<MyTableBis2> myDataList = db.MyTableBis2.ToList();
                string valuerFull = "";
                foreach (MyTableBis2 data in myDataList)
                {
                    valuerFull += data.CharCol + " ";
                }
                MessageBox.Show(valuerFull);
                var newValueAdd = textBox1.Text.Trim();

                MessageBox.Show(newValueAdd);

                var ch = myDataList.FirstOrDefault(x => x.CharCol.Equals(newValueAdd));
                if (ch == null)
                {
                    MyTableBis2 m = new MyTableBis2();
                    m.CharCol = textBox1.Text.Trim();
                    db.MyTableBis2.Add(m);
                    db.SaveChanges();
                }
                else
                {
                    MessageBox.Show("This product exist");
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                Exception innerE = ex.InnerException;
                while (innerE != null)
                {
                    MessageBox.Show(innerE.Message);
                    innerE = innerE.InnerException;
                }
            }
        }

Waiting for your return to see if it works