C# Mysql create new row but if exist don't

Question

I have a mysql table and my c# program includes something simular like login/register.

I am using this methods for openning connection, close it and some more stuff : http://www.codeproject.com/Articles/43438/Connect-C-to-MySQL

public void Insert(string id,string ip)
        {
            string query = "INSERT INTO skinsdb (id, acces, ip) VALUES('" + id + "',  1 , '" + ip + "')";

            //open connection
            if (this.OpenConnection() == true)
            {
                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //Execute command
                cmd.ExecuteNonQuery();

                //close connection
                this.CloseConnection();
            }
            else MessageBox.Show("code 18");
        }

I am simply using this for new row BUT i want if "id" already exist, just increase 1 acces(int) cell of his row. Thank you.

Possible duplicate of [SQL - IF EXISTS UPDATE ELSE INSERT INTO](http://stackoverflow.com/questions/15383852/sql-if-exists-update-else-insert-into) — Shadow, Aug 23 '16 at 12:35

apomene · Answer 1 · 2016-08-23T12:27:28.420

0

You should use SQL Parameters. You can achieve your goal by the following query:

    string query =
   "IF EXISTS (SELECT * FROM  skinsdb WHERE id=@id)
        UPDATE skinsdb SET acces=acces+1 WHERE id=@id
    ELSE
        INSERT INTO skinsdb (id, acces, ip) VALUES(@id,1,@ip)";
                //open connection
    if (this.OpenConnection() == true)
    {
       //create command and assign the query and connection from the constructor
     MySqlCommand cmd = new MySqlCommand(query, connection);
     cmd.Parameters.AddWithValue("@id",id);
     cmd.Parameters.AddWithValue("@ip",ip);
     //Execute command
     cmd.ExecuteNonQuery();
     //close connection
     this.CloseConnection();
     }

edited Aug 23 '16 at 12:27

answered Aug 23 '16 at 12:12

apomene

14,282
9
46
72

Can you use If statements like that outside of a stored function or procedure? – PaulF Aug 23 '16 at 12:40
@Paul, yes you can. I have tested the above query – apomene Aug 23 '16 at 12:43
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS (SELECT * FROM skinsdb WHERE id=@id) UPDATE skinsdb SET acces' I got this error – adrasteia Aug 23 '16 at 13:02
@apomene - which version of MySQL are you using - I also get the exception reported by adrasteia & similar if I try from the command line in workbench. – PaulF Aug 23 '16 at 13:10
5.5.45-37.4 thats what my hosting said – adrasteia Aug 23 '16 at 13:13
Googling that version indicates that you may be using Percona server rather than MySQL - that may be why your query works. MySQL does not support the use if IF statements outside of stored functions/procedures - hence the invalid syntax error. – PaulF Aug 23 '16 at 13:18
@Paul - i also googled it and i saw the same. Problem propably about it i am trying to solve with my hosting company. Thank you both of you :) – adrasteia Aug 23 '16 at 13:25
@adrasteia: sorry I was directing my original question & comment on version to apomene - I didn't spot it was you that answered. The behaviour you are getting is exactly what I would expect from MySQL & as Percona claims to be fully compatible then I would expect that too - though I wasn't sure whether it had some extensions that would only work with Percona. Have you looked at my solution? – PaulF Aug 23 '16 at 13:29
@PaulF - I checked but i didn't understand if it's doing what exactly i want. I have 1 more unique, AUTO_INCREMENT column. I just tried your solution but it always generates new row... – adrasteia Aug 23 '16 at 13:40
You must have a unique index on the 'id' column then when you try to insert a duplicate value rather than generating an error message the on duplicate update will run. I have just tested it with an auto-increment primary key with index in addition to the unique index on id & the query is incrementing the acces column of that row. – PaulF Aug 23 '16 at 14:05
@apomene - can you check code again if possible? Cuz i research a bit more and i see problem could be about the code itself but not MySql server like in this link http://stackoverflow.com/questions/29446697/1064-you-have-an-error-in-your-sql-syntax-check-the-manual-that-corresponds – adrasteia Aug 23 '16 at 14:05
@apomene - Finally, i can't use your solution but i solved it by using PaulF's solution but thank you for your help atleast i figure out how this :"cmd.Parameters.AddWithValue("@ip",ip);" works . – adrasteia Aug 23 '16 at 14:25

score 0 · Accepted Answer · answered Aug 23 '16 at 12:29

0

If you have just a single unique index on id then you can use

"INSERT INTO skinsdb (id, acces, ip) VALUES('" + id + "',  1 , '" + ip + "') 
ON DUPLICATE KEY UPDATE acces=acces+1";

answered Aug 23 '16 at 12:29

PaulF

6,673
2
18
29

I set my "id" column as "Unique" and "Index" then this solved my problem. Thank you. – adrasteia Aug 23 '16 at 14:22
What you will see every time you get a duplicate entry is a gap in the auto-increment key values - but that should not be an issue. – PaulF Aug 23 '16 at 14:32

C# Mysql create new row but if exist don't

2 Answers2