Sql Exception no displaying in messagebox

Question

I'm getting a SQL exception when trying to insert a duplicate but the SQL error is not being sent to the message box. I can see the error in the debug.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using static AccountManager.productinsert.BusinessLogic;
namespace AccountManager
{
 public partial class ProductAdd : Form
 {
 public ProductAdd()
 {
 InitializeComponent();
 }
 public void CleartextBoxes1()
 {
 ProductId.Clear();
 ProductName.Clear(); 
 ProductPrice.Clear();
 }
 private void BtnClose_Click(object sender, EventArgs e)
 {
 this.Close();
 }
 private void BtnSubmit_Click(object sender, EventArgs e)
 {
 try
 {
 var product = new ProductDetails();
 product.ProductId = ProductId.Text;
 product.ProductName = ProductName.Text;
 product.ProductPrice = ProductPrice.Text;
 AddProduct(product);
 MessageBox.Show("Product Created!");
 CleartextBoxes1();
 }
 catch (SqlException)
 {
 MessageBox.Show("Product Already Exists");
 }
 }
 }
}

The error from the debug is

Exception Exception caught: 'System.Data.SqlClient.SqlException' in AccountManager.exe ("Violation of UNIQUE KEY constraint 'UQ__Products__B40CC6CC7010A856'. Cannot insert duplicate key in object 'dbo.Products'. The duplicate key value is (1). The statement has been terminated.") System.Data.SqlClient.SqlException

Code for add product as requested

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace AccountManager
{
 class productinsert
 {
 public class BusinessLogic
 {
 public static void AddProduct(ProductDetails details)
 {
 using (SqlConnection openCon = new SqlConnection("Data Source=.;Initial Catalog=AccountMGR;Integrated Security=True;"))
 {
 var statement = "insert into Products(" +
 " ProductId, ProductName, Price)" +
 " values(" +
 " @ProductId," +
 " @ProductName," +
 " @Price)";
 using (SqlCommand queryInsert = new SqlCommand(statement))
 {
 queryInsert.Connection = openCon;
 queryInsert.Parameters.Add("@ProductId", SqlDbType.VarChar, 30).Value = details.ProductId;
 queryInsert.Parameters.Add("@ProductName", SqlDbType.VarChar, 30).Value = details.ProductName;
 queryInsert.Parameters.Add("@Price", SqlDbType.VarChar, 30).Value = details.ProductPrice;
 openCon.Open();
 try
 {
 queryInsert.ExecuteNonQuery();
 }
 catch (SqlException)
 {
 }
 }
 }
 }
 public class ProductDetails
 {
 public string ProductId { get; set; } = "";
 public string ProductName { get; set; } = "";
 public string ProductPrice { get; set; } = "";
 }
 }
 }
}

• CodeCaster: And what happens if you hit F5 to continue the debugger?
• Kevin: catch (SqlException ex) { MessageBox.Show("Product Already Exists. " + ex.Message); }
• John: @Kevin you should post this as an answer so it can be accepted and you can win fun and amazing stuff!
• Kobi: Can you please post the source of AddProduct? It is possible it is wrapping or hiding the exception.
• Lennart Stoop: If you want to catch and show the exception in the UI, you will have to remove the empty try/catch block in the business logic class

Answer 1

Try this

try
{
 SaveData();
}
catch (Exception ex)
{
 var sqlException = ex.InnerException as System.Data.SqlClient.SqlException;
 if (sqlException.Number == 2601)
 {
 MessageBox.show("Product Already Exists");
 }
 else
 {
 MessageBox.show(ex.Message());
 }
}

2627 is for Unique constraint error

2601 is for Duplicated key row error

I hope this will help you!

Answer 2

You should throw exception to catch if you have used try catch

try
{
 queryInsert.ExecuteNonQuery();
}
catch 
{
 throw;
}

Answer 3

you should catch the SqlException before the General Exception. and define an object instance of your SqlException, the below code would solve your problem:

try
{
 queryInsert.ExecuteNonQuery();
}
catch (SqlException e)
{
 if (e.Number == 2601)
 {
 MessageBox.Show("Your Record is already exists");
 }
 else
 {
 MessageBox.Show(e.Message);
 }
}
catch(Exception e)
{
 // other kind of exception
}

Notes:

2601 means Duplicated key row error

2627 means Unique constraint error