UPDATE / DELETE行中取得多个表的SQL Server / Visual Studio中的视图 [英] UPDATE/DELETE row in a VIEW made of multiple tables SQL Server/Visual Studio

问题描述

问：我做了一个正在由具有主键的多个表的视图。我使用ASP.NET中的GridView更新和删除数据库中的行。该行是由两个不同的表。我怎么更新和使用ASP.NET GridView的删除吗？我有一个很难搞清楚，因为每次尝试我做，SQL Server的说：因为它是访问多个表无法更新视图。

原来，你不能自动生成UPDATE和DELETE语句在GridView的选择数据源程序，当一个视图是由多个表的。

请帮我家伙：（

  CREATE VIEW [DBO]。[viewProcedureWithPrice]
    如
    SELECT DBO。[程序] .ProcedureID，DBO。[程序] .name和DBO。[程序]型号及DBO。[程序] .STATUS，dbo.PRICE_HISTORY.Price，
                             dbo.PRICE_HISTORY.EffectivityDate
    DBO的。[程序] INNER JOIN
                             dbo.PRICE_HISTORY ON DBO。[操作步骤] .ProcedureID = dbo.PRICE_HISTORY.ProcedureID    走CREATE TABLE [DBO]。[程序]（
    [ProcedureID] [INT] IDENTITY（1,1）NOT NULL，
    [名] [VARCHAR（100）NULL，
    [类型] [VARCHAR（100）NULL，
    [状态] [VARCHAR（20）NULL，
 约束[PK_PROCEDURE] PRIMARY KEY CLUSTERED
（
    [ProcedureID] ASC
）和（PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON）ON [PRIMARY]
）ON [PRIMARY]走CREATE TABLE [DBO]。[PRICE_HISTORY]（
    [HistoryID] [INT] IDENTITY（1,1）NOT NULL，
    [ProcedureID] [INT] NULL，
    [参考价格] [十进制（10,2）NULL，
    [EffectivityDate] [日期] NULL，
 约束[PK_PRICE_HISTORY] PRIMARY KEY CLUSTERED
（
    [HistoryID] ASC
）和（PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON）ON [PRIMARY]
）ON [PRIMARY]走ALTER TABLE [DBO]。[PRICE_HISTORY] WITH CHECK约束ADD [FK_PRICE_HISTORY_PROCEDURE]外键（[ProcedureID]）
参考文献[DBO]。[程序]（[ProcedureID]）
走ALTER TABLE [DBO]。[PRICE_HISTORY] CHECK约束[FK_PRICE_HISTORY_PROCEDURE]
走
 

解决方案

如果我明白的问题，我所做的就是添加一行在我自己的删除按钮使用不同的命令名称，说MyDelete（也许添加相应的命令参数来确定行或使用DataKeys）。抓住它在GridView_RowCommand事件，并执行删除手动那里。

也许有更好的方法，但这种工作的。

我不得不看code进入更多的细节。

Q: I made a view that is composed of multiple tables with primary keys. I'm using GridView in ASP.NET to UPDATE and DELETE a row in the database. That row is composed of two different tables. How am I going to UPDATE and DELETE it using the GridView in ASP.NET? I'm having a hard time figuring out because every attempt I make, SQL Server says "Cannot update view because it is accessing multiple tables".

It turns out, you cannot auto-generate UPDATE and DELETE statements in the "Choose Data Sourc" of a GridView when a VIEW is composed of multiple tables.

Please help me guys :(

    CREATE VIEW [dbo].[viewProcedureWithPrice]
    AS
    SELECT        dbo.[PROCEDURE].ProcedureID, dbo.[PROCEDURE].Name, dbo.[PROCEDURE].Type, dbo.[PROCEDURE].Status, dbo.PRICE_HISTORY.Price, 
                             dbo.PRICE_HISTORY.EffectivityDate
    FROM            dbo.[PROCEDURE] INNER JOIN
                             dbo.PRICE_HISTORY ON dbo.[PROCEDURE].ProcedureID = dbo.PRICE_HISTORY.ProcedureID

    GO

CREATE TABLE [dbo].[PROCEDURE](
    [ProcedureID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](100) NULL,
    [Type] [varchar](100) NULL,
    [Status] [varchar](20) NULL,
 CONSTRAINT [PK_PROCEDURE] PRIMARY KEY CLUSTERED 
(
    [ProcedureID] 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

CREATE TABLE [dbo].[PRICE_HISTORY](
    [HistoryID] [int] IDENTITY(1,1) NOT NULL,
    [ProcedureID] [int] NULL,
    [Price] [decimal](10, 2) NULL,
    [EffectivityDate] [date] NULL,
 CONSTRAINT [PK_PRICE_HISTORY] PRIMARY KEY CLUSTERED 
(
    [HistoryID] 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

ALTER TABLE [dbo].[PRICE_HISTORY]  WITH CHECK ADD  CONSTRAINT [FK_PRICE_HISTORY_PROCEDURE] FOREIGN KEY([ProcedureID])
REFERENCES [dbo].[PROCEDURE] ([ProcedureID])
GO

ALTER TABLE [dbo].[PRICE_HISTORY] CHECK CONSTRAINT [FK_PRICE_HISTORY_PROCEDURE]
GO

解决方案

If I understand the question, what I have done is add my own delete button in the row with a different command name, say "MyDelete" (maybe add the appropriate command argument to identify the row or use DataKeys). Catch it in the GridView_RowCommand event and perform your deletes manually there.

Maybe there's a better way, but this works.

I'd have to see the code to go into greater detail.

这篇关于UPDATE / DELETE行中取得多个表的SQL Server / Visual Studio中的视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！