?
Solved

automatically create update statement for view

Posted on 2015-01-25
5
Medium Priority
?
111 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 71

Assisted Solution

by:Qlemo
Qlemo earned 664 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 668 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 668 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…

840 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