.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Post New Web Links

updated view (with insert )on two tables

Posted By:      Posted Date: September 14, 2010    Points: 0   Category :Sql Server
CREATE TABLE [tbl1](       [ID] [int] IDENTITY(1,1) NOT NULL,       [F1] [int] NULL,  CONSTRAINT [PK_tbl1] PRIMARY KEY CLUSTERED (       [ID] ASC ) ) ON [PRIMARY] GO CREATE TABLE [tbl2](       [ID] [int] NOT NULL,       [f2] [int] NULL,  CONSTRAINT [PK_tbl2] PRIMARY KEY CLUSTERED (       [ID] ASC ) ) ON [PRIMARY] GO CREATE VIEW [View_1] AS SELECT     tbl1.ID, tbl1.F1, tbl2.f2 FROM         tbl1 LEFT OUTER JOIN                       tbl2 ON tbl1.ID = tbl2.ID GO INSERT INTO [BackUpsData].[dbo].[View_1]            ([F1],[f2])      VALUES(1,1) i get this message : Msg 4405, Level 16, State 1, Line 1 View or function 'View_1' is not updatable because the modification affects multiple base tables.   how can i create a view that can insert new rows into ? thanks.  

View Complete Post

More Related Resource Links

Cannot insert new record into multiple tables thru. sql 2005 View

I have a sql server 2005 database containing several tables: Orders, Shipping, and Planning.  They are joined together in the sql view V_Main.  I also have a ms access 2007 mdb that links to V_Main via odbc.  I created an access form using the view V_Main as the record source.  I cannot insert a new record into each of the 3 base tables using V_Main as the record source.  A new record is inserted into only one table.
I did find a suggestion in another forum that suggested copying the sql that creates the view into the record source, rather than the name of the view.  This does work, but adds another place to be updated manually should and other table be added to the view in the future.
I did verify that all tables have primary keys.
How can I add a new record for each of the 3 base tables using the view as the record source?  What is the code that could be placed in vb of the form?
Any suggestions would be helpful.
Thank you.

insert new records in tables from dataset to database


I have 2 tables in SqlServer 2008.

Table1: T1id, SomeData

Table2: T2id, T1id, SomeData

I am using DataAdapter.FillSchema to create shema of tables in DataSet. I created DataRelation on columns T1id in both tables.

Now when i try to Update Sql Database T1id in Table2 remains 0 and not the value of T1id in Table1.

I can successfully update Sql Database if i fill DataSet with records first (Using DataAdapter.Fill), but that's not what i want to do. I don't need "old" records in my dataset. I want to use dataset just to store all imputs from user until the proccess is done and then insert all those records "at the same time".

I think i'm getting it wrong with ForeignKeyConstraints, maybe UpdateRule is not set to cascade, i just don't know.

I have been trying to work this out for 6 days now and i'm about to break something on half.

Can someone please guide me to right direction, maybe post some sample, anything.... please!

My old post regarding this:

How to update Sql Server related tables from Dataset(SchemaFill)

SqlDataAdapter.Update related tables

asp.net insert into 3 related tables using three tier architecture and typed datasets

I am using ASP.NET (C# and sql server express 2005) for my web application project. I have three related tables.I want to do an insert using typed datasets,tableadapters and three tier architecture.I wish to know how to do simultaneous inserts in 3 tables.So i wish to insert a row in first table and get its primary key.Same with the second table.I will use the primary keys of those first two tables to do the insert in the third table.How can i accomplish this using formview control and what methods should i use??Please give me suggestions.thanks in advance for reading this. I really appreciate it.

View Temporary Tables While Debugging in SQL 2008

How can I view temporary tables while debugging in SQL 2008? Note: I do not want to add a SELECT statement everywhere I need to see the contents of the temporary table. I'm hoping there is another way...like can it be done in the command window somehow, etc?

2 questions - Dropdownlist from SQL Table and Insert into several tables

We're trying to convert an older access app with a sql server back end (with many tables) to Sharepoint. There are entry forms, update forms, look up forms, reports, etc the 2 questions I have are: 1. How can I create a dropdownlist that gets its items from a SQL table? 2. How can I create a form that, when filled out, puts items into multiple tables? Basically, I'd like to know if any of this is possible in 2010, and does anyone have any tips on where to start?    

Insert into many tables at each upload.

<!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format:other; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-520092929 1073786111 9 0 415 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin-top:0cm; margin-right:0cm; margin-bottom:10.0pt; margin-left:0cm; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-fareast-language:EN-US;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"

Create view that amins to tables of another database on the same sql server instance

Hi to everybody, I found a situation ever met before. I develop on Dynamics NAV 5.01 and I have developed a method to be able to see some particular tables of an external database. In substance it deals with a property of tables of Dynamics NAV. When I create a table in NAV, I can create it in 3 different ways: table common to all the companies table  for company or table linked to a view.    This last case is mine, on the same db of NAV I have created a view with some fields, I have created in NAV a table linked with equal fields and types. Until here all normal.    The view, however, aims to another database that doesn't center anything with NAV but that it is on the same intance of SQL server.    The consumer that accesses NAV is a consumer type database SQL Server and has the permitted db_public and db_datareader on both the database. Then he can read the views on the db of nav both on the db of the other database.    When it tries to enter from the console of sql server, with the consumer database, all it works, if I do it by NAV, it show me an error "The server principal "username" is not able to access the database "some_database_name" under the current security context. (Microsoft SQL Server, Error: 916) "    If I add on the database NAV to the consumer, the role db_owner,

Insert "View Entries..." data into custom workflow e-mail

Scenario: There is a custom task list (not created by me) that has a Multiple Lines of Text column called [Description] that is displayed in views as a "View Entries..." hyperlink to the DispForm. On the DispForm there are hyperlinks to each previous version as well as the modifying user. Problem: I need to create a workflow that e-mails the person to whom the task is assigned to with looping functionality whenever it is reassigned. All of this works fine, except I am unable to insert the [Description] column info into the e-mail. I have tried adding the [Description] column lookup to the body, as well as assigning the contents to a string variable and displaying it, but none of these things work. I also discovered that when I switch to Datasheet view the entire [Description] column is empty. When I turn off "Append Changes to Existing Text" to the column, the "View Entries..." hyperlink disappears workflow functions just fine, but since the descriptions can run rather long people don't want to remove this functionality. Any help on the subject would be apprecated!

Insert/Update data to multiple tables on a single button click


Hi experts,

I need to insert/update data to 3 or more tables on a single button click.What is best method for achieving this?I am using mysql as my DB.

Thanks for your valuable reply in advance.

adding mor tables to datasource view causes writeback performance problems


I've got a very strange behavior in one of our SSAS databases. We had performance issues so i've tried to put only the essential parts into one OLAP Database

the result is:

Just by adding more tables to the datasource view the writeback performance drops from 0,2sec to 3 sec. No changes to the cube, no changes to the dimensions.
The t-sql statement fired for the writeback remains the same.
Any idea?



Enabling Edit , delete and insert in Data view with linked sources


Hello ....


I create a data view in sharepoint designer 2007 that has linked sources (Join between two lists) ....


How can I enable edit, delete and insert links in this data view  ????.... taking in consideration that options available in data view properties appear in gray ...



Insert Parameters on a form View


Hi All

Are they any special Insert Paramters I shoud/need to use on a formView.  I currently have the below datasource but when submitted it only inserts <NULLS> intot he required table.

 <asp:SqlDataSource ID="addChangeDataStore" 
                                   ConnectionString="<%$ ConnectionStrings:LocalSQLServer %>"
                                   InsertCommand="INSERT INTO tblChanges(NINumber, TitleIDNo, Forename, Surname, ContractPostNumber, PayRef, DateofChange, TypeofChange, UserID, EmployerIDNo, FurtherInfo) VALUES (@NINO, @TitleIDNo, @Forenames, @Surname, @ContractPostNumber, @PayRef, @DateofChange, @TypeofChange, @UserID, @EmployerIDno, @FurtherInfo)" 
                                   SelectCommand="SELECT tblChanges.* FROM tblChanges">  
                                        <asp:Parameter Name="Nino" Type="String" ConvertEmptyStringToNull="true" /> 
                                        <asp:Parameter Name="TitleIDNo"  Type="String"  ConvertEmptyStringToNull="true" />

Stored Procedures using XQuery to insert XML data into tables


If I have the following XML being passed as a parameter into a stored procedure.


<name>My Bookstore</name><br/>
<location>New York</location><br/>
 <title lang="eng"&g

List box source updated, scroll into view



I have a list box with only item (a Image), its an observable collection , and that gets updated very often,

But if the previous source  was scrolled to the bottom,

 the new source is  also scrolled to the bottom,I want the new source to  scroll to the top evey time its changed

Ive tried scrollToview(currentItem) etc but  it doesnt seem to work

  ItemListForSlides bariatric_slides = new ItemListForSlides(bariatric, 1); // an observable collection

            if (ListBoxS != null)
                itemisdelcted = true;
                ListBoxS .ItemsSource = null;
                ListBoxS .ItemsSource = bariatric_slides;
                ListBoxS .Visibility = Visibility.Hidden;

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend