Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> .NET Development >> .NET Framework Data Access and Storage

SQL Data Adapter Configuration Wizard not making UPDATE command

I am trying to make a SQLDataAdapter connecting to a VIEW (not a table), which was constructed from several tables. All tables related to this VIEW have primary key columns which were linked together through relationship in diagram.

In Visual Studio, the Data Adapter Configuration Wizard went through fine making SELECT and INSERT command but failed to make UPDATE and DELETE command. I chose 'Create new stored procedure' option in the middle of wizard. The message was "Could not determine which columns uniquely identify the rows for 'MyView'." But actually there was a column uniquely identify the rows.

At first, it seemed understandable because a VIEW is a simply dynamically binding SQL SELECT statement so that it does not include a primary key like a TABLE. But considering that this VIEW is updatable from direct editing environment like MS Access ADP page or SQL Enterprise Manager, it SHOULD be able to make UPDATE command too for a VIEW. And 'MyView' satisfies all conditions for being an 'Updatable View' according to SQL online manual.

I really would like to know that if this is just a design feature or I am doing something wrong. I think it is important point because if a VIEW can be treated exactly like a TABLE (Microsoft said that a VIEW is kind of a 'virtual TABLE') it can make big difference in coding.

What I am thinking is like this. You have lots of tables in SQL server. Then make a VIEW which links all the complex tables. Then in the program side, you can code a connection to this VIEW not to every TABLES. Later, when you need to change some schema of TABLES, you only need to change the VIEW and/or the stored procedure without touching already deployed application.

But if 'VS Data Adapter Configuration Wizard' cannot make UPDATE command for VIEWS then I can't allow users to edit the DataSet and UPDATE the database. Of course I know I can bind SQL commands manually but when there are so many complex named columns in many tables it is a pain in the neck.

Any better idea or comment?
Or can anybody suggest a "BEST PRACTICE" when you are designing a database and writing a client application at the same time?
Here I copy the SQL query of 'MyView' captured from QueryAnalyzer.
--
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER VIEW dbo.PartsFab
WITH SCHEMABINDING, VIEW_METADATA
AS
SELECT dbo.Parts.[Part Number], dbo.PartsFabInfo.Active, dbo.Parts.Type, dbo.Parts.Description AS [Part Name], dbo.PartsFabInfo.Material,
dbo.PartsImage.[Drawing File], dbo.PartsRemark.Remark, dbo.PartsFabInstruction.Instruction, dbo.PartsFabInfo.[Process 1],
dbo.PartsFabInfo.[Process 2], dbo.PartsFabInfo.[Process 3], dbo.PartsFabInfo.[Process 4], dbo.PartsFabInfo.[Process 5], dbo.PartsFabInfo.[Process 6],
dbo.PartsFabInfo.[Process 7], dbo.PartsFabInfo.[Process 8], dbo.PartsFabInfo.[Process 9], dbo.PartsFabInfo.[Process 10], dbo.Inventory.[Qty OnHand],
dbo.BlankSize.L1, dbo.BlankSize.W1, dbo.BlankSize.N1, dbo.Shiplist1.Quantity AS SR, dbo.Shiplist2.Quantity AS RB,
dbo.Shiplist3.Quantity AS EL, dbo.Shiplist4.Quantity AS MV, dbo.Shiplist5.Quantity AS RN
FROM dbo.Parts LEFT OUTER JOIN
dbo.PartsFabInfo ON dbo.Parts.[Part Number] = dbo.PartsFabInfo.[Part Number] LEFT OUTER JOIN
dbo.PartsFabInstruction ON dbo.Parts.[Part Number] = dbo.PartsFabInstruction.[Part Number] LEFT OUTER JOIN
dbo.PartsImage ON dbo.Parts.[Part Number] = dbo.PartsImage.[Part Number] LEFT OUTER JOIN
dbo.PartsRemark ON dbo.Parts.[Part Number] = dbo.PartsRemark.[Part Number] LEFT OUTER JOIN
dbo.Inventory ON dbo.Parts.[Part Number] = dbo.Inventory.[Part Number] LEFT OUTER JOIN
dbo.BlankSize ON dbo.Parts.[Part Number] = dbo.BlankSize.[Part Number] LEFT OUTER JOIN
dbo.Shiplist3 ON dbo.Parts.[Part Number] = dbo.Shiplist3.[Part Number] LEFT OUTER JOIN
dbo.Shiplist4 ON dbo.Parts.[Part Number] = dbo.Shiplist4.[Part Number] LEFT OUTER JOIN
dbo.Shiplist2 ON dbo.Parts.[Part Number] = dbo.Shiplist2.[Part Number] LEFT OUTER JOIN
dbo.Shiplist5 ON dbo.Parts.[Part Number] = dbo.Shiplist5.[Part Number] LEFT OUTER JOIN
dbo.Shiplist1 ON dbo.Parts.[Part Number] = dbo.Shiplist1.[Part Number]
WHERE (dbo.Parts.[Part Number] NOT LIKE '#%')
WITH CHECK OPTION
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
[4436 byte] By [fudata] at [2007-12-16]
«« SQL 2005 Cluster
»» Implementing an IDictionary class
# 1
fudata,

You have a valid point about being able to generate Update commands for Views, unfortunately it is very difficult from most 'Views' to determine which fields are keys and what table they belong to...

Your best bet in these situations is to bite the bullet and create your own Update and Delete commands and use the command object to execute them..

DMan1 at 2007-9-9 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
# 2
Thanks DMan1,

I guessed same. Then I gotto go to bite bullet.

Happy coding.

fudata at 2007-9-9 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
# 3

I setup UPDATE command manually typing all parameters but still VS returned SQL exception of "View or function 'MyView' is not updatable because the modification affects multiple base tables". But when I change only one column it cannot affect multiple tables. Can it?

This message is from SQL server not from Visual Studio. When I executed the stored procedure for UPDATE command from SQL Query Analyzer I got exactly same message.

So SQL Server seems not like to UPDATE multiple table based VIEW. But then again how come Enterprise Manager or MS Access can UPDATE this multiple table based view?

I feel really frustrating now. Maybe I have to try an 'Indexed View' or 'Partitioned View' though I am not quite sure what they mean.

Can Anybody help me?

Following is the stored procedure for updating multi-table view (which is not working).

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


ALTER PROCEDURE dbo.vs_PartsFabUpdateCommand
(
@Part_Number nvarchar(11),
@Active bit,
@Type nvarchar(16),
@Description nvarchar(255),
@Material nvarchar(255),
@DwgFile nvarchar(255),
@Remark nvarchar(255),
@Instruction nvarchar(255),
@Process1 nvarchar(32),
@Process2 nvarchar(32),
@Process3 nvarchar(32),
@Process4 nvarchar(32),
@Process5 nvarchar(32),
@Process6 nvarchar(32),
@Process7 nvarchar(32),
@Process8 nvarchar(32),
@Process9 nvarchar(32),
@Process10 nvarchar(32),
@QtyOnHand int,
@DateLastChecked datetime,
@QtyCommitted int,
@QtyOnOrder int
)
AS
SET NOCOUNT OFF;
UPDATE PartsFab
SET Active=@Active,
Type=@Type,
[Part Name]=@Description,
Material=@Material,
[Drawing File]=@DwgFile,
Remark=@Remark,
Instruction=@Instruction,
[Process 1]=@Process1,
[Process 2]=@Process2,
[Process 3]=@Process3,
[Process 4]=@Process4,
[Process 5]=@Process5,
[Process 6]=@Process6,
[Process 7]=@Process7,
[Process 8]=@Process8,
[Process 9]=@Process9,
[Process 10]=@Process10,
[Qty OnHand]=@QtyOnHand,
[Date Last Checked]=@DateLastChecked,
[Qty Committed]=@QtyCommitted,
[Qty OnOrder]=@QtyOnOrder

WHERE ([Part Number] = @Part_Number);

SELECT * FROM PartsFab

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

fudata at 2007-9-9 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
# 4
>But when I change only one column it cannot affect multiple tables. Can it?
Yes it is possible that one column can effect multiple tables...
For EXample:

TableCars
CarId = 1 MakeId = 1 ColorID = 1
CarID = 2 Make = 2 Color = 2

TableColors
Id = 1 Color = Red
id = 2 Color = Blue

TableMakes
id = 1 Make = Ford
id = 2 Make = Dodge

ViewCars
CarId Make Color
1 Ford Red
2 Dodge Blue

If there are constraints in the database and i go to my view and change the color of CarId 1 to Green I must update TableColors before I can update tablecars

just as an example...make sense?

DMan1 at 2007-9-9 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
# 5
Thanks again, DMan1,

Your example makes sense. But in your example, you changed color of car1 to green, which was NOT in the color table. But in 'MyView' I change the color of car1 to only Blue, and it does not affect multiple table.

Maybe it is not because 'MyView' is not updatable but because I put all UPDATE action in a single statement. I found following rule mentioned in SQL Online Book -'CREATE VIEW - Updatable View' section:

-
"UPDATE or INSERT statement is written so that it modifies data in only one of the base tables referenced in the FROM clause of the view. "
-

This gives me a hint that I have to make separate UPDATE statements for each referenced table within the stored procedure.

fudata at 2007-9-9 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
# 6
What microsoft need to do is to add a viewtableadapter which delegates updates to related tableadapters, which are configurable via a wizard....
TomLeeson at 2007-9-9 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
.NET Development
.NET Framework Data Access and Storage

.NET Development Hot Topic

.NET Development New Topic

.NET Development

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified