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

Question

question

elfenliedtopfan5-3426 asked · Mar 06 at 12:36 AM

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

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

sql-server-general dotnet-csharp

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2021-03-06T01:25:15.133Z

WadeGausdenNZ answered · Mar 06 at 01:25 AM

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 ·

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

elfenliedtopfan5-3426 · Mar 06 at 01:35 AM

Thank you i see what i did when you explained it insted of having that as a litreal i wrapped it in quotes as such and put it in as plane text when that was part of the sql so needs to be basicly not a string haha,

thank you for you reply much appreiated.

0 ·

Answer 2 · 2021-03-06T01:25:10.877Z

karenpayneoregon answered · Mar 06 at 01:25 AM

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;

·

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question