Member Avatar for Nfurman
Nfurman 0 Junior Poster in Training

Hello.

Because my client's table has two rows getting int values as ids from other table to display the string values.

Table 1 Table 2

Item: 12 12 = 'Cat'

For displaying the values I have created view in DB

create view ClientsView as
select cl.id,cl.Name,cl.Familyname,cl.Position,cl.Email,cl.OfficePhone,cl.Mobile,cl.Website,cl.Comment,cl.[Date],cont.country,cl.Street,cl.City,clgr.[Group]
from Clients as cl INNER JOIN
dbo.Countries as cont on cont.id=cl.Country 
Inner Join
dbo.ClientsGroups as clgr on clgr.id=cl.[Group];
go

works fine for reading data. what's about the insertion and using view in GridView or DetailsView?

View won't work for insertion or deleting. Is there any way to make him do it?

  • 1 Contributor
  • 0 Replies
  • 88 Views

Dani AI

Generated

As discovered, a view that joins lookup tables is fine for SELECT but is not automatically updatable. SQL Server only allows simple, single-table views to accept direct INSERT/UPDATE/DELETE; when a view contains JOINs the engine cannot decide which base table should receive new data.

Two practical approaches. First, keep the GridView/DetailsView bound to the real Clients table for edits and use DropDownLists or lookup queries to present country/group names (post the selected ID on insert). Second, implement an INSTEAD OF trigger on the view that accepts rows from the view and distributes them into the base tables. Triggers must handle multi-row inserts, map display values to IDs (or accept IDs directly), and wrap work in a transaction. Example pattern:

CREATE TRIGGER trg_ClientsView_InsteadOfInsert
ON ClientsView
INSTEAD OF INSERT
AS
BEGIN
  SET NOCOUNT ON;

  INSERT INTO Clients (Name, Familyname, Position, Email, OfficePhone, Mobile, Website, Comment, [Date], Country, Street, City, [Group])
  SELECT i.Name, i.Familyname, i.Position, i.Email, i.OfficePhone, i.Mobile, i.Website, i.Comment, i.[Date],
         COALESCE(c.id, i.Country), i.Street, i.City, COALESCE(g.id, i.[Group])
  FROM inserted i
  LEFT JOIN Countries c ON c.Name = i.country
  LEFT JOIN ClientsGroups g ON g.Name = i.[Group];
END;

Notes and cautions: handle multiple rows from the inserted table, manage identity/OUTPUT if the UI needs created IDs, and verify permissions/ownership chaining so users can call the view without direct rights on base tables. For details on the rules and trigger behavior see Microsoft Docs: Views (Database Engine) and .

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.