Solved

automatically create update statement for view

Posted on 2015-01-25
5
84 Views
Last Modified: 2015-01-25
I have a view in SQL server which is defined ast

SELECT ..... FROM tblmaster m JOIN tblreginfo r ON (m.account=r.account)

In my VB.net application, I can connect to the SQL server via ODBC and import tables and views.

However, when I import a table, the tableadapter.Update() command is created for me. For the view, it is not.

What should I do?
0
Comment
Question by:mankowitz
5 Comments
 
LVL 68

Assisted Solution

by:Qlemo
Qlemo earned 166 total points
ID: 40569367
The view refers to two tables, and it doesn't look like it is easy to build a proper update statement from the view. Can you perform an update in T-SQL without getting an error?
0
 
LVL 40
ID: 40569377
None of the tools that automatically generate UPDATE statements (TableAdapter, CommandBuilder) can do it on a multi-table query, because they do now know which of the tables you want to update. You might get the same problem with a table that does not have a primary key, because the tools have no way of identifying a unique row.

The solution is to build an UPDATE statement, assign it  to a SqlCommand object and execute it through its ExecuteNonQuery method.
0
 
LVL 24

Author Comment

by:mankowitz
ID: 40569661
I hear what you are saying, and it makes sense. The only thing is that I actually can issue update statements via SQL Server or through the SSMS window.

What I'm saying is that SQL server seems to be smart enough to allow me to update the view and even smart enough to prohibit me from updating calculated columns in the view, but VB doesn't let me update it.
0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 167 total points
ID: 40569684
The SqlCommand object will not let you update the view, because by default, a view is what its name says it is, a way to view the data, not a way to make changes to it.

What you do through VB is update the table. If you need to update more than one table, then you need an individual Update command for each.
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 167 total points
ID: 40569792
has someone created an INSTEAD OF TRICGGER for the view to allow updates to take place...?

the problems with updating via the combinedview is that the base tables probably aren't in a 1:1 relationship

so the exact underlying rows you wish to effect aren't clear....

read via the view ...
understand the underlying components and relationships
and update each table separately.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now