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

elfenliedtopfan5 121 Reputation points
2021-03-06T00:36:34.317+00:00

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,

p2IUko1.png

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.

LfzQBye.png

any help would be much appreciated kind regards
elfenliedtopfan5

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,265 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,601 questions
0 comments No comments
{count} votes

Accepted answer
  1. Wade Gausden 166 Reputation points
    2021-03-06T01:25:15.133+00:00

    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); 
    
    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Karen Payne MVP 35,376 Reputation points
    2021-03-06T01:25:10.877+00:00

    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;
    
    1 person found this answer helpful.
    0 comments No comments