FormView, SqlDataSource and handling exceptions

[Rob]

Hey all,

So.. a simple FormView/SqlDataSource to handle inserting records into a
table. The table has a primary key that the user enters (eg DiscountCode).
If the user enters a duplicate the database complains about a primary key
violation (which is what I want) and an exception is thrown. The OnInserted
event of the SqlDataSource provides access to the exception so, presumably,
you can provide nice handling for various errors.

For example:

protected void FormView1_ItemInserted(object sender,
FormViewInsertedEventArgs e)
{
//
// If there wasn't an exception the record was added successfully
//
if (e.Exception == null)
{
Response.Clear();
Response.Redirect("HappyLand.aspx");
Response.End();
}

//
// An exception occurred. Handle PK violations only
//
System.Data.SqlClient.SqlException se = e.Exception as
System.Data.SqlClient.SqlException;
if (se != null)
{
if (se.Number == 2627)
{
e.ExceptionHandled = true;
}
}
}

Now... what I can't seem to figure out is a "good" way of actually handling
the error. What I'd like to do is indicate that the page is invalid and have
a validation summary displayed along with a polite error message (ala
"Cannot insert duplicate record!").

However... even if I include a call to Page.Validate() which fires up a
custom validator that "knows" a duplicate record was just inserted.... the
summary does not display and the other controls reset to empty (i.e. the
values the user just entered are lost).

Here's what I mean:

private bool _Duplicate = false;

protected void FormView1_ItemInserted(object sender,
FormViewInsertedEventArgs e)
{
_Duplicate = false;

//
// If there wasn't an exception the record was added successfully
//
if (e.Exception == null)
{
Response.Clear();
Response.Redirect("AdminDiscountsList.aspx");
Response.End();
}

//
// An exception occurred. Handle PK violations only
//
System.Data.SqlClient.SqlException se = e.Exception as
System.Data.SqlClient.SqlException;
if (se != null)
{
if (se.Number == 2627)
{
_Duplicate = true;
Page.Validate();
e.ExceptionHandled = true;
}
}
}

protected void CustomValidator1_ServerValidate(object source,
ServerValidateEventArgs args)
{
args.IsValid = !_Duplicate;
}

So... what is the correct way of handling a PK violation using FormView and
SqlDataSource?

Regards,

Rob

 

[Guest]

What i would do is check for the primary key in the database in the
ItemInserting event handler and then set e.Cancel to true, that way an
excpetion never gets generated and you return to the FormView in inserting
mode with all values maintained. You can also put up some error text in a
label control as well. It probably gives you more overhead than handling the
exception but it is neat and works. An even better way would be to not allow
the user to specify the primary key. In my opinion there are very few good
reasons for allowing this, the only one i can think of off hand is username
in an acl type table.

 

[Rob]

What i would do is check for the primary key in the database in the
ItemInserting event handler and then set e.Cancel to true, that way an
excpetion never gets generated and you return to the FormView in inserting
mode with all values maintained.

A couple of thoughts on this... checking first will not prevent a PK
violation... between the time the check is done and the time the insert is
done, another user can insert a record. Very unlikely but possible. I'm
hoping for a "perfect" solution (if possible).

Secondly regardless of the advisability of letting users enter primary key
values (which I tend not to allow... just in the instance I goofed) the
business rule is "cannot have duplicate 'code values'". So whether or not
the 'code value' is a PK or not, the database will throw a constraint
violation (uniqueness).

So... I'd still like an actual solution. Though maybe I'll have to hide the
FormView, display a message about a duplicate, and the user can click Back
on the browser. Which I don't like much either... I'd rather have a
consistent mechanism for reporting "validation" problems to a user (ala a
ValidationSummary).

Thanks!

Rob

 

[Guest]

The way i see it the main rpoblem with catching the exception is that as far
as ASP is concerned the DataInsert has completed, so all the values entered
are lost.

If you do it the way i sugested, check every value that has to be unique
according to your busines rules and catch the exception any way. That way
you will not lose the entered values in almost all circumstances. In the
rare occaision that a duplicate value is inserted in between you checking for
it and the Insert query actually tring to insert it then the exception will
be caught and dealt with but the user will lost the values they entered (very
very unlikely to actually happen).

Alternatively, so long as you are doing the insert in a stored procedure, if
you make the parameters two way (input and output) then you could catch the
exception, flip the FormView back into Insert mode, and manually re-populate
the values of the controls in the form view with the value from the
parameters returned from the stored procedure and also display an error.
This require quite a bit more code than the other method but it should be
more efecient because you are only making one call to the data base

 

[Rob]

almost all circumstances. In the rare occaision

(very very unlikely to actually happen).

hehehe... famous last words

I will likely do a validation check for duplicates and handle the exception
by displaying a "oops duplicate record, click back and correct" label
instead of the formview.

I guess I was hoping that because the formview/sqldatasource makes the
exception available, and has a HandledException flag, that there would be a
way to treat the exception as a validation error and represent the same
information. I guess I don't see that as a stretch... treat exception like a
validation error... seems like a likely enough approach.

In otherwords allow an application to gracefully handle database constraint
exceptions (eg. consistant ui/error messaging) when using a
formview/sqldatasource.

Regards,

Rob