question

elfenliedtopfan5-3426 avatar image
0 Votes"
elfenliedtopfan5-3426 asked ·

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,

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-generaldotnet-csharp
10 |1000 characters needed characters left characters exceeded

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

WadeGausdenNZ avatar image
1 Vote"
WadeGausdenNZ answered ·

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

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

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 Votes 0 ·
karenpayneoregon avatar image
1 Vote"
karenpayneoregon answered ·

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

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