June 18, 2012 at 12:20 pm
Afternoon Everyone, I am using ASP as a frontend and SQL 2008 as my backend. I have created a form based on a query which has two tables,
tblAsset and tblSites linked via a (FK SiteID In the tblAsset and PrimaryID in tblSites) my data on my form gets populated correctly when I view but when I try to add data into the fields from the second table (tblSites) I get the following error ' Cannot Insert & Update Multiple Tables' ??
Why is this?
i have been told to use the following code:
sql = 'update.othertable set joinedfield=" & values ( values("join dbconnection.execute sql
values.remove("joinedfield")
June 18, 2012 at 1:50 pm
There is nowhere near enough information posted here to have any chance of helping. There are variable referenced and a lot of details that we don't know about. This is by no means an ASP site but there are certainly people around here that know it (including myself).
I honestly don't even know what to ask before because there is just nothing posted here but a single line of code in the middle of some process. It is going to take a lot more detail before I even have an idea of what is going on here.
Who told you to use this code? Can you go ask them what it is doing and why you have to use it?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 18, 2012 at 2:43 pm
Thanks for your reply, basically I have an asp form that contains fields from two tables. I am trying to add values into fields from tblasset and tblsites on my form, when I click save I get the error 'Cannot insert & Update multiple tables'??
Why do you think this is? The two tables in my query have a left inner join relationship. Is that any help?
June 18, 2012 at 2:49 pm
It is not really help to provide any actual details but it sounds like your insert/update is trying to do this on two tables at the same time. You can't do that. If you need to insert/update data to more than 1 table it MUST be separate statements.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 18, 2012 at 2:53 pm
left inner join? no such animal exists in SQL, so that's probably not what you meant. it's either a left outer outer or an inner join
whatever your code is doing, it's not updating a single table; i'm guessing it populated a DataTable from a query featuring a join, and you are trying to call UpdateDataTable on that ?
you can update FROM a join, but not a view or a query that has joined two tables together.
you will have to provide a lot more detail to get some decent help on that.
Lowell
June 18, 2012 at 2:58 pm
Lowell (6/18/2012)
left inner join? no such animal exists in SQL, so that's probably not what you meant. it's either a left outer outer or an inner joinwhatever your code is doing, it's not updating a single table; i'm guessing it populated a DataTable from a query featuring a join, and you are trying to call UpdateDataTable on that ?
you can update FROM a join, but not a view or a query that has joined two tables together.
you will have to provide a lot more detail to get some decent help on that.
If a view meets the requirements for an updateable view, you can update the underlying tables as long as you are only updating one table at a time, or at least that is how I remember it.
June 18, 2012 at 3:01 pm
Lynn Pettis (6/18/2012)
Lowell (6/18/2012)
left inner join? no such animal exists in SQL, so that's probably not what you meant. it's either a left outer outer or an inner joinwhatever your code is doing, it's not updating a single table; i'm guessing it populated a DataTable from a query featuring a join, and you are trying to call UpdateDataTable on that ?
you can update FROM a join, but not a view or a query that has joined two tables together.
you will have to provide a lot more detail to get some decent help on that.
If a view meets the requirements for an updateable view, you can update the underlying tables as long as you are only updating one table at a time, or at least that is how I remember it.
Your memory serves you well.
From BOL there are few other caveats but they are fairly minor.
http://msdn.microsoft.com/en-us/library/ms187956.aspx
Updatable Views
You can modify the data of an underlying base table through a view, as long as the following conditions are true:
Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 18, 2012 at 3:30 pm
Sorry I did mean a left outer join, I am trying to update these fields with in a view. I really dont see what the issue is, I am using a program to do the asp frontend and the 3rd party software says if I wants to update multiple tables add to a section within the software.
sql = "update othertable set joinedfield=" & values("joinedfield") & " ... "
dbConnection.Execute sql
values.Remove("joinedfield")
?
June 18, 2012 at 3:35 pm
Lucasprss (6/18/2012)
Sorry I did mean a left outer join, I am trying to update these fields with in a view. I really dont see what the issue is, I am using a program to do the asp frontend and the 3rd party software says if I wants to update multiple tables add to a section within the software.sql = "update othertable set joinedfield=" & values("joinedfield") & " ... "
dbConnection.Execute sql
values.Remove("joinedfield")
?
The issue is that you can only update one base table with a single statement. You can't Update a view and set values in more than 1 base table. You would need to update the view twice in order to update 2 base tables.
Update MyView set MyColFromTable1 = 'someVal'
Update MyView set MyColFromTable2 = 'someOtherVal'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 19, 2012 at 5:06 am
The issue is that the sql is coming from an asp form that generates the code on the fly, so I checked the website that my software is from and it said to add mycode on the BeforeAdd.
June 19, 2012 at 7:42 am
Lucasprss (6/19/2012)
The issue is that the sql is coming from an asp form that generates the code on the fly, so I checked the website that my software is from and it said to add mycode on the BeforeAdd.
OK well I can't see your code and I have no idea what this 3rd party tool is, but I do know how updates to a view work in sql server. You will have to modify your code to do separate updates for each of the base tables you want to update in your view. If you can't do that then you will need to work with the vendor or provide a lot more details here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply