Databases
StackOverflow2013
Postsdbo
POSingle web page, LinqDataSource, GridView to edit multiple data tables
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSingle web page, LinqDataSource, GridView to edit multiple data tables
    primarykey
    data
    text
    <p>Problem is - new application, customer wants Linq, .NET 4.0, many data tables that are "categories" and "types" - simple data tables that are usually two fields: ID and Name (or description)</p> <p>Instead of generating a web form to edit each of these data tables - wouldnt it be nice to only need one web page to edit all of them and you simply select the data table you want to edit. (BTW - where are the "pattern junkies". Isnt this a pattern? Where is the pattern .NET code template for this?)</p> <p>I am playing with the Northwinds database. I added a table called DropDownConfiguration:</p> <pre><code>USE [Northwind] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DropDownConfiguration]( [DropDownConfigurationID] [bigint] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](50) NOT NULL, [TableName] [nvarchar](50) NOT NULL, [PrimaryKeyName] [nvarchar](50) NOT NULL, CONSTRAINT [PK_DropDownConfiguration] PRIMARY KEY CLUSTERED ( [DropDownConfigurationID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO </code></pre> <p>Then I populated this data table with the following data:</p> <pre><code>DropDownConfigurationID Name TableName PrimaryKeyName 1 Categories Categories CategoryID 2 Regions Regions RegionID </code></pre> <p>asp.net web page looks like:</p> <pre><code>&lt;%@ Page Language="VB" AutoEventWireup="false" CodeFile="EditMultiTable.aspx.vb" Inherits="EditMultiTable" %&gt; &lt;!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"&gt; &lt;html xmlns="http://www.w3.org/1999/xhtml"&gt; &lt;head runat="server"&gt; &lt;title&gt;&lt;/title&gt; &lt;/head&gt; &lt;body&gt; &lt;form id="form1" runat="server"&gt; &lt;div&gt; &lt;asp:LinqDataSource ID="LinqDataSourceMultiTable" runat="server" ContextTypeName="DataClassesDataContext" EnableInsert="True" EnableUpdate="True" EntityTypeName="" TableName="Categories"&gt; &lt;/asp:LinqDataSource&gt; &lt;asp:DropDownList ID="ddlDropDownConfigurations" runat="server" AutoPostBack="True"&gt; &lt;/asp:DropDownList&gt; &lt;asp:GridView ID="GridViewMultiTable" runat="server" DataKeyNames="CategoryID" DataSourceID="LinqDataSourceMultiTable"&gt; &lt;Columns&gt; &lt;asp:CommandField ShowEditButton="True" /&gt; &lt;/Columns&gt; &lt;/asp:GridView&gt; &lt;/div&gt; &lt;/form&gt; &lt;/body&gt; &lt;/html&gt; </code></pre> <p>Code behind looks like:</p> <pre><code>Option Explicit On Option Strict On Partial Class EditMultiTable Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim myDropDownConfigurationList As List(Of DropDownConfiguration) If Not Page.IsPostBack Then ' Tell the drop down what data tables we want to be able to edit myDropDownConfigurationList = GetDropDownConfigurations() ddlDropDownConfigurations.DataSource = myDropDownConfigurationList ddlDropDownConfigurations.DataTextField = "Name" ddlDropDownConfigurations.DataValueField = "DropDownConfigurationID" ddlDropDownConfigurations.DataBind() End If End Sub Protected Sub ddlDropDownConfigurations_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlDropDownConfigurations.SelectedIndexChanged Dim myDropDownConfiguration As DropDownConfiguration Dim myDropDownConfigurationList As List(Of DropDownConfiguration) If ddlDropDownConfigurations.SelectedIndex &gt; -1 Then ' clear out the old data bindings between the LinqDataSource and the GridView GridViewMultiTable.DataSourceID = Nothing GridViewMultiTable.DataSource = Nothing GridViewMultiTable.DataKeyNames = Nothing GridViewMultiTable.DataMember = Nothing GridViewMultiTable.AutoGenerateColumns = False GridViewMultiTable.AutoGenerateEditButton = False GridViewMultiTable.DataBind() GridViewMultiTable.Columns.Clear() ' Set up the LinqDataSource for the new table myDropDownConfigurationList = GetDropDownConfigurations() myDropDownConfiguration = (From ddc In myDropDownConfigurationList Where ddc.DropDownConfigurationID = Long.Parse(ddlDropDownConfigurations.SelectedValue) Select ddc).FirstOrDefault() LinqDataSourceMultiTable.TableName = myDropDownConfiguration.TableName LinqDataSourceMultiTable.EntityTypeName = String.Empty LinqDataSourceMultiTable.EnableInsert = True LinqDataSourceMultiTable.EnableUpdate = True LinqDataSourceMultiTable.DataBind() ' bind the GridView to the LinqDataSource with the new data table GridViewMultiTable.DataSourceID = "LinqDataSourceMultiTable" GridViewMultiTable.DataKeyNames = New String() {myDropDownConfiguration.PrimaryKeyName} GridViewMultiTable.AutoGenerateColumns = True GridViewMultiTable.AutoGenerateEditButton = True GridViewMultiTable.DataBind() End If End Sub ' Get my data table that lists the data tables that I want to configure Function GetDropDownConfigurations() As List(Of DropDownConfiguration) Dim myDropDownConfigurationList As List(Of DropDownConfiguration) Using myDataClassesDataContext As New DataClassesDataContext myDropDownConfigurationList = (From ddc In myDataClassesDataContext.DropDownConfigurations Select ddc).ToList() End Using Return myDropDownConfigurationList End Function End Class </code></pre> <p>my problem is - either the LinqDataSource or the GridView is not being cleared. Its kind of using half of one data table and half of another. Which is why in my code behind - I have tried clearing the LinqDataSource and GridView in every way imaginable. If I run the program, select "Regions" in the drop down list box and then try to edit the first row of "regions" I got the following error:</p> <p>DataBinding: 'Category' does not contain a property with the name 'RegionID'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. </p> <p>Exception Details: System.Web.HttpException: DataBinding: 'Category' does not contain a property with the name 'RegionID'.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USBubba
    1. USBubba
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COI hate this design :/ Getting a database table involved and all that just to make two grids instead of one? This won't pay off until you have 20 grids getting populated.
      singulars
      1. POSingle web page, LinqDataSource, GridView to edit multiple data tables
      1. USChris McCall
    2. COI expect to have at least 20 data tables that need editing
      singulars
      1. POSingle web page, LinqDataSource, GridView to edit multiple data tables
      1. USBubba
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload