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!

Not sure. But does this help? stackoverflow.com/questions/15982499/… — Andrew Cheong
– Andrew Cheong, Commented Dec 31, 2018 at 10:14
@Helen Hi, have you access to the database ? if you have acces adding constraint on database definition it's was better off using code. Like : ALTER TABLE TABLE ADD CONSTRAINT Unique_NameCol UNIQUE (NameCol); — Sanpas
– Sanpas, Commented Dec 31, 2018 at 10:15
The two characters are unicode characters (two bytes) 4771 (0x12A3) and 4853 (0x12F5) used 3 times. Try using a RichTextBox. — jdweng
– jdweng, Commented Dec 31, 2018 at 10:17
Did you try debugging the values of txtproduct.Text and txtproduct.Text.Trim()? Also if you don't need to do something specific with product, you might prefer to write something with Any like if(db.Products.Any(x => x.Productname == txtproduct.Text)) — Rafalon
– Rafalon, Commented Dec 31, 2018 at 10:43
@pascalsanchez , thank you for responding. Yes I have access to database and added ConstriantUnique_NameCol, but geting the same problem — Helen Tekie
– Helen Tekie, Commented Dec 31, 2018 at 10:47

Andrey Nikolov · Accepted Answer · 2018-12-31 10:19:21Z

3

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.

answered Dec 31, 2018 at 10:19

Andrey Nikolov

13.5k3 gold badges26 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Helen Tekie Over a year ago

thank you for responding, I have nvarchar in my database. I have no problem to insert to my database with those unusual character. But when I insert the first productname, then next another productname is interpreting as the same productname

Andrey Nikolov Over a year ago

The field may be nvarchar, but obviously the unicode text is converted to question marks. So there must be some conversion to non-unicode. Check how you insert the data, are there any parameters with wrong types, text literals not prefixed with N, etc.

Helen Tekie Over a year ago

No I do not have any parameter, but if I put N in front of my string Then it will be the other way around I could dublicate but could not insert anothername, With N I could dublicate but without N then every string in my database is interpreting as the same Word. I Think it's too complicate

Sanpas · Accepted Answer · 2019-01-02 09:05:09Z

0

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

answered Jan 2, 2019 at 9:05

Sanpas

1,18010 silver badges30 bronze badges

6 Comments

Helen Tekie Over a year ago

My godness ... IT IS WORKING !!!!!! I will never understand What is the secret behind this becouse it not so much different code I was using allways. It's litle strange How Id of product ordering .. but it's working. I mean Id 1,Id2 then Id5 comes like Id 3 I think it is something about characters how they interepting. Thank you so much Pascal. You are so greate! I never tought it will be work. Becouse of those unusual characters I have even another Issue there I want to get summery of cities in a state like thise one stackoverflow.com/posts/53996141/revisions

Sanpas Over a year ago

Thanks you for your return :). Your're welcome to echange by email :). If you can check my answer or upvote :). Thank in advance.

Sanpas Over a year ago

@HelenTekie i'm going to look new posts. Have you find way to get expected result ?

Helen Tekie Over a year ago

sanches, do you mean about geting sumery of cities? It is working when I have usual characters but with Unusual characters it shows me only or I only see one row in my gridview with Totalcities as "Title" and value of 3

Sanpas Over a year ago

@HelenTekie you can send mi a sample template project that reproduce thoses be email ? thank in advance.

|

Sanpas · Accepted Answer · 2018-12-31 14:41:29Z

0

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 :

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

edited Dec 31, 2018 at 14:41

answered Dec 31, 2018 at 13:39

Sanpas

1,18010 silver badges30 bronze badges

12 Comments

Helen Tekie Over a year ago

thank you but in my case How should I reform my Linq queary ? It doesn't work like if I code Product pr = new Product(); Pr.productName = N'txtproduct.trim()' ?? Like this?

Sanpas Over a year ago

I it's realy god question i'm going to loking the way you rare using C# ? I think that can be respond i'have one answer with special configuration EntityFramework stackoverflow.com/questions/12632240/…

Sanpas Over a year ago

@HelenTekie please you can share your insert instruction :). Thank you in advance.

Helen Tekie Over a year ago

sanches , I have edited my question and showing excatly how I do when I insert. I hope I understod your question

Sanpas Over a year ago

@HelenTekie Thanks you. For insert into database your are using entity framework ? Have you a edmx table or other file that equivalent (table definition for the entity framework) ? You have look at the previous link ?

|

level3looper · Accepted Answer · 2018-12-31 15:27:35Z

0

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

edited Dec 31, 2018 at 15:27

answered Dec 31, 2018 at 13:51

level3looper

1,0411 gold badge8 silver badges10 bronze badges

2 Comments

Helen Tekie Over a year ago

How can I formulate in Linq ? db.products.where(Unicode(substring(f1,1,1)) == txtproducts.Text) Like that ????

level3looper Over a year ago

@HelenTekie Sorry. I'm not familiar with Linq. I think this idea is a bit complicated - but if it works - maybe you can make a SQL stored procedure and execute that through Linq? The idea is to parse your search string to individual Unicode integer values for each character.

Collectives™ on Stack Overflow

Unable to Insert Char(63) by SQL query

4 Answers 4

3 Comments

6 Comments

12 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

6 Comments

12 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related