Try catch on sql database error

Andy Woodward · March 13th, 2009, 07:21 AM

Hi Imar

I have come up with a problem in my website to do with database errors.
I created my website after reading your book.

In the database I put a unique constraint on a column called PinCode with datatype int.

In my website I have a page with a details view on it where users enter details of the new record.
When the user clicks the insert button the record gets added to the database.
To test the unique constraint I added a record with a duplicate entry in the PinCode column. When I clicked insert I didn't get an error message.
The site behaved the same way it does when the record was inserted correctly.

I checked the database and it didn't add the record so the constraint is working. What I need is a way to display an error when a user tries to insert a duplicate entry.

I believe I have to use a try...catch method. Where do I put this? Does it go in the code behind page. I am using c#.

The code for my page is:

Code:

 
<%@PageTitle="Telephony Admin-Management-Add New Pin"Language="C#"MasterPageFile="~/MasterPages/NewManagementMaster.master"AutoEventWireup="true"
CodeFile="AddNewPin.aspx.cs"Inherits="Management_AddNewPin" %>
<asp:ContentID="Content1"ContentPlaceHolderID="head"Runat="Server">
</asp:Content>
<asp:ContentID="Content2"ContentPlaceHolderID="cpMainContent"Runat="Server">
<asp:DetailsViewID="DetailsView1"runat="server"AutoGenerateRows="False"DataKeyNames="Id"
DataSourceID="SqlDataSource1"DefaultMode="Insert"Height="50px"Width="284px"
oniteminserted="DetailsView1_ItemInserted"oniteminserting="DetailsView1_ItemInserting"
onitemupdated="DetailsView1_ItemUpdated"
onitemupdating="DetailsView1_ItemUpdating"onpageindexchanging="DetailsView1_PageIndexChanging">
<Fields>
<asp:BoundFieldDataField="Id"HeaderText="Id"InsertVisible="False"ReadOnly="True"SortExpression="Id"/>
<asp:TemplateFieldHeaderText="FirstName"SortExpression="FirstName">
<EditItemTemplate>
<asp:TextBoxID="TextBox1"Width="150"runat="server"Text='<%# Bind("FirstName") %>'></asp:TextBox>
<asp:RequiredFieldValidator
ID="RequiredFieldValidator1"runat="server"ErrorMessage="Please enter a First Name"ControlToValidate="TextBox1">*</asp:RequiredFieldValidator>
</EditItemTemplate>
<InsertItemTemplate>
<asp:TextBoxID="TextBox1"Width="150"runat="server"Text='<%# Bind("FirstName") %>'></asp:TextBox>
<asp:RequiredFieldValidatorID="RequiredFieldValidator2"runat="server"ControlToValidate="TextBox1"ErrorMessage="Please enter a First Name">*</asp:RequiredFieldValidator>
</InsertItemTemplate>
<ItemTemplate>
<asp:LabelID="Label1"runat="server"Text='<%# Bind("FirstName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateFieldHeaderText="LastName"SortExpression="LastName">
<EditItemTemplate>
<asp:TextBoxID="TextBox2"Width="150"runat="server"Text='<%# Bind("LastName") %>'></asp:TextBox>
<asp:RequiredFieldValidatorID="RequiredFieldValidator3"runat="server"ControlToValidate="TextBox2"ErrorMessage="Please enter a Last Name">*</asp:RequiredFieldValidator>
</EditItemTemplate>
<InsertItemTemplate>
<asp:TextBoxID="TextBox2"Width="150"runat="server"Text='<%# Bind("LastName") %>'></asp:TextBox>
<asp:RequiredFieldValidatorID="RequiredFieldValidator4"runat="server"ControlToValidate="TextBox2"ErrorMessage="Please enter a Last Name">*</asp:RequiredFieldValidator>
</InsertItemTemplate>
<ItemTemplate>
<asp:LabelID="Label2"runat="server"Text='<%# Bind("LastName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateFieldHeaderText="PinCode"SortExpression="PinCode">
<EditItemTemplate>
<asp:TextBoxID="TextBox3"Width="100"runat="server"Text='<%# Bind("PinCode") %>'></asp:TextBox>
<asp:RequiredFieldValidatorID="RequiredFieldValidator5"runat="server"ControlToValidate="TextBox3"ErrorMessage="Please enter a PIN Code"></asp:RequiredFieldValidator>
</EditItemTemplate>
<InsertItemTemplate>
<asp:TextBoxID="TextBox3"Width="100"runat="server"Text='<%# Bind("PinCode") %>'></asp:TextBox>
<asp:RequiredFieldValidatorID="RequiredFieldValidator6"runat="server"ControlToValidate="TextBox3"ErrorMessage="Please enter a PIN Code">*</asp:RequiredFieldValidator>
</InsertItemTemplate>
<ItemTemplate>
<asp:LabelID="Label3"runat="server"Text='<%# Bind("PinCode") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateFieldHeaderText="Filter"SortExpression="Filter">
<EditItemTemplate>
<asp:DropDownListID="DropDownList3"runat="server"DataSourceID="sdsFilters"DataTextField="Filter"
DataValueField="ID"SelectedValue='<%# Bind("Filter") %>'AppendDataBoundItems="True">
<asp:ListItemValue="">Please make a selection</asp:ListItem>
<asp:ListItemValue=""></asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownListID="DropDownList2"runat="server"DataSourceID="sdsFilters"DataTextField="Filter"
DataValueField="ID"SelectedValue='<%# Bind("Filter") %>'AppendDataBoundItems="True">
<asp:ListItemValue="">Please make a selection</asp:ListItem>
<asp:ListItemValue=""></asp:ListItem>
</asp:DropDownList>
</InsertItemTemplate>
<ItemTemplate>
<asp:LabelID="Label4"runat="server"Text='<%# Bind("Filter") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateFieldHeaderText="CategoryId"SortExpression="CategoryId">
<EditItemTemplate>
<asp:DropDownListID="DropDownList1"runat="server"DataSourceID="sdsCategories"DataTextField="Name"
DataValueField="Id"SelectedValue='<%# Bind("CategoryId") %>'AppendDataBoundItems="True">
<asp:ListItemValue="">Please make a selection</asp:ListItem>
</asp:DropDownList>
<asp:RequiredFieldValidatorID="RequiredFieldValidator7"runat="server"ControlToValidate="DropDownList1"ErrorMessage="Please make a selection"></asp:RequiredFieldValidator>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownListID="DropDownList1"runat="server"DataSourceID="sdsCategories"DataTextField="Name"
DataValueField="Id"SelectedValue='<%# Bind("CategoryId") %>'AppendDataBoundItems="True">
<asp:ListItemValue="">Please make a selection</asp:ListItem>
</asp:DropDownList><asp:RequiredFieldValidatorID="RequiredFieldValidator8"runat="server"
ControlToValidate="DropDownList1"ErrorMessage="Please make a selection">*</asp:RequiredFieldValidator>
</InsertItemTemplate>
<ItemTemplate>
<asp:LabelID="Label5"runat="server"Text='<%# Bind("CategoryId") %>'></asp:Label>
</ItemTemplate>
<ControlStyleWidth="150px"/>
</asp:TemplateField>
<asp:CheckBoxFieldDataField="DeletePending"HeaderText="DeletePending"
SortExpression="DeletePending"/>
<asp:BoundFieldDataField="UpdateDateTime"HeaderText="UpdateDateTime"
SortExpression="UpdateDateTime"Visible="False"/>
<asp:CommandFieldShowEditButton="True"ShowInsertButton="True"ButtonType="Button"
CausesValidation="True"CancelText="Clear"/>
</Fields>
</asp:DetailsView>
<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:TelephonyAdminConnectionString1 %>"DeleteCommand="DELETE FROM [PinEntries] WHERE [Id] = @Id"InsertCommand="INSERT INTO [PinEntries] ([FirstName], [LastName], [PinCode], [Filter], [CategoryId], [DeletePending], [UpdateDateTime]) VALUES (@FirstName, @LastName, @PinCode, @Filter, @CategoryId, @DeletePending, @UpdateDateTime)"SelectCommand="SELECT [Id], [FirstName], [LastName], [PinCode], [Filter], [CategoryId], [DeletePending], [UpdateDateTime] FROM [PinEntries] WHERE ([Id] = @Id)"UpdateCommand="UPDATE [PinEntries] SET [FirstName] = @FirstName, [LastName] = @LastName, [PinCode] = @PinCode, [Filter] = @Filter, [CategoryId] = @CategoryId, [DeletePending] = @DeletePending, [UpdateDateTime] = @UpdateDateTime WHERE [Id] = @Id">
<SelectParameters>
<asp:QueryStringParameterName="Id"QueryStringField="Id"Type="Int32"/>
</SelectParameters>
<DeleteParameters>
<asp:ParameterName="Id"Type="Int32"/>
</DeleteParameters>
<UpdateParameters>
<asp:ParameterName="FirstName"Type="String"/>
<asp:ParameterName="LastName"Type="String"/>
<asp:ParameterName="PinCode"Type="Int32"/>
<asp:ParameterName="Filter"Type="String"/>
<asp:ParameterName="CategoryId"Type="Int32"/>
<asp:ParameterName="DeletePending"Type="Boolean"/>
<asp:ParameterName="UpdateDateTime"Type="DateTime"/>
<asp:ParameterName="Id"Type="Int32"/>
</UpdateParameters>
<InsertParameters>
<asp:ParameterName="FirstName"Type="String"/>
<asp:ParameterName="LastName"Type="String"/>
<asp:ParameterName="PinCode"Type="Int32"/>
<asp:ParameterName="Filter"Type="String"/>
<asp:ParameterName="CategoryId"Type="Int32"/>
<asp:ParameterName="DeletePending"Type="Boolean"/>
<asp:ParameterName="UpdateDateTime"Type="DateTime"/>
</InsertParameters>
</asp:SqlDataSource>
<asp:SqlDataSourceID="sdsCategories"runat="server"ConnectionString="<%$ ConnectionStrings:TelephonyAdminConnectionString1 %>"SelectCommand="SELECT [Id], [Name] FROM [Category] ORDER BY [SortOrder]">
</asp:SqlDataSource>
<asp:SqlDataSourceID="sdsFilters"runat="server"ConnectionString="<%$ ConnectionStrings:TelephonyAdminConnectionString1 %>"SelectCommand="SELECT [ID], [Filter] FROM [Filters] ORDER BY [Filter]">
</asp:SqlDataSource>
<br/>
<br/>
<br/>
<asp:ValidationSummaryID="ValidationSummary1"runat="server"HeaderText="Please correct the following errors before you press the Insert button"/>
</asp:Content>

And the code behind page looks like this:

Code:

 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
publicpartialclassManagement_AddNewPin : System.Web.UI.Page
{
protectedvoid Page_Load(object sender, EventArgs e)
 
{
if (Request.QueryString.Get("Id") != null)
{
DetailsView1.DefaultMode = DetailsViewMode.Edit;
}
}
protectedvoid DetailsView1_PreRender(object sender, EventArgs e)
{
if (this.DetailsView1.CurrentMode == DetailsViewMode.Edit)
{
TextBox box = DetailsView1.FindControl("TextBox1") asTextBox;
box.Focus();
}
}
 
protectedvoid DetailsView1_ItemInserted(object sender, DetailsViewInsertedEventArgs e)
{
EndEditing();
}
protectedvoid DetailsView1_ItemInserting(object sender, DetailsViewInsertEventArgs e)
{ 
e.Values["UpdateDateTime"] = DateTime.Now;
}
protectedvoid DetailsView1_ItemUpdated(object sender, DetailsViewUpdatedEventArgs e)
{ 
EndEditing();
}
protectedvoid DetailsView1_ItemUpdating(object sender, DetailsViewUpdateEventArgs e)
{ 
e.NewValues["UpdateDateTime"] = DateTime.Now;
}
privatevoid EndEditing()
{ 
Response.Redirect("PinEntries.aspx");
}
protectedvoid DetailsView1_PageIndexChanging(object sender, DetailsViewPageEventArgs e)
{
}
}

Thanks in anticipation

Andy

Imar · March 13th, 2009, 04:27 PM

Hi there,

Can you please add linebreaks to the code you posted and remove all formatting using the first button on the toolbar? The page is now two meters wide which makes the code impossible to read. Also, with formatted code all spaces are removed due to a bug in this forum.

Anyway, try setting up a handler for the Inserted event of the SqlDataSource and then look at:

e.Exception

Do you see the actual error there?

Imar