c# mysql check if value exists retruning 0 when value exists.

Question

Hello all,

i have been working on stopping duplicate entries going into my database and adding unique computer info like mouther bord serials as each pc motherboard will have a unique id,

so once i add them and have created database entryies and populated it.,

can see form below image,

and with this code here,
public static bool checkAppSetup(string Name, string row)
{

            //" SELECT EXISTS(SELECT * from Applications_Setup WHERE '" + row + "'"+"="+"'" + Name + "')"   
            MySqlCommand cmd = new MySqlCommand("SELECT COUNT(*) FROM Applications_Setup WHERE '" + row + "'='" + Name + "'", conn); 
            object obj = cmd.ExecuteScalar();
            if (Convert.ToInt32(obj) > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }

and have tried the same code on mysql workbench and got a result.

any help would be much appreciated kind regards
elfenliedtopfan5

Answer 1

Hi there,

Your issue is because you have additional quotes wrapped around your column name.

What you want is :

SELECT COUNT(*) FROM Applications_Setup WHERE ComputerSerial = 'ABC'

But what your code is actually executing is :

SELECT COUNT(*) FROM Applications_Setup WHERE 'ComputerSerial' = 'ABC'

For the latter, it's comparing two strings so will always return false (Unless you have a column value the same as the column name I guess!).

Simply change this line :

MySqlCommand cmd = new MySqlCommand("SELECT COUNT(*) FROM Applications_Setup WHERE '" + row + "'='" + Name + "'", conn); 

To

MySqlCommand cmd = new MySqlCommand("SELECT COUNT(*) FROM Applications_Setup WHERE " + row + " = '" + Name + "'", conn); 

Answer 2

You can prevent duplicates using a UNIQUE INDEX and check for duplicates.

Taken from second link

SELECT 
    col, 
    COUNT(col)
FROM
    table_name
GROUP BY col
HAVING COUNT(col) > 1;