none
Primary Key exception still occurs prior to my messagebox in the catch being displayed RRS feed

  • Question

  • Hi there,

    Im using Visual Studio 2019 and .NET 4.5.  Following code still displays the Primary key exception error message despite me handling the error in my code.  I dont think it did this in 2017 and 4.0 framework.  Please help me suppress it.

                        //EmployeeID already exists exception handler
                        if (ex.HResult == unchecked((int)0x80131904))
                        {
                            MessageBoxResult messageBoxResult = MessageBox.Show("EmployeeID: " + txtEmpTid.Text + " already exists.  Would you like to update the existing record?", "Warning", MessageBoxButton.YesNo);
                            if (messageBoxResult == MessageBoxResult.Yes)
                            {
                                using (SqlConnection sqlcon1 = new SqlConnection(ConfigurationManager.ConnectionStrings["trainingConnectionString"].ConnectionString))
                                {
                                    using (SqlCommand upd = new SqlCommand("sp_UpdateEmployee", sqlcon))
                                    {
                                        //Create SQL string for updating existing employee and execute
                                        upd.CommandType = CommandType.Text;
                                        upd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = txtEmpTid.Text;
                                        upd.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = txtEmpFnm.Text;
                                        upd.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = txtEmpLnm.Text;
                                        upd.Parameters.Add("@Department", SqlDbType.NVarChar).Value = cbxDpt.Text;
                                        upd.Parameters.Add("@Status", SqlDbType.NVarChar).Value = cbxSts.Text;
                                        upd.Parameters.Add("@Chairperson", SqlDbType.NVarChar).Value = chbEmpSup.Text;
                                        upd.Parameters.Add("@InductedByID", SqlDbType.NVarChar).Value = cbxIby.Text;
                                        upd.Parameters.Add("@Induction", SqlDbType.Date).Value = dtpInd.Text;
                                        upd.Parameters.Add("@Position", SqlDbType.NVarChar).Value = cbxPos.Text;
                                        upd.Connection = sqlcon1;
                                        sqlcon1.Open();
                                        upd.ExecuteNonQuery();
                                        sqlcon1.Close();
                                        //Display message on success
                                        string success = "Employee: " + txtEmpFnm.Text + " " + txtEmpLnm.Text + " was updated Successfully!";
                                        string title = txtEmpFnm.Text + " " + txtEmpLnm.Text;
                                        MessageBox.Show(success, title);
                                    }
                                }
                            }


    Wednesday, September 18, 2019 12:20 AM

Answers

  • Umm.... At the risk of asking a really stupid question, the problem couldn't be the call to MessageBox.Show, by any chance?

                    catch (SqlException ex)
                    {
                        MessageBox.Show(ex.ToString()); // This line, perhaps?
                        //EmployeeID already exists exception handler
                        if (ex.HResult == unchecked((int)0x80131904))
                        {

    • Marked as answer by Corbillion Wednesday, September 18, 2019 5:52 AM
    Wednesday, September 18, 2019 5:48 AM

All replies

  • Hello,

    Not seeing all code I would think that you would use a try/catch with catch (SqlException sqlex) section in the try followed by a general Exception section. I don't know the error code but is would be easy to learn it then check for it then handle it.

    Something like this

    using System;
    using System.Data.SqlClient;
    
    namespace Operations.Classes
    {
        public class Operations
        {
    
            public bool AddCustomer(
                string firstName, 
                string lastName, 
                string address, 
                string city, 
                string state, 
                string zipCode, 
                ref int primaryKeyValue)
            {
    
                using (var cn = new SqlConnection { ConnectionString = "TODO" })
                {
                    using (var cmd = new SqlCommand { Connection = cn })
                    {
                        cmd.CommandText = "INSERT INTO Customer (FirstName,LastName,[Address],City,[State],ZipCode) " + 
                                          "VALUES (@FirstName,@LastName,@Address,@City,@State,@ZipCode)";
                        try
                        {
                            cmd.Parameters.AddWithValue("@FirstName", firstName);
                            cmd.Parameters.AddWithValue("@LastName", lastName);
                            cmd.Parameters.AddWithValue("@Address", address);
                            cmd.Parameters.AddWithValue("@City", city);
                            cmd.Parameters.AddWithValue("@State", state);
                            cmd.Parameters.AddWithValue("@ZipCode", zipCode);
                            cn.Open();
    
                            var result = cmd.ExecuteNonQuery();
                            if (result == 1)
                            {
                                cmd.CommandText = "Select @@Identity";
                                primaryKeyValue = Convert.ToInt32(cmd.ExecuteScalar());
                            }
    
                        }
                        catch (SqlException sqlex)
                        {
                            // TODO - check error code and react if possible
                        }
                        catch (Exception ex)
                        {
                            // TODO
                        }
                    }
                }
    
                return true; // TODO
    
            }
        }
    }
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, September 18, 2019 2:02 AM
    Moderator
  • Hi Karen,

    Thanks for your reply, I have included all code, the original code is part of my catch block.  It is supposed to catch the primary key constraint error from my SQL database and execute the query as an update instead of an insert.  It works fine but displays the full exception before my success message.  I want to suppress the exception message.

    {
                    try
                    {
                        //Create SQL string for inserting new employee data and execute
                        ins.CommandType = CommandType.StoredProcedure;
                        ins.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = txtEmpTid.Text;
                        ins.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = txtEmpFnm.Text;
                        ins.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = txtEmpLnm.Text;
                        ins.Parameters.Add("@Department", SqlDbType.NVarChar).Value = cbxDpt.Text;
                        ins.Parameters.Add("@Status", SqlDbType.NVarChar).Value = cbxSts.Text;
                        if(chbEmpSup.Checked == true)
                        {
                            ins.Parameters.Add("@Chairperson", SqlDbType.Bit, 1).Value = 1;
                        }
                        else if (chbEmpSup.Checked == false)
                        {
                            ins.Parameters.Add("@Chairperson", SqlDbType.Bit, 1).Value = 0;
                        }
                        ins.Parameters.Add("@InductedByID", SqlDbType.NVarChar).Value = cbxIby.Text;
                        ins.Parameters.Add("@Induction", SqlDbType.Date).Value = dtpInd.Text;
                        ins.Parameters.Add("@Position", SqlDbType.NVarChar).Value = cbxPos.Text;
                        ins.Connection = sqlcon;
                        sqlcon.Open();
                        ins.ExecuteNonQuery();
                        //Display message on success
                        string success = "New Employee: " + txtEmpFnm.Text + " " + txtEmpLnm.Text + " was created Successfully!";
                        string title = txtEmpFnm.Text + " " + txtEmpLnm.Text;
                        MessageBox.Show(success, title);
                        dgvEmp.Refresh();
                    }
                    catch (SqlException ex)
                    {
                        MessageBox.Show(ex.ToString());
                        //EmployeeID already exists exception handler
                        if (ex.HResult == unchecked((int)0x80131904))
                        {
                            MessageBoxResult messageBoxResult = MessageBox.Show("EmployeeID: " + txtEmpTid.Text + " already exists.  Would you like to update the existing record?", "Warning", MessageBoxButton.YesNo);
                            if (messageBoxResult == MessageBoxResult.Yes)
                            {
                                using (SqlConnection sqlcon1 = new SqlConnection(ConfigurationManager.ConnectionStrings["trainingConnectionString"].ConnectionString))
                                {
                                    using (SqlCommand upd = new SqlCommand("sp_UpdateEmployee", sqlcon))
                                    {
                                        //Create SQL string for updating existing employee and execute
                                        upd.CommandType = CommandType.StoredProcedure;
                                        upd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = txtEmpTid.Text;
                                        upd.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = txtEmpFnm.Text;
                                        upd.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = txtEmpLnm.Text;
                                        upd.Parameters.Add("@Department", SqlDbType.NVarChar).Value = cbxDpt.Text;
                                        upd.Parameters.Add("@Status", SqlDbType.NVarChar).Value = cbxSts.Text;
                                        upd.Parameters.Add("@Chairperson", SqlDbType.NVarChar).Value = chbEmpSup.Text;
                                        upd.Parameters.Add("@InductedByID", SqlDbType.NVarChar).Value = cbxIby.Text;
                                        upd.Parameters.Add("@Induction", SqlDbType.Date).Value = dtpInd.Text;
                                        upd.Parameters.Add("@Position", SqlDbType.NVarChar).Value = cbxPos.Text;
                                        upd.Connection = sqlcon1;
                                        sqlcon1.Open();
                                        upd.ExecuteNonQuery();
                                        sqlcon1.Close();
                                        //Display message on success
                                        string success = "Employee: " + txtEmpFnm.Text + " " + txtEmpLnm.Text + " was updated Successfully!";
                                        string title = txtEmpFnm.Text + " " + txtEmpLnm.Text;
                                        MessageBox.Show(success, title);
                                        dgvEmp.Refresh();
                                    }
                                }
                            }
                        }
                    }
                }

    Here is the exception message it throws:



    • Edited by Corbillion Wednesday, September 18, 2019 5:40 AM
    Wednesday, September 18, 2019 5:27 AM
  • Umm.... At the risk of asking a really stupid question, the problem couldn't be the call to MessageBox.Show, by any chance?

                    catch (SqlException ex)
                    {
                        MessageBox.Show(ex.ToString()); // This line, perhaps?
                        //EmployeeID already exists exception handler
                        if (ex.HResult == unchecked((int)0x80131904))
                        {

    • Marked as answer by Corbillion Wednesday, September 18, 2019 5:52 AM
    Wednesday, September 18, 2019 5:48 AM
  • I think most probably the problem lies on sp_UpdateEmployee.

    It should do update to dbo.Employee table instead of insert if EmployeeId parameter is not "0". (Otherwise there won't be warning about PK violation on insert)

    Or maybe there is another SP sp_CreateEmployee or something like that which you should call instead.
    Wednesday, September 18, 2019 5:49 AM
    Answerer
  • Yes that was it, thanks, I must of left that in there when debugging.  Very new to all this.  Thanks Ante.
    Wednesday, September 18, 2019 5:52 AM
  • No worries.
    Wednesday, September 18, 2019 5:55 AM