Solved

automatically create update statement for view

Posted on 2015-01-25
5
92 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 69

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

749 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