Try catch on sql database error(re-post)

Andy Woodward · March 17th, 2009, 10:11 AM

Hi Imar

Apologies for the last post being wide. Here is the post again. Hope you can help.

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>

The code behind page is:

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 for your time

Andy

Imar · March 17th, 2009, 02:22 PM

Quote:

Originally Posted by Imar

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

Hi Andy,

Thanks for reposting the code with code tags. However, it's still formatted and thus still causes all spaces to disappear. This makes it near impossible to look at the code.

Also, did you try setting the breakpoint?

Imar