Gridview update not throwing sql duplicate error

Jul 23 2007 9:16 AM

I have a indexed column in my ms-sql table that doesn't allow for duplicates. I wrote a add page that works fine. I use the sqldatasource_inserted method to catch the sql error. When trying to wirte the edit page, I don't get the error thrown. If I click on Update, it will NOT update the database with the new value, but it won't throw an error either. It will actually throw the "Updated" text. I would like to let the user know that it hasn't been updated due to the fact that they used something that has already been used.

I am using a sqldatasource to bind a gridview and use the standard editing capabilities.

Do I have to look somewhere else other than the datasources updated event? I assumed that this was the proper area because with the detailsview, I use the inserted event of the datasource and that works fine.

Here is my code-behind for the page (removed most comments to reduce size):
public partial class Inventory_Companies : System.Web.UI.Page
{   // instantiate a variable to use as a trigger to help handle multiple errors
    protected bool isvalid = false;
    protected string form_desc = "company";
    protected string required_fields = "company name";

    protected void Page_Load(object sender, EventArgs e)
    {  lblError.Text = "";
        lblError.BackColor = System.Drawing.Color.Transparent;
    }

    protected void SqlDataGridView1_Updated(object sender, SqlDataSourceStatusEventArgs e)
    {   // if the exception isn't null there is a problem
        if (e.Exception != null)
        {   // create a new sql exception so that we can get the error code
            SqlException ex = e.Exception as SqlException;
            if (ex != null)
            {   // start building the error because we know we will need it at this point
                lblError.Text = "<font color=red>";
                // check to see if it is because of the unique constraint on the table in the db
                if (ex.Number == 2627)
                {   // if it is. Let them know that they can't use a duplicate value
                    lblError.Text += "The " + required_fields + " for the " + form_desc + " is already in use.<br>";
                    lblError.Text += "Try again using a different name.";
                }
                else
                {   // if not, we need to let them know we don't know why and show them the error given
                    lblError.Text += "There was an unknown error updating the " + form_desc + ".<br>";
                    lblError.Text += "Please contact the Information Services department and give them this error";
                    lblError.Text += "<br>" + e.Exception.Message.ToString() + "<br>";
                } // end the label text and set the background to a more noticable color
                lblError.Text += "</font>";
                lblError.BackColor = System.Drawing.Color.Linen;
                e.ExceptionHandled = true; // let the compiler know we handled the error
            }
        }
        else
        { // no sql errors, so set the trigger to check for the gridview row update for more errors
            isvalid = true;
        }
    }

    protected void SqlDataGridView1_Deleted(object sender, SqlDataSourceStatusEventArgs e)
    {   // if e is not null, then there is an error and we have to deal with it
        if (e.Exception != null)
        {   // set the error text and background color. Also tell the compiler that we handled the error
            lblError.Text = "<font color=red>";
            lblError.Text += "There was an unknown error deleting the device.<br>";
            lblError.Text += "Please contact the Information Services department and give them this error";
            lblError.Text += "<br>" + e.Exception.Message.ToString() + "<br>";
            lblError.Text += "</font>";
            lblError.BackColor = System.Drawing.Color.Linen;
            e.ExceptionHandled = true;
        }
        else
        {   // no errors, so we can set the trigger to let the gridview check for more asp.net related errors
            isvalid = true;
        }
    }

    protected void GridView1_RowUpdated(object sender, GridViewUpdatedEventArgs e)
    {   // if the trigger hasn't been tripped, we can check for errors, otherwise we already have sql errors that we dealt with
        if (isvalid)
        {   // if exception isn't null, there is an error
            if (e.Exception != null)
            {   // set the text to let them know and display the actual error message so they can let us know the error
                lblError.Text = "<font color=red>There was a problem updating the " + form_desc + ". Recheck your information.";
                lblError.Text += "<br>Error: " + e.Exception.Message.ToString() + "</font>";
                lblError.BackColor = System.Drawing.Color.Linen; // change to a more noticable color
                e.ExceptionHandled = true; // tell the compiler that we took care of the error, so it doesn't have to
            }
            else
            {
                lblError.Text = "<font color=black>Updated</font>";
            }
        }
        else
        {
            // When an exception occurs, keep the DetailsView control in insert mode.
            e.KeepInEditMode = true;
        }
    }

    protected void GridView1_RowDeleted(object sender, GridViewDeletedEventArgs e)
    {   // if trigger hasn't been triggered, there were no sql errors, so we can check for asp.net errors
        if (isvalid)
        {   // if exception isn't null, then there are errors
            if (e.Exception != null)
            {   // set the label to tell them there was something wrong and display the message
                lblError.Text = "<font color=red>There was an error deleting the " + form_desc + " . Please contact Information Services.";
                lblError.Text += "<br>Error: " + e.Exception.Message.ToString() + "</font>";
                lblError.BackColor = System.Drawing.Color.Linen;
                e.ExceptionHandled = true;
            }
        }
    }
}

 


Answers (1)