Dropdowns not updating correctly

So I've got this Issue Tracking app that I'm doing in dynamic data. I've got a Projects table and an Issues table. When I edit Issues I get a drop down of projects so I can assign an issue to a project and that's all great.

My problem is that when I add a new project via the Projects page and then go to the Issues page the new project does not appear in either the filter or edit drop downs. How can I get these guys to update? I'm assuming there's some caching going on for performance but this is a real problem here. The only way I've found to fix the problem is to stop both the debugger and cassini and restart both.

Thanks.

Re: Dropdowns not updating correctly

Without seeing your code, it may sound like caching as you said, or you may be storing the data for the projects in session. You may need to just recall the data to repopulate the list (which isn't a big hit). Or if you want to keep it in session your going to need some type of flag to say go and reload the project list because there is a new one in there.

Feel free to publish your code and maybe we can help futher.

Re: Dropdowns not updating correctly

Well, I'm using the Dynamic Data Extensions so there isn't really any code that's specific to the project. I'm not sure whether you picked up on that or not.

If you did then how within the extensions do I repopulate the list?

Re: Dropdowns not updating correctly

Hi stevekain,

There is an issue with how data context caching is done in the Preview version of Dynamic Data that might be the cause of the behavior that you are getting. However, I can't seem to be able to repro your scenario on my test box (i'm using the Products and Categories tables from Northwind though, but the parent-child pattern should be the same). Are you using the default Dynamic Data project without any customizations? Could you post your DB schema that illustrates the problem?

Re: Dropdowns not updating correctly

I am using the default dynamic data project. I've added one or two renderhints but other than that it is standard. I do not have any table-specific pages or controls in action here.

This is the script for the db that is having the problem:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Projects]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Projects](
	[ProjectID] [int] IDENTITY(1,1) NOT NULL,
	[ProjectName] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Projects] PRIMARY KEY CLUSTERED
(
	[ProjectID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Statuses]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Statuses](
	[StatusID] [int] IDENTITY(1,1) NOT NULL,
	[StatusName] [nvarchar](50) NOT NULL,
	[StatusLevel] [int] NOT NULL,
 CONSTRAINT [PK_Statuses] PRIMARY KEY CLUSTERED
(
	[StatusID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Priorities]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Priorities](
	[PriorityID] [int] IDENTITY(1,1) NOT NULL,
	[PriorityName] [nvarchar](50) NOT NULL,
	[PriorityLevel] [int] NOT NULL,
 CONSTRAINT [PK_Priorities] PRIMARY KEY CLUSTERED
(
	[PriorityID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SecurityLevels]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SecurityLevels](
	[SecurityLevelID] [int] IDENTITY(1,1) NOT NULL,
	[SecurityLevelName] [nvarchar](50) NOT NULL,
	[LevelValue] [int] NOT NULL,
 CONSTRAINT [PK_SecurityLevels] PRIMARY KEY CLUSTERED
(
	[SecurityLevelID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Notes]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Notes](
	[NoteID] [int] IDENTITY(1,1) NOT NULL,
	[IssueID] [int] NOT NULL,
	[CreatedByID] [int] NOT NULL,
	[NoteText] [nvarchar](4000) NOT NULL,
 CONSTRAINT [PK_Notes] PRIMARY KEY CLUSTERED
(
	[NoteID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Issues]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Issues](
	[IssueID] [int] IDENTITY(1,1) NOT NULL,
	[ProjectID] [int] NOT NULL,
	[PriorityID] [int] NOT NULL,
	[StatusID] [int] NOT NULL,
	[CreatedByID] [int] NOT NULL,
	[AssignedToID] [int] NULL,
	[ResolvedByID] [int] NULL,
	[IssueTitle] [nvarchar](50) NOT NULL,
	[IssueDetails] [nvarchar](4000) NULL,
 CONSTRAINT [PK_Issues] PRIMARY KEY CLUSTERED
(
	[IssueID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Milestones]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Milestones](
	[MilestoneID] [int] IDENTITY(1,1) NOT NULL,
	[ProjectID] [int] NOT NULL,
	[MilestoneName] [nvarchar](500) NOT NULL,
	[DueDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Milestones] PRIMARY KEY CLUSTERED
(
	[MilestoneID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Users]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Users](
	[UserID] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [nvarchar](50) NOT NULL,
	[Password] [nvarchar](50) NOT NULL,
	[SecurityLevelID] [int] NOT NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
	[UserID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Notes_Issues]') AND parent_object_id = OBJECT_ID(N'[dbo].[Notes]'))
ALTER TABLE [dbo].[Notes]  WITH CHECK ADD  CONSTRAINT [FK_Notes_Issues] FOREIGN KEY([IssueID])
REFERENCES [dbo].[Issues] ([IssueID])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Notes_Users]') AND parent_object_id = OBJECT_ID(N'[dbo].[Notes]'))
ALTER TABLE [dbo].[Notes]  WITH CHECK ADD  CONSTRAINT [FK_Notes_Users] FOREIGN KEY([CreatedByID])
REFERENCES [dbo].[Users] ([UserID])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Issues_Priorities]') AND parent_object_id = OBJECT_ID(N'[dbo].[Issues]'))
ALTER TABLE [dbo].[Issues]  WITH CHECK ADD  CONSTRAINT [FK_Issues_Priorities] FOREIGN KEY([PriorityID])
REFERENCES [dbo].[Priorities] ([PriorityID])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Issues_Projects]') AND parent_object_id = OBJECT_ID(N'[dbo].[Issues]'))
ALTER TABLE [dbo].[Issues]  WITH CHECK ADD  CONSTRAINT [FK_Issues_Projects] FOREIGN KEY([ProjectID])
REFERENCES [dbo].[Projects] ([ProjectID])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Issues_Statuses]') AND parent_object_id = OBJECT_ID(N'[dbo].[Issues]'))
ALTER TABLE [dbo].[Issues]  WITH CHECK ADD  CONSTRAINT [FK_Issues_Statuses] FOREIGN KEY([StatusID])
REFERENCES [dbo].[Statuses] ([StatusID])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Table5_AssignedUsers]') AND parent_object_id = OBJECT_ID(N'[dbo].[Issues]'))
ALTER TABLE [dbo].[Issues]  WITH CHECK ADD  CONSTRAINT [FK_Table5_AssignedUsers] FOREIGN KEY([AssignedToID])
REFERENCES [dbo].[Users] ([UserID])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Table5_CreateUsers]') AND parent_object_id = OBJECT_ID(N'[dbo].[Issues]'))
ALTER TABLE [dbo].[Issues]  WITH CHECK ADD  CONSTRAINT [FK_Table5_CreateUsers] FOREIGN KEY([CreatedByID])
REFERENCES [dbo].[Users] ([UserID])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Table5_ResolvedUsers]') AND parent_object_id = OBJECT_ID(N'[dbo].[Issues]'))
ALTER TABLE [dbo].[Issues]  WITH CHECK ADD  CONSTRAINT [FK_Table5_ResolvedUsers] FOREIGN KEY([ResolvedByID])
REFERENCES [dbo].[Users] ([UserID])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Milestones_Projects]') AND parent_object_id = OBJECT_ID(N'[dbo].[Milestones]'))
ALTER TABLE [dbo].[Milestones]  WITH CHECK ADD  CONSTRAINT [FK_Milestones_Projects] FOREIGN KEY([ProjectID])
REFERENCES [dbo].[Projects] ([ProjectID])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Users_SecurityLevels]') AND parent_object_id = OBJECT_ID(N'[dbo].[Users]'))
ALTER TABLE [dbo].[Users]  WITH CHECK ADD  CONSTRAINT [FK_Users_SecurityLevels] FOREIGN KEY([SecurityLevelID])
REFERENCES [dbo].[SecurityLevels] ([SecurityLevelID])
ON UPDATE CASCADE
ON DELETE CASCADE

Re: Dropdowns not updating correctly

I am having a very similar issue. When an Update/Insert/Delete is performed on an associated table, the corresponding dropdown is not updated in the next view. I'm thinking that we're missing an update, load, or databind call somewhere. I am also using the default templates with only one view using minor customization.

Thanks,

Re: Dropdowns not updating correctly

The problem you both are seeing is indeed a bug in Dynamic Data caching. I'll try to see if we can provide some sort of workaround but I'm not sure it's possible from outside the assembly.

Re: Dropdowns not updating correctly

same problem here too

Re: Dropdowns not updating correctly

Wow. That's a real disappointment. I sure hope there's a workaround because this bug is a true dealbreaker for me. And I should think for anyone considering actually using the preview . . .

Re: Dropdowns not updating correctly

stevekain

Wow. That's a real disappointment. I sure hope there's a workaround because this bug is a true dealbreaker for me. And I should think for anyone considering actually using the preview . . .

I agree, this is dissapointing and it has brought our testing to a stand still. And if there's no work around, we are going to have to can it and take another approach. I guess if your application does not require immediate updating, this will be fine, but our application is for cleansing and reconfiguring data, and the records need to be updated across the board at the time they are changed, not when the application feels like re-caching the data. I would have thought this would be on the testing list BEFORE the preview.

Re: Dropdowns not updating correctly

Honestly I can't imagine an application that wouldn't need immediate updating. I suppose if you had a database with no relations, but what real world app has that?

I too have a hard time understanding how this got out the door as part of the preview. It took me 20 minutes of messing around with this thing to find the problem.

Re: Dropdowns not updating correctly

Sometimes the most obvious of things ellude us the most. Fortunately, I think we have been able to come up with a working solution to the problem. You will need to edit make changes to 2 files: FilterUserControl.ascx and ForeignKey_Edit.ascx. This solution uses C# (sorry, no VB for now) and should work the same for both inline and code-behind versions.

For ForeignKey_Edit.ascx

In the Page_Load method, replace the following line of code

DropDownList1.DataSource = parentTable.Query;

with this

DropDownList1.DataSource = parentTable.DataContextProperty.GetValue(Activator.CreateInstance(DynamicDatabase.TheDatabase.CreateDataContext().GetType()), null);

For FilterUserControl.ascx

In the Page_Init method, replace the following line of code

DropDownList1.DataSource = DataSource;

with this

var foreignKeyColumn = DynamicDatabase.TheDatabase.GetMetaTable(this.TableName).FindColumn(this.DataField) as DynamicMetaForeignKeyMember;
if (foreignKeyColumn != null) {
    // only kick in if we are dealing with a foreign key column
    DropDownList1.DataSource = foreignKeyColumn.ParentMetaTable.DataContextProperty.GetValue(Activator.CreateInstance(DynamicDatabase.TheDatabase.CreateDataContext().GetType()), null);
}
else {
    DropDownList1.DataSource = DataSource;
}

The workaround is a bit ugly but it should work. Let us know if you experience more issues. Once we are able to verify that this fix works we will post updated versions of the relevant files.

Re: Dropdowns not updating correctly

Excellent Marcin! That did it. Both the filter and edit dropdowns are now updating as expected.

I'm really pretty impressed by the Dynamic Data stuff. It seems like for years different frameworks and controls promised this sort of out of the box functionality but none managed to actually provide it.

From what I've seen of Dynamic Data so far it really delivers where previous attempts have failed. I think it really has the potential to get me 40% of my 80% (in the 80-20 rule) in about 10% of the time. Calculate that out and it's pretty great. Take my word for it.

Thanks again.

Re: Dropdowns not updating correctly

Yes, awesome, thank you for the fix. One note though, in the FIlterUserControl.ascx, there isn't a Page_Load function, you are using the Page_Init function instead. I really love the out of the box functionality of dynamic data. It makes the develoment time of data sites so much faster. Even with the customizations I've added, it still cut the development time by at least 75%. Thank you guys so much for this set of tools.

Re: Dropdowns not updating correctly

Thanks Ron,

I've updated the fix instructions. I am glad you are finiding Dynamic Data useful.

Re: Dropdowns not updating correctly

I have also placed a zip file with the modified files that you can simply unzip into your Dynamic Data project on my blog.