Check if existing or not in mysql database

Question

I'm trying to insert a data to the MySQL database but before that, I'm trying to check if that data is existing or not. If not, it will insert but if yes it will show a message. I've run this code but it only says "Existing!" even though it doesn't exist.

try
{
    conn.Open();
    string sql12 = "SELECT * FROM courseandorg where connID = ?connID";
    MySqlCommand cmd12 = new MySqlCommand("INSERT INTO courseandorg VALUES (connID, '" + lblCourseAbbrev.Text + "','" + lblOrgName.Text + "','" + lblOrgAbbrev.Text + "', '" + cboStatus.Text + "','" + txtquorum.Text + "')");  
    MySqlCommand cmd14 = new MySqlCommand(sql12, conn);
    cmd14.Parameters.AddWithValue("@connID", txtCOConnID.Text);
    int count = Convert.ToInt32(cmd14.ExecuteScalar());    

    if (count > 0)
    {
        MessageBox.Show("Existing!");
    }
    else
    {
        cmd12.ExecuteNonQuery();
        MessageBox.Show("Successfully save!");
    }

    conn.Close();
}

catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}
finally
{
    if (conn.State == ConnectionState.Open)
        conn.Close();
}

You are selecting the **whole** table. If there are any records, then print Existing. Is that what you want? — Vini Brasil, Sep 24 '17 at 10:13
You select everything. As soon as a single record exists, the function will return true. Maybe you tried to do `select count(*)`, but note that this can be very inefficient on large tables. It's better to do an `insert on duplicates ignore` statement, or an `insert .. select where not exists`. That way you have only one round trip to the database, and less risk of race conditions when others write records too. — GolezTrol, Sep 24 '17 at 10:14
While we're here it's also worth pointing out that your code is wide open to SQL injection. And that your variable names are just inviting bugs and unexpected behavior. — David, Sep 24 '17 at 10:16

Salah Akbari · Answer 1 · 2017-09-24T10:36:56.213

1

Your select statement has not a where clause so it retrieve all rows. You should filter your search so that it just check for the existing of the record that you are trying to insert:

string sql12 = "SELECT * FROM courseandorg where connID = @connID";
//Or I think in MySQL you should use ? instead of @ connID = ?connID"
cmd14.Parameters.AddWithValue("@connID",connIDValue);

Also this kind of string concatenation is open for SQL injection. Try parameterized queries instead.

edited Sep 24 '17 at 10:36

answered Sep 24 '17 at 10:15

Salah Akbari

36,933
10
58
91

I tried to run it again with the code as you said but it says "Fatal error during command execution" I update the code, take a look. – Michelle Sep 24 '17 at 10:55
I updated the code again but it doesn't work. It says the connection must be valid and open but it is already opened and i closed it. – Michelle Sep 24 '17 at 11:04
it says the connection must be valit and open again. – Michelle Sep 24 '17 at 11:07
@Michelle This is why you should always use [using](https://docs.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/using-statement) statement. – Salah Akbari Sep 24 '17 at 11:12
conn.Open(); string sql12 = "SELECT * FROM courseandorg WHERE connID = '" + txtCOConnID.Text + "'"; MySqlCommand cmd12 = new MySqlCommand("INSERT INTO courseandorg VALUES (connID, '" + lblCourseAbbrev.Text + "','" + lblOrgName.Text + "','" + lblOrgAbbrev.Text + "', '" + cboStatus.Text + "','" + txtquorum.Text + "')"); MySqlCommand cmd14 = new MySqlCommand(sql12, conn); int count = Convert.ToInt32(cmd14.ExecuteScalar()); conn.Close(); – Michelle Sep 24 '17 at 11:17
conn.Open(); if (count > 0) { MessageBox.Show("Taken na to!"); } else { cmd12.ExecuteNonQuery(); MessageBox.Show("Save na! Yehey!"); } conn.Close(); } – Michelle Sep 24 '17 at 11:18
catch (Exception ex) { MessageBox.Show(ex.Message); } finally { if (conn.State == ConnectionState.Open) conn.Close(); } – Michelle Sep 24 '17 at 11:19
It's the same again. – Michelle Sep 24 '17 at 11:19

Check if existing or not in mysql database

1 Answers1