How to get alert when Unique key is violated

Question

protected void Button1_Click(object sender, EventArgs e)
    {
         SqlConnection myConnection = new SqlConnection("server=VIVID-PC;Integrated Security = True;Database=SchoolDb");
        SqlCommand myCommand = new SqlCommand("Command String", myConnection);
        myConnection.Open();

        string firstText = TextBox1.Text;
        string SecondText = TextBox2.Text;
        string thirdText = TextBox3.Text;
        string fourthText = TextBox4.Text;



        myCommand = new SqlCommand("INSERT INTO SchoolDb_Student(StudentName,RollNo,Session,MobileNo)values('" + firstText + "','" + SecondText + "' , '" + thirdText + "','" + fourthText + "')", myConnection);
        myCommand.ExecuteNonQuery();

        myConnection.Close();

        Response.Redirect("/view.aspx");

    }

Answer 1

1. Use command with parameters to pass data to server.
2. Make sure you dispose connection and command (via using statement)
3. Store connection strings in config file
4. Do not create dummy command objects

Here is complete code:

using(var connection = new SqlConnection(connectionString))
using(var command = connection.CreateCommand())
{
    command.CommandText = 
       @"INSERT INTO SchoolDb_Student(StudentName,RollNo,Session,MobileNo)
         VALUES (@studentName, @rollNo, @session, @mobileNo)";

    command.Parameters.AddWithValue("studentName", TextBox1.Text);
    command.Parameters.AddWithValue("rollNo", TextBox2.Text);
    command.Parameters.AddWithValue("session", TextBox3.Text);
    command.Parameters.AddWithValue("mobileNo", TextBox4.Text);

    connection.Open();

    try
    {
        command.ExecuteNonQuery();
    }
    catch(SqlException e)
    {
        if (e.Message.Contains("Violation of UNIQUE KEY constraint"))
            // you got unique key violation
    }
}

Further considerations - improve naming in your code - TextBox1, TextBox2 etc says nothing to reader. Give them appropriate names, like StudentNameTextBox, RollNoTextBox etc. Also good practice is splitting data access and UI logic.

Answer 2

If the database detects a unique key violation, this line

myCommand.ExecuteNonQuery();

will throw an exception. You can catch that exception and proceed with your own code:

try
{
    myCommand.ExecuteNonQuery();
}
catch(Exception e)
{
    // right here, "something" went wrong. Examine e to check what it was.
}

Please note that your code is vulnerable to SQL injection attacks. You should be using command paramaters instead of building the SQL manually. In addition, you should be using using blocks (see here for details)

Answer 3

ExecuteNonQuery will throw an exception if it's unable to INSERT row into database. In your case, it's most likely an SqlException. Catch it.

Answer 4

use your returnType from ExecuteNonQuery() (Read the remarks part) to detect failure in insertion. you can use the exception or the no. of rows affected part

Try this :

try
{
... your rest of the code
...
int rowsAffected = myCommand.ExecuteNonQuery(); // Most probaboly it will throw exception in case of Unique key violation. If not, still no rows have been affected
if(rowsAffected<1) 
{
    //your Alert for no records inserted
}
else
{
    //your alert for successful insertion
}

}
catch(SqlException ex)
{
//check the exception and display alert
}
finally
{
   //release connection and dispose command object
}

Answer 5

As suggested in comment use command param.

try
{
   //Your other code 
  _myCommand.ExecuteNonQuery();
   myConnection.Close();
   Response.Redirect("/view.aspx");
}
catch(SqlException sqlExc)
{
 // Your popup or msg.
}

You also loop for different sql error in catch block.