CODEWITHSUNDEEP
c#mysqluser-interaction
Justin Aizengard
Justin Aizengard

Reputation: 92

Displaying an error message during insert execution

How do I display an error message if a student is already assigned with the position currently being inserted into the mysql database, then rollback the transaction?

If a different position is assigned, it should continue to check the next row.

Here is my code for insertion:

conn.Open();

MySqlTransaction mt = conn.BeginTransaction();

try {
    for (int cnt = 0; cnt <= lv1.Items.Count - 1; cnt++) {
        if (lv1.Items[cnt].SubItems[3].Text == " ")
            continue;

        string query = "insert into candidate(pid,s_id)values(@pid,@sid)";

        MySqlCommand cmd = new MySqlCommand(query, conn);

        cmd.Parameters.Add(new MySqlParameter("@pid", lv1.Items[cnt].SubItems[0].Text ));
        cmd.Parameters.Add(new MySqlParameter("@sid", lv1.Items[cnt].SubItems[2].Text));

        cmd.Transaction = mt;
        cmd.ExecuteNonQuery();
    }
    mt.Commit();
} catch (Exception error) {
    MessageBox.Show(error.Message);
    mt.Rollback();
}
conn.Close();

This is my dummy student table:

enter image description here

This is my dummy position table:

enter image description here

This is my dummy candidate table:

enter image description here

This is my listview control sample:

enter image description here

Upvotes: 0

Views: 271

Answers (1)

competent_tech
competent_tech

Reputation: 44931

Don't rely on an exception from the insert statement: if you don't insert anything, there is no need to perform a rollback.

Instead, check to see if the item already exists and only insert if it does not. If you need to tell the user that it already exists, you can show the message box.

For example:

// This is just an example; not sure what exact conditions you need
var cmdExists = new MySqlCommand("SELECT 1 FROM candidate WHERE pid = @pid");
cmdExists.Parameters.Add(new SqlParameter("@pid", lv1.Items[cnt].SubItems[0].Text));
if (cmdExists.ExecuteScalar() == DBNull.Value)
{
     string query = "insert into candidate(pid,s_id)values(@pid,@sid)";
     ...
 } else {
     MessageBox.Show("some error message that makes sense to your user");
 }

Upvotes: 2

Related Questions