Displaying an error message during insert execution

Question

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

The problem with this question is that there are at least 3 of them. One question about how to display an error message. Another question is about how to rollback a db transaction. The third question is about how you write your algorithm to do all this. — Sam I am says Reinstate Monica, Oct 06 '14 at 18:23
@Sam I am, well my main problem is how I am going to display an error message or just a message if the student is already assigned with "that" position. The last 2 problems that you mentioned. . . is just a minor problem compared to the first one ... xD — Justin Aizengard, Oct 06 '14 at 18:31

competent_tech · Answer 1 · 2014-10-06T18:30:53.697

2

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");
 }

edited Oct 06 '14 at 18:30

answered Oct 06 '14 at 18:24

competent_tech

44,465
11
90
113

2

I really, really hate it when people downvote without providing an explanation. This smacks of cowardice, laziness, ignorance, or all of the above. This is a site that is supposed to help people with programming problems and if you don't explain your reasons, people will never learn. – competent_tech Oct 06 '14 at 18:35
thanks . . . I'll analyze this sample that you've provided xD – Justin Aizengard Oct 06 '14 at 18:44
I'm no guru, so perhaps your approach is just fine...but I usually try to `SELECT COUNT(*)...` instead, just to avoid the possibility of a `null` of any sort. My way, I _always_ get a valid integer value. – DonBoitnott Oct 06 '14 at 19:04
1

@DonBoitnott: That is a valid point, but I tend to use SELECT 1 because I almost always use it in SQL Server in conjunction with an EXISTS statements (IF EXISTS(SELECT 1 FROM ...) which will short circuit the execution on the first record that it finds rather than having to iterate through all matches to find the count. MySql may behave differently, but I have found that this approach does improve performance in SQL Server. – competent_tech Oct 06 '14 at 19:08

Displaying an error message during insert execution

1 Answers1