Chapter 14

usmaak · March 3rd, 2013, 01:21 AM

Hi,

I'm trying to work my way through chapter 14, and have run into a problem that I haven't been able to solve. I'm getting the following when filling in the fields on PhotoAlbum.aspx and clicking the Insert button

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Picture_PhotoAlbum". The conflict occurred in database "H:\ASP.NET PROJECTS\BEGASPNET\SITE\APP_DATA\PLANETWROX.MDF", table "dbo.PhotoAlbum", column 'Id'.
The statement has been terminated.

I've compared everything to the Chapter 14 source, and I can't find anything wrong. But there has to be something wrong, or it wouldn't work.

Troubleshooting steps that I've taken so far.

1. I ran the Chapter 14 web site from the chapter downloads. It worked fine.
2. I copied everything from chapter 14 App_Data over to my website. I still get the error.
3. I copied over the entire App_Code folder from chapter 14 to my website. I still get the error.
4. I copied over ManagePhotoAlbum.aspx and ManagePhotoAlbum.aspx.cs from chapter 14. Still getting the error.

Stack trace here:

Code:

Stack Trace: 



[SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Picture_PhotoAlbum". The conflict occurred in database "H:\ASP.NET PROJECTS\BEGASPNET\SITE\APP_DATA\PLANETWROX.MDF", table "dbo.PhotoAlbum", column 'Id'.
The statement has been terminated.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +1753346
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5295154
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +242
   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +1682
   System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +59
   System.Data.SqlClient.SqlDataReader.get_MetaData() +90
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +365
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite) +1325
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +175
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +53
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +134
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +41
   System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10
   System.Data.Mapping.Update.Internal.DynamicUpdateCommand.Execute(UpdateTranslator translator, EntityConnection connection, Dictionary`2 identifierValues, List`1 generatedValues) +217
   System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter) +262

[UpdateException: An error occurred while updating the entries. See the inner exception for details.]
   System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter) +444
   System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache) +146
   System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options) +571
   System.Web.UI.WebControls.EntityDataSourceView.ExecuteInsert(IDictionary values) +566
   System.Web.UI.DataSourceView.Insert(IDictionary values, DataSourceViewOperationCallback callback) +81
   System.Web.UI.WebControls.ListView.HandleInsert(ListViewItem item, Boolean causesValidation) +374
   System.Web.UI.WebControls.ListView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +604
   System.Web.UI.WebControls.ListView.OnBubbleEvent(Object source, EventArgs e) +208
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
   System.Web.UI.WebControls.ListViewItem.OnBubbleEvent(Object source, EventArgs e) +88
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
   System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +114
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +159
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +35
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1724

Thanks

Imar · March 3rd, 2013, 04:16 AM

Hi there,

Sounds like an issue with the database. Are you sure you made both Id columns a primary key and an identity. and set up the relationship between the two tables on the correct columns?

If you've made changes to the database at a later stage, make sure you update the EDMX model. The easiest way to do this is to remove the photo album and picture entities from the model and then add them again by dragging them from the database onto the model design surface.

Hope this helps,

Imar

usmaak · March 3rd, 2013, 12:59 PM

Quote:

Originally Posted by Imar

Hi there,

Sounds like an issue with the database. Are you sure you made both Id columns a primary key and an identity. and set up the relationship between the two tables on the correct columns?

If you've made changes to the database at a later stage, make sure you update the EDMX model. The easiest way to do this is to remove the photo album and picture entities from the model and then add them again by dragging them from the database onto the model design surface.

Hope this helps,

Imar

Unless I'm not understanding something about how this works, I eliminated that possibility by replacing the App_Data and App_Code folders with the folders from Chapter 14. Replacing App_Data should get rid of my database, and use the one from the book. Replacing App_Code should get rid of my edmx, and use the one from the book. Even after doing this, I'm still getting the error.

Imar · March 3rd, 2013, 06:09 PM

In that case, can you post the code behind of newphotoalbum.aspx and the full code for managephotoalbum.aspx? There may be a mismatch in the album id you're passing and expecting.

Cheers,

Imar

usmaak · March 5th, 2013, 06:49 PM

Is there any way to post attachments? I don't see one. If not, I can put the files somewhere else, and link them.

Imar · March 6th, 2013, 10:16 AM

I don't think so. It's limited to authors and Wrox employees.

However, it's code so you can paste it as plain text (run it through Notepad first to get rid of the color coding) and the use the Code button (#) on the toolbar to mark your pasted text as code.

Cheers,

Imar

usmaak · March 7th, 2013, 09:46 PM

Here's the code for NewPhotoAlbum.aspx.cs

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using PlanetWroxModel;

public partial class _NewPhotoAlbum : BasePage 
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void EntityDataSource1_Inserted(object sender, EntityDataSourceChangedEventArgs e)
    {
      if (e.Entity != null)
      {
        PhotoAlbum myPhotoAlbum = (PhotoAlbum)e.Entity;
        Response.Redirect(string.Format("ManagePhotoAlbum.aspx?PhotoAlbum={0}", 
					 myPhotoAlbum.Id.ToString()));
      }
    }
}

Here's the code for ManagePhotoAlbum.aspx.cs

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using PlanetWroxModel;

public partial class _ManagePhotoAlbum : BasePage 
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void EntityDataSource1_Inserting(object sender, EntityDataSourceChangingEventArgs e)
    {
      int photoAlbumId = Convert.ToInt32(Request.QueryString.Get("PhotoAlbumId"));
      Picture myPicture = (Picture)e.Entity;
      myPicture.PhotoAlbumId = photoAlbumId;
    }
}

Here's the code for ManagePhotoAlbum.aspx

Code:

<%@ Page Title="Manage Photo Album" Language="C#" MasterPageFile="~/MasterPages/FrontEnd.master" AutoEventWireup="true" CodeFile="ManagePhotoAlbum.aspx.cs" Inherits="_ManagePhotoAlbum" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="cpMainContent" Runat="Server">
  <asp:ListView ID="ListView1" runat="server" DataKeyNames="Id" DataSourceID="EntityDataSource1" InsertItemPosition="LastItem">
    <InsertItemTemplate>
      <li style="">
        Description:
        <asp:TextBox ID="DescriptionTextBox" runat="server" Text='<%# Bind("Description") %>' />
        <br />
        ToolTip:
        <asp:TextBox ID="ToolTipTextBox" runat="server" Text='<%# Bind("ToolTip") %>' />
        <br />
        ImageUrl:
        <asp:TextBox ID="ImageUrlTextBox" runat="server" Text='<%# Bind("ImageUrl") %>' />
        <br />
        <asp:Button ID="InsertButton" runat="server" CommandName="Insert" Text="Insert" />
        <asp:Button ID="CancelButton" runat="server" CommandName="Cancel" Text="Clear" />
      </li>
    </InsertItemTemplate>
    <ItemTemplate>
      <li style="">
        Description:
        <asp:Label ID="DescriptionLabel" runat="server" Text='<%# Eval("Description") %>' />
        <br />
        ToolTip:
        <asp:Label ID="ToolTipLabel" runat="server" Text='<%# Eval("ToolTip") %>' />
        <br />
        ImageUrl:
        <asp:Label ID="ImageUrlLabel" runat="server" Text='<%# Eval("ImageUrl") %>' />
        <br />
        <asp:Button ID="DeleteButton" runat="server" CommandName="Delete" Text="Delete" />
      </li>
    </ItemTemplate>
    <LayoutTemplate>
      <ul class="ItemContainer">
        <li runat="server" id="itemPlaceholder" />
      </ul>
    </LayoutTemplate>
  </asp:ListView>
  <asp:EntityDataSource ID="EntityDataSource1" runat="server" ConnectionString="name=PlanetWroxEntities" DefaultContainerName="PlanetWroxEntities" EnableDelete="True" EnableFlattening="False" EnableInsert="True" EntitySetName="Pictures" Where="it.PhotoAlbum.Id = @photoAlbumId">
    <WhereParameters>
      <asp:QueryStringParameter Name="PhotoAlbumId" QueryStringField="PhotoAlbumId" Type="Int32" />
    </WhereParameters>
  </asp:EntityDataSource>
</asp:Content>

Thanks!

Imar · March 8th, 2013, 05:48 AM

Ah there we have it. Here's how you're passing the ID (without Id in the name):

Code:

Response.Redirect(string.Format("ManagePhotoAlbum.aspx?PhotoAlbum={0}", 
                     myPhotoAlbum.Id.ToString()));

and here's how you're reading it (with Id):

Code:

int photoAlbumId = Convert.ToInt32(Request.QueryString.Get("PhotoAlbumId"));

Since the two don't match, the code in the second block tries to a insert a Picture for a PhotoAlbum with an Id of zero which doesn't exist.

Cheers,

Imar

usmaak · March 8th, 2013, 08:21 PM

Thank you for your help. I've found and fixed the issue.

And this is a great book. I've really enjoyed learning from it.

Imar · March 9th, 2013, 09:31 AM

>> And this is a great book. I've really enjoyed learning from it.

Great. Spread the word, Spread the word! ;-)

Cheers,

Imar