Solved

automatically create update statement for view

Posted on 2015-01-25
5
95 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 70

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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

690 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