automatically create update statement for view

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?
LVL 24
mankowitzAsked:
Who is Participating?
 
Jacques Bourgeois (James Burger)PresidentCommented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
Jacques Bourgeois (James Burger)PresidentCommented:
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
 
mankowitzAuthor Commented:
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
 
LowfatspreadCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.